January 15, 2010 at 8:19 am
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.
January 16, 2010 at 6:15 am
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
January 16, 2010 at 11:02 am
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
Change is inevitable... Change for the better is not.
January 16, 2010 at 11:17 am
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
January 16, 2010 at 11:31 am
Even better.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2010 at 12:15 am
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?
January 17, 2010 at 12:21 am
Jeff Moden,
Sorry for inconvenience. I have posted deadlock log file in .TXT format.
January 17, 2010 at 2:16 am
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
January 18, 2010 at 3:54 am
Hi ,
Try to use tablock in delete Statement. it may work.:w00t:
Regards,
Saravanan
January 18, 2010 at 4:06 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply