Deadlock on same key which is a clustered index

Question

Post reply

Deadlock on same key which is a clustered index

Manuni Shah

SSC-Addicted

Points: 425
More actions
August 2, 2006 at 7:51 am

#92149

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 Ec0x43E75528) 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 Ec0x76BA7528) 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 Ec0x31381528) 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 Ec0x774B5528) 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 Ec0x31381528) 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 1

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