Deadlock issue : Why 2 SPID thread with U lock cause deadlock on same resource.

Question

Post reply

Deadlock issue : Why 2 SPID thread with U lock cause deadlock on same resource.

Ghanshyam Borasaniya

SSC-Addicted

Points: 407
More actions
July 5, 2006 at 12:51 am

#169001

Our production database is hosted on SQL2K EE with SP3a on Windows 2003, Dell Poweredge 6650 with 4CPU and 16GB RAM. All business logic is deployed at backend using stored procedures. We are observing deadlock occassionally on one of stored procedure 'storeComment' , which has UPDATE statement on line no 126. There is index on revision_id column as well and also we have used 'updlock' hint as given below.
Here update statements and usage of revision table is highly concurance, apart from updlock hint and proper index, Why two SPID thread with U lock on same resource cause deadlock.?
------- Update statement of stored procedure 'storeComment' at line no 126
update revision set has_commentType = @has_commentType
from revision ( updlock )
where revision_id = @doc_revision_id
---------------------
Deadlock encountered .... Printing deadlock information
2006-06-27 15:11:05.10 spid4
2006-06-27 15:11:05.10 spid4     Wait-for graph
2006-06-27 15:11:05.10 spid4
2006-06-27 15:11:05.10 spid4     Node:1
2006-06-27 15:11:05.10 spid4     KEY: 7:53575229:23 (b6017c666c8c) CleanCnt:1 Mode: U Flags: 0x0
2006-06-27 15:11:05.10 spid4      Grant List 0::
2006-06-27 15:11:05.10 spid4        Owner:0x462f44e0 Mode: U        Flg:0x0 Ref:1 Life:02000000 SPID:195 ECID:0
2006-06-27 15:11:05.10 spid4        SPID: 195 ECID: 0 Statement Type: UPDATE Line #: 126
2006-06-27 15:11:05.10 spid4        Input Buf: RPC Event: storeComment;1
2006-06-27 15:11:05.10 spid4      Requested By:
2006-06-27 15:11:05.10 spid4        ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0x40161518) Value:0x29b40ce0 Cost0/6018)
2006-06-27 15:11:05.10 spid4
2006-06-27 15:11:05.10 spid4     Node:2
2006-06-27 15:11:05.10 spid4     KEY: 7:53575229:23 (90014995cf5d) CleanCnt:1 Mode: U Flags: 0x0
2006-06-27 15:11:05.10 spid4      Grant List 3::
2006-06-27 15:11:05.10 spid4        Owner:0x2f953000 Mode: U        Flg:0x0 Ref:1 Life:02000000 SPID:185 ECID:0
2006-06-27 15:11:05.10 spid4        SPID: 185 ECID: 0 Statement Type: UPDATE Line #: 126
2006-06-27 15:11:05.10 spid4        Input Buf: RPC Event: storeComment;1
2006-06-27 15:11:05.10 spid4      Requested By:
2006-06-27 15:11:05.10 spid4        ResType:LockOwner Stype:'OR' Mode: U SPID:195 ECID:0 Ec0x4C057518) Value:0x4833f060 Cost0/8B68)
2006-06-27 15:11:05.10 spid4     Victim Resource Owner:
2006-06-27 15:11:05.10 spid4      ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0x40161518) Value:0x29b40ce0 Cost0/6018)
2006-06-27 15:12:02.63 spid4
Deadlock encountered .... Printing deadlock information
---
As per Lock Modes Compatibility table it should not convert into deadlock, still why deadlock.
Please let me know your view.
Thanks in advance.
Regards
Ghanshyam Borasaniya
DBA.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi,

This looks like it is most likely a conversion deadlock.

Does the column [has_commentType] have an index on it?

Also, what is the index on which the update lock has been taken out? Run the following sql statement to find out. I'm guessing it's the index on revision_id as it's the update lock.

select * from sysindexes where id = 53575229 and indid = 23

With an indid of 23 it looks like you might have a few indexes on that table - and this is probably where the problem lies.

Karl
source control for SQL Server

devendra-301915 Newbie Points: 2 More actions · Answer 2

Hi Karl,

I thought that the update locks could not be shared. I am wondering how would two threads have an update lock on the same resource ie 7:53575229:23 ? That was the purpose of putting the updlock hint. I am from the same company where Ghanshyam works.

Thanks in Advance,

Devendra

SQLZ SSChampion Points: 12872 More actions · Answer 3

Hi Devendra,

The update lock isn't placed on the entire index, the update lock will be on a specific index key range. In other words, the resource that is being locked is not the index but an index key range within that index.

If you wanted to you could have issued a table lock using the TABLOCK hint as well but this will prevent other processes from updating other records in the same table - which isn't ideal.

Karl
source control for SQL Server

devendra-301915 Newbie Points: 2 More actions · Answer 4

Hi Karl,

Thanks for the answer. The index 23 is on revision_id and author_user_id in the revision table. There are a few more indexes on this table but not 23. How could we resolve this deadlock ?

Thanks again,

Devendra