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 (or update) 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 would like the rows in the current, pre-update state, to be returned.
I tried playing around with the isolation levels but that did not seem to have any effect.
I tried setting the transaction isolation level to Serializable but that didnt work. 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.
Thanks for any assistance
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 (or update) 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 would like the rows in the current, pre-update state, to be returned.
I tried playing around with the isolation levels but that did not seem to have any effect.
I tried setting the transaction isolation level to Serializable but that didnt work. 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.
Thanks for any assistance