Deadlock on same key which is a clustered index
-
Our production database is hosted on SQL2K EE with SP3a on Windows 2003, Dell PowerEdge 6650 with 4 CPU and 16 GB RAM. All business logic is deployed at backend using stored procedures. We are observing deadlock occasionally on stored procedure 'update_RDL_action' and 'update_RDL_actionlist', which has UPDATE statement on line no 20. There is clustered composite primary key index on 'user_id, revision_dist_list_id, action_id ' columns and also we have used 'updlock' hint as given below. Also there is a non-clustered index on 'revision_id' column of 'dm_rdl_users' table.
Here update statements and usage of dm_rdl_users table is highly concurrence, apart from updlock hint and proper index, why two SPID thread with U lock on same resource cause deadlock also when we have a clustered index on it?
------- Update statement of stored procedure 'update_RDL_action' at line no 20. Also 'update_RDL_actionList' stored procedure calls 'update_RDL_action' and so the same line no. 20 comes into execution
UPDATE dm_rdl_users
SET action_complete_date=GETDATE(), action_complete=1
FROM dm_rdl_users (updlock) INNER JOIN dm_doc_revision_dist_list
ON (dm_rdl_users.revision_dist_list_id = dm_doc_revision_dist_list.dist_list_id)
WHERE dm_rdl_users.action_complete <> 1 and dm_rdl_users.action_id = @action_id
AND dm_doc_revision_dist_list.revision_id = @revision_id AND dm_rdl_users.user_id = @user_id
---------------------
Querying the sysindexes table on the id and indid from the deadlock it shows the clustered primary key index on 'user_id, revision_dist_list_id, action_id ' columns of dm_rdl_users table.
Deadlock encountered .... Printing deadlock information
2006-08-01 14:16:23.05 spid4
2006-08-01 14:16:23.05 spid4 Wait-for graph
2006-08-01 14:16:23.05 spid4
2006-08-01 14:16:23.05 spid4 Node:1
2006-08-01 14:16:23.05 spid4 KEY: 23:1477580302:1 (e900391c8616) CleanCnt:2 Mode: U Flags: 0x0
2006-08-01 14:16:23.05 spid4 Wait List:
2006-08-01 14:16:23.05 spid4 Owner:0x38b3f500 Mode: U Flg:0x0 Ref:1 Life:02000000 SPID:99 ECID:0
2006-08-01 14:16:23.05 spid4 SPID: 99 ECID: 0 Statement Type: UPDATE Line #: 20
2006-08-01 14:16:23.05 spid4 Input Buf: RPC Event: update_RDL_actionList;1
2006-08-01 14:16:23.05 spid4 Requested By:
2006-08-01 14:16:23.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:81 ECID:0 Ec
0x43E75528) Value:0x518c5c00 Cost0/2561C)2006-08-01 14:16:23.05 spid4
2006-08-01 14:16:23.05 spid4 Node:2
2006-08-01 14:16:23.05 spid4 KEY: 23:1477580302:1 (e900391c8616) CleanCnt:2 Mode: U Flags: 0x0
2006-08-01 14:16:23.05 spid4 Grant List 1::
2006-08-01 14:16:23.05 spid4 Owner:0x2179a780 Mode: U Flg:0x0 Ref:0 Life:02000000 SPID:102 ECID:0
2006-08-01 14:16:23.05 spid4 SPID: 102 ECID: 0 Statement Type: UPDATE Line #: 20
2006-08-01 14:16:23.05 spid4 Input Buf: RPC Event: update_RDL_actionList;1
2006-08-01 14:16:23.05 spid4 Requested By:
2006-08-01 14:16:23.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:99 ECID:0 Ec
0x76BA7528) Value:0x38b3f500 Cost0/0)2006-08-01 14:16:23.05 spid4
2006-08-01 14:16:23.05 spid4 Node:3
2006-08-01 14:16:23.05 spid4 KEY: 23:1477580302:1 (3d01b311d379) CleanCnt:2 Mode: X Flags: 0x0
2006-08-01 14:16:23.05 spid4 Wait List:
2006-08-01 14:16:23.05 spid4 Owner:0x2fd8a700 Mode: U Flg:0x0 Ref:1 Life:02000000 SPID:85 ECID:0
2006-08-01 14:16:23.05 spid4 SPID: 85 ECID: 0 Statement Type: UPDATE Line #: 20
2006-08-01 14:16:23.05 spid4 Input Buf: RPC Event: update_RDL_action;1
2006-08-01 14:16:23.05 spid4 Requested By:
2006-08-01 14:16:23.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:102 ECID:0 Ec
0x31381528) Value:0x21828ec0 Cost0/0)2006-08-01 14:16:23.05 spid4
2006-08-01 14:16:23.05 spid4 Node:4
2006-08-01 14:16:23.05 spid4 KEY: 23:1477580302:1 (3d01b311d379) CleanCnt:2 Mode: X Flags: 0x0
2006-08-01 14:16:23.05 spid4 Grant List 3::
2006-08-01 14:16:23.05 spid4 Owner:0x2cf428c0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:81 ECID:0
2006-08-01 14:16:23.05 spid4 SPID: 81 ECID: 0 Statement Type: UPDATE Line #: 20
2006-08-01 14:16:23.05 spid4 Input Buf: RPC Event: update_RDL_action;1
2006-08-01 14:16:23.05 spid4 Requested By:
2006-08-01 14:16:23.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:85 ECID:0 Ec
0x774B5528) Value:0x2fd8a700 Cost0/2CD94)2006-08-01 14:16:23.05 spid4 Victim Resource Owner:
2006-08-01 14:16:23.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:102 ECID:0 Ec
0x31381528) Value:0x21828ec0 Cost0/0)---
Please let me know your view. Also how can we prevent deadlocks on clustered index.
Thanks in advance.
Regards
Manuni Shah
-
In this case better use temp table to store the output
of resultant row after inner join...
...like this
Select col1, col2,.....
into #temptable
From table1 A inner join table2 B
on .......
where ......
then apply Update Joining Basetable to temptable.
Finally drop #temptable.
This should resolve deadlock.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply