We're getting numerous deadlocks in a multi-user system where users are coming in and updating their own data.
In our troubleshooting/traces, the deadlocks seem to be coming from lock contention on a Primary Key Clustered Index, not on the table itself.
Also, from what we can see, the application is requesting multiple locks on the Primary Key Index as it proceeds to perform the update on the primary table.
Can anybody shed some light on this?
The output from the sp_lock procedure below shows multiple page-level
locks being acquired on the Primary Key Index during a Row update,
(all by the same SPID). Although the output below doesn't show any
WAIT status locks, usually we encounter several, and subsequently
have deadlocks. In addition, usually the lock mode being requested
is IX - for intent exclusive, just before a final 'X' mode lock being acquired -
presumably to update the index?
I'm not sure I understand why the index would even need updating?
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 397244470 1 PAG 1:462530 IX GRANT
52 7 397244470 0 TAB IX GRANT
52 7 397244470 3 KEY (5600524a83f4) X GRANT
52 7 397244470 4 KEY (e7007b1049f1) X GRANT
52 7 397244470 1 PAG 1:460575 IX GRANT
52 7 397244470 1 PAG 1:104507 IX GRANT
52 7 397244470 1 PAG 1:93918 S GRANT
52 7 397244470 1 PAG 1:93919 S GRANT
52 7 397244470 1 PAG 1:93920 S GRANT
52 7 397244470 1 PAG 1:93921 S GRANT
52 7 397244470 1 PAG 1:93922 S GRANT
52 7 397244470 1 PAG 1:93923 S GRANT
52 7 397244470 1 PAG 1:93924 S GRANT
52 7 397244470 1 PAG 1:93925 S GRANT
52 7 397244470 1 PAG 1:93926 S GRANT
52 7 397244470 1 PAG 1:93927 S GRANT
52 7 397244470 1 PAG 1:93936 S GRANT
52 7 397244470 1 PAG 1:93937 S GRANT
52 7 397244470 1 PAG 1:93938 S GRANT
52 7 397244470 1 PAG 1:93939 S GRANT
52 7 397244470 1 PAG 1:93940 S GRANT
52 7 397244470 1 PAG 1:93941 S GRANT
52 7 397244470 1 PAG 1:93942 S GRANT
52 7 397244470 1 PAG 1:93943 S GRANT
52 7 397244470 1 PAG 1:93944 S GRANT
52 7 397244470 1 PAG 1:93945 S GRANT
52 7 397244470 1 PAG 1:93946 S GRANT
52 7 397244470 1 PAG 1:93947 S GRANT
52 7 397244470 1 PAG 1:93948 S GRANT
52 7 397244470 1 PAG 1:93949 S GRANT
52 7 397244470 1 PAG 1:93950 S GRANT
52 7 397244470 1 PAG 1:93951 S GRANT
52 7 397244470 1 PAG 1:93952 S GRANT
52 7 397244470 1 KEY (2b00467cf4a5) X GRANT
53 4 0 0 DB S GRANT
54 7 66099276 0 TAB Sch-S GRANT
54 7 0 0 DB S GRANT
54 7 0 0 DB S GRANT
54 7 0 0 DB S GRANT
54 7 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 1 85575343 0 TAB IS GRANT
55 7 0 0 DB S GRANT
57 7 0 0 DB S GRANT
58 4 0 0 DB S GRANT
59 7 0 0 DB S GRANT
62 7 0 0 DB S GRANT
78 7 0 0 DB S GRANT
We can provide more detailed traces and code as needed.
- Peter C
In our troubleshooting/traces, the deadlocks seem to be coming from lock contention on a Primary Key Clustered Index, not on the table itself.
Also, from what we can see, the application is requesting multiple locks on the Primary Key Index as it proceeds to perform the update on the primary table.
Can anybody shed some light on this?
The output from the sp_lock procedure below shows multiple page-level
locks being acquired on the Primary Key Index during a Row update,
(all by the same SPID). Although the output below doesn't show any
WAIT status locks, usually we encounter several, and subsequently
have deadlocks. In addition, usually the lock mode being requested
is IX - for intent exclusive, just before a final 'X' mode lock being acquired -
presumably to update the index?
I'm not sure I understand why the index would even need updating?
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 0 0 DB S GRANT
52 7 397244470 1 PAG 1:462530 IX GRANT
52 7 397244470 0 TAB IX GRANT
52 7 397244470 3 KEY (5600524a83f4) X GRANT
52 7 397244470 4 KEY (e7007b1049f1) X GRANT
52 7 397244470 1 PAG 1:460575 IX GRANT
52 7 397244470 1 PAG 1:104507 IX GRANT
52 7 397244470 1 PAG 1:93918 S GRANT
52 7 397244470 1 PAG 1:93919 S GRANT
52 7 397244470 1 PAG 1:93920 S GRANT
52 7 397244470 1 PAG 1:93921 S GRANT
52 7 397244470 1 PAG 1:93922 S GRANT
52 7 397244470 1 PAG 1:93923 S GRANT
52 7 397244470 1 PAG 1:93924 S GRANT
52 7 397244470 1 PAG 1:93925 S GRANT
52 7 397244470 1 PAG 1:93926 S GRANT
52 7 397244470 1 PAG 1:93927 S GRANT
52 7 397244470 1 PAG 1:93936 S GRANT
52 7 397244470 1 PAG 1:93937 S GRANT
52 7 397244470 1 PAG 1:93938 S GRANT
52 7 397244470 1 PAG 1:93939 S GRANT
52 7 397244470 1 PAG 1:93940 S GRANT
52 7 397244470 1 PAG 1:93941 S GRANT
52 7 397244470 1 PAG 1:93942 S GRANT
52 7 397244470 1 PAG 1:93943 S GRANT
52 7 397244470 1 PAG 1:93944 S GRANT
52 7 397244470 1 PAG 1:93945 S GRANT
52 7 397244470 1 PAG 1:93946 S GRANT
52 7 397244470 1 PAG 1:93947 S GRANT
52 7 397244470 1 PAG 1:93948 S GRANT
52 7 397244470 1 PAG 1:93949 S GRANT
52 7 397244470 1 PAG 1:93950 S GRANT
52 7 397244470 1 PAG 1:93951 S GRANT
52 7 397244470 1 PAG 1:93952 S GRANT
52 7 397244470 1 KEY (2b00467cf4a5) X GRANT
53 4 0 0 DB S GRANT
54 7 66099276 0 TAB Sch-S GRANT
54 7 0 0 DB S GRANT
54 7 0 0 DB S GRANT
54 7 0 0 DB S GRANT
54 7 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 1 85575343 0 TAB IS GRANT
55 7 0 0 DB S GRANT
57 7 0 0 DB S GRANT
58 4 0 0 DB S GRANT
59 7 0 0 DB S GRANT
62 7 0 0 DB S GRANT
78 7 0 0 DB S GRANT
We can provide more detailed traces and code as needed.
- Peter C