Deadlocking on Deletion Process
-
Hi,
I need help troubleshooting a deadlock issue. I've performed a 1204 trace. I've listed below a typical output from the errorlog for the deadlock output. Basically the issue is always to do with deletions from the same table. The 2 spids involved are both performing the deletions from the same table.
I've done a profiler trace for the same period.
The table in question has clustered index on the 'where' clause ie the FileContentsGuid column. An archiving deletion process is being tested, and deadlocking is occuring, alway on this table.
Any help would be great.
Deadlock encountered .... Printing deadlock information
2006-08-02 14:48:21.24 spid4
2006-08-02 14:48:21.24 spid4 Wait-for graph
2006-08-02 14:48:21.24 spid4
2006-08-02 14:48:21.24 spid4 Node:1
2006-08-02 14:48:21.24 spid4 KEY: 7:1781581385:1 (ad03d31daffe) CleanCnt:1 Mode: X Flags: 0x0
2006-08-02 14:48:21.24 spid4 Grant List 2::
2006-08-02 14:48:21.24 spid4 Owner:0x64e895e0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:82 ECID:0
2006-08-02 14:48:21.24 spid4 SPID: 82 ECID: 0 Statement Type: DELETE Line #: 1
2006-08-02 14:48:21.24 spid4 Input Buf: Language Event: DELETE FROM FVFileContents WITH (ROWLOCK) WHERE FileContentsGuid=0xFA590970EC9743C591352D97E050612E
2006-08-02 14:48:21.24 spid4 Requested By:
2006-08-02 14:48:21.24 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:72 ECID:0 Ec
0x65AB3540) Value:0x237186c0 Cost0/634)2006-08-02 14:48:21.24 spid4
2006-08-02 14:48:21.24 spid4 Node:2
2006-08-02 14:48:21.24 spid4 KEY: 7:1781581385:1 (4f03ad20d4d9) CleanCnt:1 Mode: X Flags: 0x0
2006-08-02 14:48:21.24 spid4 Grant List 3::
2006-08-02 14:48:21.24 spid4 Owner:0x23718900 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:72 ECID:0
2006-08-02 14:48:21.24 spid4 SPID: 72 ECID: 0 Statement Type: DELETE Line #: 1
2006-08-02 14:48:21.24 spid4 Input Buf: Language Event: DELETE FROM FVFileContents WITH (ROWLOCK) WHERE FileContentsGuid=0xFEDA3D64912A4173AD577F9ABD20C3EB
2006-08-02 14:48:21.24 spid4 Requested By:
2006-08-02 14:48:21.24 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:82 ECID:0 Ec
0x214A1540) Value:0x64e89fa0 Cost0/C44)2006-08-02 14:48:21.24 spid4 Victim Resource Owner:
2006-08-02 14:48:21.24 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:72 ECID:0 Ec
0x65AB3540) Value:0x237186c0 Cost0/634)2006-08-02 14:49:58.78 spid4
-
Is it possible for you to send the portion of the code?
-
Hi, I will create an output of the profiler trace for this period with the relavant period.
-
It sounds as if SQL Server is escalating the locks from ROWLOCK to PAGELOCK.
-
What can I do, in this instance. ?
-
Not sure how I would attached the profiler trace, so I've detailed the order of events from the profiler trace
1. spid:72 Time 2006-08-02 14:46:37.690
DELETE FROM FVFileContents WITH (ROWLOCK) WHERE FileContentsGuid=0xFEDA3D64912A4173AD577F9ABD20C3EB
2. spid:82 Time 2006-08-02 15:46:46:58.613
DELETE FROM FVFileContents WITH (ROWLOCK) WHERE FileContentsGuid=0xFA590970EC9743C591352D97E050612E
3. spid:72 Time 2006-08-02 14:48:21.240
Deadlock victim
spid 72 delete statement fails
4. spid:82 Time 2006-08-02 14:48:21.473
spid 82 delete statement completes
-
Hi all,
resolved my issue. Process used to diagnose the fault is described for those who are interested :-
1. Enable traceflag 1204 - from this was able to identify tables,processids,spids involved.
2. Performed a profiler trace that looked at sql run, deadlock information.
Was able to estable the query execution order.
3. Adjusted the profiler trace to include details of show plan.
4. Reading the execution plan I could see that the issue was to do with a foreign key index scan that was being performed during the deletion process.
5. Noticed the table did not have an index on the foreign key, instead was using the primary key clustered index. This was not the most effecient way.
6. Applied the index on the foreign key, problems gone away.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply