SQL Server Performance Forum – Threads Archive

Deadlock occurs even after using UPDLOCK

I have the following type of stored procedure. CREATE PROCEDURE update_xxx @idINT, @update_typeINT, @output int output AS SET NOCOUNT ON IF (@update_type=1 OR @update_type=3) BEGIN UPDATE xxx SET col1=GETDATE(),col2=1 FROM xxx (updlock) INNER JOIN yyy ON (yyy.id = xxx.id) WHERE xxx.id = @id set @output = @@rowcount END IF (@update_type=2 OR @update_type=3) BEGIN UPDATE xxx SET col3=GETDATE() FROM xxx(updlock) INNER JOIN yyy ON (yyy.id = xxx.id) WHERE col3 IS NULL and xxx.id = @id END GO but then too I get a deadlock within the same sp with the different spid. the deadlock detais are as follows 2006-05-26 09:11:51.96 spid4 Node:1 2006-05-26 09:11:51.96 spid4 KEY: 7:1921754249:1 (7800ccdcfbc6) CleanCnt:1 Mode: U Flags: 0x0 2006-05-26 09:11:51.96 spid4 Grant List 0:: 2006-05-26 09:11:51.96 spid4 Owner:0x7e3782e0 Mode: U Flg:0x0 Ref:0 Life:02000000 SPID:184 ECID:0 2006-05-26 09:11:51.96 spid4 SPID: 184 ECID: 0 Statement Type: UPDATE Line #: 33 2006-05-26 09:11:51.96 spid4 Input Buf: RPC Event: update_xxx;1 2006-05-26 09:11:51.96 spid4 Requested By: 2006-05-26 09:11:51.96 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:81 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x32E2D51<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x79a32240 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/4D624) 2006-05-26 09:11:51.96 spid4 2006-05-26 09:11:51.96 spid4 Node:2 2006-05-26 09:11:51.96 spid4 KEY: 7:1921754249:1 (0c01bd9f7362) CleanCnt:1 Mode: U Flags: 0x0 2006-05-26 09:11:51.96 spid4 Grant List 2:: 2006-05-26 09:11:51.96 spid4 Owner:0x5fb2fa00 Mode: U Flg:0x0 Ref:0 Life:02000000 SPID:81 ECID:0 2006-05-26 09:11:51.96 spid4 SPID: 81 ECID: 0 Statement Type: UPDATE Line #: 20 2006-05-26 09:11:51.96 spid4 Input Buf: RPC Event: update_xxx;1 2006-05-26 09:11:51.96 spid4 Requested By: 2006-05-26 09:11:51.96 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:184 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x7AEAD51<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x4ae9f540 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/0) 2006-05-26 09:11:51.96 spid4 Victim Resource Owner: 2006-05-26 09:11:51.96 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:184 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x7AEAD51<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x4ae9f540 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/0) Please if you can help me, this is a very critical issue for me

I don’t see any deadlock information from the log, in general they will be :
You could give SQL Server a lock hint to take an exclusive lock instead of a shared lock when executing the SELECT
How quickly that update runs or are you controlling updates in smaller batches? You could give SQL Server a lock hint to take an exclusive lock instead of a shared lock when executing the SELECT (some cases). Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The problem lies in the details you posted. First of all, it’s likely to be a conversion deadlock. Whenever I see a deadlock involving updates against the same table that’s my first clue. From the details: spid 184 has an update lock (it’s running the second of the two update statements). At the same time, spid 81 is requesting it’s own update lock on this same resource. Note that both update locks are on the key 7:1921754249:1. I’m guessing here that this a clustered index on the primary key, id. In the mean time, spid 81 has an update lock on different rows (it’s running the first of the two update statements). Now, before either spid can actually perform the update, they’ll need to convert their update locks to exclusive locks. Problem is that neither spid can convert to an exclusive lock because the other spid has an update lock. As a start I’d wrap the whole unit of work you’re attempting to do in a single transaction. This is good practice anyway and you should be doing as a matter of course. In your case this will probably resolve the problem. Your problem is only going to arise when two spids call the sproc with @update_type = 3, since this is going to run both update statements. By wrapping it all up in a transaction, the second spid should not be able to run the first update statement until the original spid’s entire transaction has finished – which is what is causing the deadlocking in this instance. (don’t forget to add your own error handling where you rollback the transaction in case of error):
begin tran IF (@update_type=1 OR @update_type=3)
BEGIN
UPDATE xxx
SET col1=GETDATE(),col2=1
FROM xxx (updlock) INNER JOIN yyy
ON (yyy.id = xxx.id)
WHERE xxx.id = @id
set @output = @@rowcount END
IF (@update_type=2 OR @update_type=3)
BEGIN
UPDATE xxx
SET col3=GETDATE()
FROM xxx(updlock) INNER JOIN yyy
ON (yyy.id = xxx.id)
WHERE col3 IS NULL and xxx.id = @id END commit tran I’d also check the indexes on that table and try and figure out where an index key update lock is going to conflict with the columns you’re actually updating. You shouldn’t need to explicitly specify updlock as a locking hint. This is an update statement so SQL Server will automatically take out an update lock. Hope that all helps, Karl Grambow www.sqldbcontrol.com

]]>