UPDLOCK Question
-
I found this as part of a poorly performing stored procedure. I notice with sp_lock that this line puts update locks on the whole table. What I can't figure out is if there is a valid reason for it. It doesn't make sence to have the lock hint in the query since it is an aggregate query. And the procedure is doing an Insert to that table with the next highest version number. It is an audit table, so it contains around 500,000 records right now.
Begin Tran
...
...
...
SELECT Max(VersionNo) FROM NegotiatedAdjustments_Action(UPDLOCK) WHERE ItemID = 3883790.001
...
...
Commit Tran
This was written by a third party company and we are taking over support. There are plenty of other inefficiencies and was curious about this. Just wondering if anyone thinks this is another.
Looking for some comments on why this would be done.
-
quote:
What I can't figure out is if there is a valid reason for it.
That all depends on what you mean by valid. They're trying to prevent VersionNo from being updated before their transaction completes. In a sense, it's better than other applications I've seen that do something similar without the lock in the transaction bracket. That leads to collisions (two processes can obtain the same max() and attempt to insert the same value). Therefore, I wouldn't remove the lock hint unless you can redesign the table.
If there is a reason not to use Identity, it's best to use a table which keeps track of the ID numbers. Basically you have two columns, one for the table name and one which stores the last value. You use a stored procedure to update the table and prevent different processes from uptaining the same ID number. This works out a lot better than the Max() method.
-
Alright, I understand what you are saying about holding that Max Version Number so no other Transaction can get it, but why is it look like it is locking the whole table rather than the 9 records that belong to ItemID 3883790.001. When I run sp_lock on the spid I get about 4000 rows of page locks.
Thats would make me think it is locking the whole table for some reason. Is it because of the aggregate? Is locking the whole table the only way to ensure that no one else gets that max number for 3883790.001.
Does locking the whole table make it run much slower or just blocks other transactions from making updates before it is done?
I am just trying to figure this one out.
-
quote:
why is it look like it is locking the whole table rather than the 9 records that belong to ItemID 3883790.001
Because another row could be updated to make ItemID = 3883790.001, in which case the max() you just got may no longer be valid.
Even if it was not an aggregate, the entire table would still need to be locked to ensure consistent data.
The lock blocks other transactions (making *them* slower), but it does not make this particular transaction slower.
-
According BOL
quote:
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.
I think it cannot block other transaction from gathering the next highest version number before current transaction has done insert .so It must use HOLDLOCK orSERIALIZABLE locking hint.Am I right?This lock hint(UPDLOCK)wa always my question.any idea would be appreciate
-
As your quote suggests, an UPDLOCK hint holds the lock until the end of the transaction. Therefore, no one else can update the table while the transaction is running.
Other transactions can read the Max() value, but they couldn't update the value.
But remember that in this case, all of the updates are probably happing the same way. They all want to use an update lock. When one transaction already has an update lock, no other transactions can get their own update lock. So they have to wait. They don't even read the table until the first transaction is finished.
-
I have a table like the following:
CREATE TABLE test (Id int primary key,blah varchar(30))
and a stored proc like this:
create procedure usp_test @p varchar(30)
as
declare @nextid int
begin tran
select @nextid=count(*)+1
from test(holdlock)
insert test
values (@nextid,@p)
commit tran
I use holdlock to ensure pk violation does not take place
If got your post the following scenario can perform without pk violation
T1 : exec usp_test 'a' T2: exec usp_test 'b'
T1 select @nextid=1 and before T1's insert ,T2 run and wants
to place updlock on the table but can't so block ,T1 proceed
and insert the row .T2 carry on and insert @nextid=2
Is that right or somthing is wrong?
I become happy If I can Prevent other users
wich want to perform only select action against table from blocking
.Thanks in advance
-
If you use update locks, you should be OK. You can test this by putting in a very long loop in your SP, so that it takes several seconds to execute. Loop between the time you get the Max() and you insert the new values.
-
Thank you so much.I am going to change all hold lock with UPDlock.
.Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply