SQL Server Performance Forum – Threads Archive

Strange Deadlock Issue, please help.

Hi, In our application, we encounter deadlock on a single table. Env: SQLServer 2000, SP4, ReadCommitted One thread is trying to do this update sql: "update resources_allocate set FromDate=?, RepFromDate=?, ResId=?, Allocation=?, Manday=?, RepManday=?, ObjId=?, ObjType=?, ToDate=?, RepToDate=?, Description=?, Status=?, RequestTs=?, FrozenAllocation=?, FrozenToDate=?, FrozenFromDate=?, ApprAllocation=?, ApprToDate=?, ApprFromDate=?, RequestUserId=? where allocationId=?" The criteria allocationId is the PK. So it is going to update one record. The same update statement is performed multiple times on different allocationId (PK). That is: update resources_allocate set xxx= … where allocationId = 1xxx update resources_allocate set xxx= … where allocationId = 2xxx update resources_allocate set xxx= … where allocationId = 3xxx So IX locks on different page. The other thread is trying to do this select sql: "select resourcesa0_.allocationId as allocati1_, resourcesa0_.FromDate as FromDate126_, resourcesa0_.RepFromDate as RepFromD3_126_, resourcesa0_.ResId as ResId126_, resourcesa0_.Allocation as Allocation126_, resourcesa0_.Manday as Manday126_, resourcesa0_.RepManday as RepManday126_, resourcesa0_.ObjId as ObjId126_, resourcesa0_.ObjType as ObjType126_, resourcesa0_.ToDate as ToDate126_, resourcesa0_.RepToDate as RepToDate126_, resourcesa0_.Description as Descrip12_126_, resourcesa0_.Status as Status126_, resourcesa0_.RequestTs as RequestTs126_, resourcesa0_.FrozenAllocation as FrozenA15_126_, resourcesa0_.FrozenToDate as FrozenT16_126_, resourcesa0_.FrozenFromDate as FrozenF17_126_, resourcesa0_.ApprAllocation as ApprAll18_126_, resourcesa0_.ApprToDate as ApprToDate126_, resourcesa0_.ApprFromDate as ApprFro20_126_, resourcesa0_.RequestUserId as Request21_126_ from resources_allocate resourcesa0_ where (objId=? )and(objType=? ) order by resourcesa0_.FromDate , resourcesa0_.ToDate" The resultset is going to be a multiple one. And this is the 1204 deadlock trace: 2005-10-20 11:05:37.50 spid4 Deadlock encountered …. Printing deadlock information 2005-10-20 11:05:37.50 spid4 2005-10-20 11:05:37.50 spid4 Wait-for graph 2005-10-20 11:05:37.50 spid4 2005-10-20 11:05:37.50 spid4 Node:1 2005-10-20 11:05:37.51 spid4 PAG: 27:1:13410 CleanCnt:2 Mode: IX Flags: 0x2 2005-10-20 11:05:37.51 spid4 Grant List 0:: 2005-10-20 11:05:37.51 spid4 Owner:0x1bee52c0 Mode: IX Flg:0x0 Ref:4 Life:02000000 SPID:52 ECID:0 2005-10-20 11:05:37.51 spid4 SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1 2005-10-20 11:05:37.51 spid4 Input Buf: RPC Event: sp_executesql;1 2005-10-20 11:05:37.51 spid4 Requested By: 2005-10-20 11:05:37.51 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:51 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

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

‘ />0/0) 2005-10-20 11:05:37.51 spid4 2005-10-20 11:05:37.51 spid4 Node:2 2005-10-20 11:05:37.51 spid4 PAG: 27:1:13409 CleanCnt:2 Mode: S Flags: 0x2 2005-10-20 11:05:37.51 spid4 Grant List 0:: 2005-10-20 11:05:37.51 spid4 Owner:0x67b339c0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:51 ECID:0 2005-10-20 11:05:37.53 spid4 SPID: 51 ECID: 0 Statement Type: SELECT Line #: 1 2005-10-20 11:05:37.53 spid4 Input Buf: RPC Event: sp_execute;1 2005-10-20 11:05:37.53 spid4 Grant List 1:: 2005-10-20 11:05:37.53 spid4 Requested By: 2005-10-20 11:05:37.53 spid4 ResType:LockOwner Stype:’OR’ Mode: IX SPID:52 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

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

‘ />0/2B2C) 2005-10-20 11:05:37.53 spid4 Victim Resource Owner: 2005-10-20 11:05:37.53 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:51 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

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

‘ />0/0) Error: 1205, Severity: 13, State: 61 I know S lock is not compatible with IX lock, that causes deadlock. But I run the select myself in Query Analyzer, I just get the IS lock on the page. IS lock and IX lock is compatible. How can I reproduce the S lock on the page in a select? In another word, when does the SQLServer choose the page S lock granularity when doing a select? Any one can shed some light on?

http://www.sql-server-performance.com/deadlocks.asp
http://www.sql-server-performance.com/at_sql_locking.asp
http://www.awprofessional.com/articles/article.asp?p=26890&rl=1 – fyi. A common technique for handling deadlocks is to use an UPDLOCK table hint on SELECT statements that are commonly involved in transactions that deadlock. Reduce deadlocks by using resources in the same order. For example, if stored procedures SP1 and SP2 use tables T1 and T2, make sure that both SP1 and SP2 process T1 and T2 in the same order. Satya SKJ
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.

Are dirty reads acceptable? If so, NOLOCK on the select might be an option.

Really thanks for the replies.
To satya: Ok, it seems that we don’t know when does sqlserver place S lock on page when select. The only way to resolve the above deadlock is to use lock hints. To dtipton: The dirty read is not allowed, says the Boss. Thanks for all of you, again.

Probably PROFILER or server side trace would help you to capture the details. Satya SKJ
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.

]]>