Deadlock on 2 deletes

Question

Deadlock on 2 deletes

G. van der Kolk

Grasshopper

Points: 24
More actions
August 28, 2007 at 6:51 am

#121267

Hi, I'm trying to resolve a deadlock on 2 conflicting deletes. Problem is that I don't understand why the delete conflict with each other, because the deletes are executed on 2 different tables. This is the information I get with traceflag 1205 and 1204:
2007-08-28 14:27:16.45 spid3 ----------------------------------
2007-08-28 14:27:16.45 spid3 Starting deadlock search 965
2007-08-28 14:27:16.45 spid3 Target Resource Owner:
2007-08-28 14:27:16.45 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec0x80F2B590) Value:0x81fdb840
2007-08-28 14:27:16.45 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec0x80F2B590) Value:0x81fdb840
2007-08-28 14:27:16.45 spid3 Node:2 ResType:LockOwner Stype:'OR' Mode: S SPID:59 ECID:0 Ec0x8481D590) Value:0x82e4e5c0
2007-08-28 14:27:16.45 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec0x80F2B590) Value:0x81fdb840
2007-08-28 14:27:16.45 spid3
2007-08-28 14:27:16.45 spid3
2007-08-28 14:27:16.45 spid3 Deadlock cycle was encountered .... verifying cycle
2007-08-28 14:27:16.45 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec0x80F2B590) Value:0x81fdb840 Cost0/510)
2007-08-28 14:27:16.45 spid3 Node:2 ResType:LockOwner Stype:'OR' Mode: S SPID:59 ECID:0 Ec0x8481D590) Value:0x82e4e5c0 Cost0/4B0)
2007-08-28 14:27:16.45 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec0x80F2B590) Value:0x81fdb840 Cost0/510)
2007-08-28 14:27:16.45 spid3
2007-08-28 14:27:16.45 spid3
Deadlock encountered .... Printing deadlock information
2007-08-28 14:27:16.45 spid3
2007-08-28 14:27:16.45 spid3 Wait-for graph
2007-08-28 14:27:16.45 spid3
2007-08-28 14:27:16.45 spid3 Node:1
2007-08-28 14:27:16.45 spid3 KEY: 6:1301579675:1 (e201bc1ab2f5) CleanCnt:2 Mode: X Flags: 0x0
2007-08-28 14:27:16.45 spid3 Grant List 1::
2007-08-28 14:27:16.45 spid3 Owner:0x80073360 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:59 ECID:0
2007-08-28 14:27:16.45 spid3 SPID: 59 ECID: 0 Statement Type: CURSORDELETE Line #: 1
2007-08-28 14:27:16.45 spid3 Input Buf: RPC Event: sp_cursor;1
2007-08-28 14:27:16.45 spid3 Requested By:
2007-08-28 14:27:16.45 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec0x80F2B590) Value:0x81fdb840 Cost0/510)
2007-08-28 14:27:16.45 spid3
2007-08-28 14:27:16.45 spid3 Node:2
2007-08-28 14:27:16.45 spid3 KEY: 6:517576882:1 (ad01509c7c2a) CleanCnt:2 Mode: X Flags: 0x0
2007-08-28 14:27:16.45 spid3 Grant List 1::
2007-08-28 14:27:16.45 spid3 Owner:0x84cccd20 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:55 ECID:0
2007-08-28 14:27:16.45 spid3 SPID: 55 ECID: 0 Statement Type: CURSORDELETE Line #: 1
2007-08-28 14:27:16.45 spid3 Input Buf: RPC Event: sp_cursor;1
2007-08-28 14:27:16.45 spid3 Requested By:
2007-08-28 14:27:16.45 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:59 ECID:0 Ec0x8481D590) Value:0x82e4e5c0 Cost0/4B0)
2007-08-28 14:27:16.45 spid3 Victim Resource Owner:
2007-08-28 14:27:16.45 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:59 ECID:0 Ec0x8481D590) Value:0x82e4e5c0 Cost0/4B0)
2007-08-28 14:27:16.45 spid3
2007-08-28 14:27:16.45 spid3 End deadlock search 965 ... a deadlock was found.
Can anyone explain what's going on here?

Viewing 5 posts - 1 through 4 (of 4 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

because what was logged was just sp_cursor;1 and you probably don't know what each SPID was doing ( 55 and 59 ) at the time, you start by identifying the objects (tables) involved. From the trace "KEY: 6:517576882:1" and "KEY: 6:1301579675:1" check objetct_id = 517576882 and object_id= 1301579675 on the db_id =6.

You should try to get a profiler trace to capture more information if you don't know what was the client side actually sending.

My guess is that one delete needed data from one table and the other needed the data from the counter part.

hope this helps

* Noel

G. van der Kolk Grasshopper Points: 24 More actions · Answer 2

The Keys refer to the clustered index of two different tables which are related via another table.

The Queries that are executed are delete statements from these tables.

Problem is that I don't understand how it is possible that deletes on different tables can cause a deadlock

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

1) Possibly there is some foreign key and cascade delete between the two tables?

2) The statements shown are simply the final piece of the deadlock puzle. Somewhere else up in the calling sprocs they reference the opposite table of the pair. That is classically what causes a deadlock. SprocA is holding a lock on tableA while making a request to lock tableB at the same instant sprocB is holding a lock on tableB while making a request to lock tableA.

3) Cursors are BAD. Perhaps you can rewrite the code to avoid them and not get the deadlock in the first place. Performance will likely be significantly better as well.

4) Also consider refactoring the code to

a) Access tables in the same order

b) Minimize transaction complexity and duration.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

the delete will cause a read on all child tables to verify that you are not deleting a required parent.

Because of this you can get some very strange locking behavior. I am assuming that you don't have triggers that could be complicating this issue.

Start here if you haven't already: http://msdn2.microsoft.com/en-us/library/ms178104.aspx

Look into the updlock http://msdn2.microsoft.com/en-us/library/ms187373.aspx

This is basically run a select on the row you are going to delete before you try and delete and issue an intent lock before you try to delete. This will prevent a table lock. (which is most likely the cause of your issue).