Prevent row access until transaction committed

[Chris Dunaway]

We have a database that is accessed by the web to display the data. We
also have programs that insert or update to that database.

What I want to be able to do is insert data using a transaction but I
don't want the web page to display any new rows being inserted until
the transaction is committed.

So, for example, the program begins a transaction to insert an order
header and a number of order details

In the meantime, someone requests data using the web page. I don't
want my new rows being returned to the web page until the transaction
that is inserting (or updating) them is committed.

I tried playing around with the isolation levels but that did not seem
to have any effect.

Thanks for the help.

 

[Benjamin Fallar III]

set the transaction property to SERIALIZABLE

 

[Chris Dunaway]

That doesn't have the desired effect. If connection A is updating the
row in a transaction using Serializable, and connection B tries to
select the row, connection B is blocked until the transaction is either
committed or rolled back. I need connection B to be able to select
the row and if the row is being updated, then just return the original
contents of the row.

I can specify the (ReadPast) table hint on the select and that is a
little closer to what I want, but in that case, the row being update is
not returned at all. I would rather it be returned but show the
original contents if the row is currently involved in a transaction.