I log the deadlocks in my SQL error log, and I have a deadlock I don't understand. This is the dead-lock information:
2004-08-12 04:42:31.06 spid4 Deadlock encountered .... Printing deadlock information
2004-08-12 04:42:31.07 spid4
2004-08-12 04:42:31.07 spid4 Wait-for graph
2004-08-12 04:42:31.07 spid4
2004-08-12 04:42:31.07 spid4 Node:1
2004-08-12 04:42:31.07 spid4 KEY: 10:2:1 (07002126e175) CleanCnt:2 Mode: U Flags: 0x0
2004-08-12 04:42:31.07 spid4 Grant List 1::
2004-08-12 04:42:31.07 spid4 Owner:0x35be8fa0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:0
2004-08-12 04:42:31.07 spid4 SPID: 61 ECID: 0 Statement Type: SELECT Line #: 37
2004-08-12 04:42:31.07 spid4 Input Buf: RPC Event: proc_accommodations_price_min_select;1
2004-08-12 04:42:31.07 spid4 Requested By:
2004-08-12 04:42:31.07 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x7786B5F8) Value:0x35be9140 Cost:(0/48)
2004-08-12 04:42:31.07 spid4
2004-08-12 04:42:31.07 spid4 Node:2
2004-08-12 04:42:31.07 spid4 KEY: 10:2:1 (07002126e175) CleanCnt:2 Mode: U Flags: 0x0
2004-08-12 04:42:31.07 spid4 Convert List:
2004-08-12 04:42:31.07 spid4 Owner:0x35be9140 Mode: X Flg:0x2 Ref:1 Life:02000000 SPID:57 ECID:0
2004-08-12 04:42:31.07 spid4 SPID: 57 ECID: 0 Statement Type: UPDATE STATISTICS Line #: 363
2004-08-12 04:42:31.07 spid4 Input Buf: Language Event: DECLARE @intError AS INTEGER
EXEC proc_interface_kanteff_client @intError = @intError OUTPUT
2004-08-12 04:42:31.07 spid4 Requested By:
2004-08-12 04:42:31.07 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec:(0x2FF07598) Value:0x35be8f20 Cost:(1/0)
2004-08-12 04:42:31.07 spid4 Victim Resource Owner:
2004-08-12 04:42:31.07 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec:(0x2FF07598) Value:0x35be8f20 Cost:(1/0)
The object KEY: 10:2:1 is the table sysindexes, the clustered index. In SPID 61 a SELECT is done, so a lock type S is hold. In SPID 57 a UPDATE STATISTICS is done on the same table, a lock type X is hold. But is seems both SPID's are locking the same object , the one with the hash value (07002126e175). Does the "convert list" has anything to do with it? Why is this is deadlock?
Lex van de Pol
Is this possibly executing in the context of a transaction. If so, it's likely to lock it up - dependant on the isolation level.