By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,826 Members | 815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,826 IT Pros & Developers. It's quick & easy.

Update Deadlocks on Primary Key index

P: 1
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
Nov 30 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.