Prevent row access until transaction committed

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
Thu Apr 28 11:25:59 CDT 2005

set the transaction property to SERIALIZABLE

"Chris Dunaway" <dunawayc@gmail.com> wrote in message
news:1114703213.903123.323750@o13g2000cwo.googlegroups.com...
> 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.
>

Chris
Thu Apr 28 13:18:16 CDT 2005

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.