September 23, 2005 at 3:56 am
Hello all,
BOL states: Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. I have some trouble translating this. Does this mean that the update locks are released after the execution of the SELECT statement, unless the SELECT statement in within a transaction?
I other words, are my assumptions in the following 2 scenario's correct?
Scenario 1. no transaction
SELECT something FROM table WITH (UPDLOCK)
/* update locks released */
scenario 2. with transaction
BEGIN TRANSACTION
SELECT something FROM table WITH (UPDLOCK)
/* some code, including an UPDATE */
COMMIT TRANSACTION
/* update locks released */
Thank you for your attention. Gerry S.
September 23, 2005 at 5:56 am
Generally UPDLOCK is used for INSERT/UPDATE on a table. What are you attempting to accomplish by using this lock?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 23, 2005 at 6:33 am
A simplified example:
Before the UPDATE of an Order I have to check the Status of the Order(invoiced or NOT invoiced) and the existance of Order Details. Other users should not be allowed to delete the Order during the check of Status and the UPDATE.
I want to verify that I understood BOL correctly.
(Me being a bloody foreigner and all.)
September 23, 2005 at 10:52 am
The "UPDLOCK" locking hint is used when doing a select for update i.e. when you are using pessimistic concurrency model and you want to make sure that no-one else can modify that data. The usage of the UPDLOCK hint also prevents a typical lock conversion issue that can happen if this is not used and direct updates are made (can explain this more if you want).
Since it is a UPDLOCK, it will put a U lock on the record which will later be changed to an X lock once the application gets ready and fires the actual update statement. So, the code looks like this:
--setting the lock timeout to 1 second
SET LOCK_TIMEOUT 1000
begin tran
select 1 from ord_hdr with (updlock, rowlock) where ord_id = 2
--the above statement uses the UPDLOCK hint to take the update lock and uses the rowlock hint (generally a good practice) to prevent lock escalations (there are other ways to prevent this as well)...if going by the PK, there is no need to even add the rowlock hint.
<error handling code here>
update ord_hdr set qty = qty + 10 where ord_id = 2
<error handling code here>
commit/rollback logic
The U lock gets taken when the UPDLOCK SQL fires, this gets changed to X when the update gets fired and then gets released when the actual commit/rollback occurs. The key is to keep your transactions small, lock the record before the update - if someone else has taken a lock on that record already, you will get a lock timeout after the specified period of lock wait..trap the 1222 error and decide what you want to do -re-try or log the error and move on.
Did that answer your question ? This is a pretty vast topic...you can check articles on http://www.sql-server-performance.com for more information or look-up BOL and using sp_lock and other procedures like syslockinfo etc., see how the locks are being taken and released.
Hth.
September 27, 2005 at 12:41 am
AJ and rsharma, thank you both for your answers. I need the UPDLOCK within a transaction, and will act accordingly.
A small question remains: what is the use of using a SELECT WITH (UPDLOCK) without a TRANSACTION?
September 27, 2005 at 5:17 am
If you use it without a TRANSACTION and there is a problem you won't be able to ROLLBACK ...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 27, 2005 at 7:25 am
When the SELECT is not in a transaction, SELECT * FROM WITH(UPDLOCK) will prevent others to update the table while the select statement is running.
If the hint is not there, only S locks are held, which is compatible to U lock.
The UPDLock is released immediately after the select is finished.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply