April 28, 2005 at 1:55 pm
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
April 28, 2005 at 2:20 pm
Have you tried to use "UPDLOCK" hint or "NOLOCK"?
Quoted from BOL below.
"Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it."
"Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement."
April 28, 2005 at 3:09 pm
By default you will not be able to select rows that are being inserted/modified until the transaction completes. Just put all modification statements between a BEGIN TRANSACTION and a COMMIT TRANSACTION.
Modified/inserted Rows within an uncommitted transaction by default will not be able to be selected. A Select statement trying to access such rows will have to wait for the transaction to complete or be rolled back.
Setting the isolation level to Read Uncommitted will allow you to read rows being inserted or modified but not committed. This is Highly NOT recommended.
You cannot get the prior values of rows in the midst of a modification. Sql Server 2005 has a snapshot isolation that may(?) help in this area.
April 28, 2005 at 5:55 pm
Edited to note the converion to Exclusive Lock:
UPDLOCK will not help in the situation presented by Chris. UPDLOCK will allow SELECT statements (not requiring UPDLOCK or HOLDLOCK) by other users to read rows that have been SELECTed but as soon as the modification process starts the Updlock is converted to an Exclusive Lock (assuming all shared read locks have been released) and then all other users including SELECT statements are blocked from reading the row.
"...UPDLOCK has the advantage of allowing you to read data (without blocking other readers)..."
NOLOCK is the same as READ UNCOMMITTED and is usually not a very good idea.
April 29, 2005 at 8:30 am
You might also consider using READPAST: Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy