Prevent row access until transaction committed

  • Thread starter Thread starter Chris Dunaway
  • Start date Start date
C

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.
 
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.
 
Back
Top