Deadlock on single Table, when DELETE

Question

Deadlock on single Table, when DELETE

ypradeep

SSC Enthusiast

Points: 161
More actions
January 15, 2010 at 8:19 am

#100045

Hi SQL Devs,
Description ablut table.
Table name is FACILITY_FEE (Child table of FACILITY) has 2 NON-Cluster indexs on facility_fee_id (PK) and facility_id (fk).
And, Resource list is showing that, there is RIDLock with lock combination X-S = X-S, but with different PageIDs.
ridlock fileid=1 pageid=91609 dbid=10 objectname=FACILITY_FEE id=lock4f0ca00 mode=X associatedObjectId=72057594043367424
owner-list
owner id= process3c12ef8 mode=X
waiter-list
waiter id= process7808478 mode=S requestType=wait
ridlock fileid=1 pageid=20307 dbid=10 objectname=FACILITY_FEE id=lock7b19e80 mode=X associatedObjectId=72057594043367424
owner-list
owner id= process7808478 mode=X
waiter-list
waiter id= process3c12ef8 mode=S requestType=wait
Note that, Delete SP dletefacility table has 2 row Facility (1 for current, and 1 for old version. Both are related with source_facility_id).
Attached are the Script to generate Test data. Also Stored procedures are attached.
As per my understanding, this deadlock was due to operating both transactions on same data. Also not having a cluster index could be a reason.
Can any one explain the technical reasons behind this deadlock?
I would appriciate any help to resolve this.
Thank You,
Pradeep.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I'm honestly not sure why this is happening.

Is there any reason you have no clustered index? If there's no good reason, maybe make the pk clustered, see if it helps.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

You might also consider saving your Word docs as a Word 97-2003 compatible doc because you cut out a lot of people who don't have Word 2007 from helping you. Like, me. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Actually, I'd suggest either saving the xml deadlock graph in a text file or posting the deadlock graph directly into your post, rather than giving us what looks like your company's deadlock problem and resolution document.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Even better.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ypradeep SSC Enthusiast Points: 161 More actions · Answer 5

Gail Shaw,

There is no reason for not having a CLUSTER index. I can do it.

But I want to know, the technical reason behind this deadlock, and how to reproduce this?

ypradeep SSC Enthusiast Points: 161 More actions · Answer 6

Jeff Moden,

Sorry for inconvenience. I have posted deadlock log file in .TXT format.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

ypradeep (1/17/2010)
But I want to know, the technical reason behind this deadlock, and how to reproduce this?

As I said, I'm honestly not sure why this is happening.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Saravanan T SSCrazy Points: 2506 More actions · Answer 8

Hi ,

Try to use tablock in delete Statement. it may work.:w00t:

Regards,
Saravanan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Saravanan T (1/18/2010)
Hi ,
Try to use tablock in delete Statement. it may work.:w00t:

It'll prevent the deadlock sure. It'll also prevent anyone else from doing anything at all to the table for the duration of the delete.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass