1

I do not understand this deadlock graph that we are experiencing on this table (I have had to redact the exact column names) I have not had much exposure to deadlocks outside of reading material and this doesn't fit my basic understanding of a deadlock and after several hours chasing information online I am none the wiser

CREATE TABLE [dbo].[Table](
[ColumnID] [int] IDENTITY(1,1) NOT NULL,
[2ColumnID] [int] NOT NULL,
[3Column] [int] NOT NULL,
[4Column] [varchar](20) NOT NULL,
[5Column] [char](4) NULL,
[6Column] [varchar](max) NULL,
CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED 
(
[ColumnID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,  
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]

Here is the SQl Error Log

10/04/2013 17:07:40 spid4s      Deadlock encountered .... Printing deadlock information                                                                                                                                                                                                    
10/04/2013 17:07:40 spid4s      Wait-for graph                                                                                                                                                                                                                                             
10/04/2013 17:07:40 spid4s                                                                                                                                                                                                                                                                 
10/04/2013 17:07:40 spid4s      Node:1                                                                                                                                                                                                                                                     
10/04/2013 17:07:40 spid4s      OBJECT: 6:1629964883:0         CleanCnt:3 Mode:SIX    Flags: 0x0                                                                                                                                                                                              
10/04/2013 17:07:40 spid4s       Grant List 0:                                                                                                                                                                                                                                             
10/04/2013 17:07:40 spid4s       Grant List 1:                                                                                                                                                                                                                                             
10/04/2013 17:07:40 spid4s       Grant List 2:                                                                                                                                                                                                                                             
10/04/2013 17:07:40 spid4s         Owner:0x0000000087AC0100 Mode: SIX      Flg:0x0 Ref:3 Life:02000000 SPID:82 ECID:0 XactLockInfo: 0x0000000162F99388                                                                                                                                     
10/04/2013 17:07:40 spid4s         SPID: 82 ECID: 0 Statement Type: DELETE Line #: 1                                                                                                                                                                                                       
10/04/2013 17:07:40 spid4s         Input Buf: Language Event: (@p0 int,@p1 int,@p2 int,@p3 varchar(16),@p4 char(4))DELETE FROM [dbo].[Table] WHERE ([ColumnID] = @p0) AND ([2ColumnID] = @p1) AND ([3Column] = @p2) AND ([4Column] = @p3) AND ([5Column] = @p4)                    
10/04/2013 17:07:40 spid4s       Grant List 3:                                                                                                                                                                                                                                             
10/04/2013 17:07:40 spid4s       Requested By:                                                                                                                                                                                                                                             
10/04/2013 17:07:40 spid4s         ResType:LockOwner Stype:'OR'Xdes:0x0000000166D37350 Mode:   IX SPID:52 BatchID:2 ECID:0 TaskProxy:(0x00000001644C6598) Value:0xa30c4980 Cost:(0/0)                                                                                                        
10/04/2013 17:07:40 spid4s                                                                                                                                                                                                                                                                 
10/04/2013 17:07:40 spid4s      Node:2                                                                                                                                                                                                                                                     
10/04/2013 17:07:40 spid4s      KEY: 6:72057598466916352 (2700f60ea813) CleanCnt:2 Mode:RangeS-U Flags: 0x0                                                                                                                                                                                
10/04/2013 17:07:40 spid4s       Grant List 0:                                                                                                                                                                                                                                             
10/04/2013 17:07:40 spid4s         Owner:0x00000003C6C44980 Mode: RangeS-S Flg:0x0 Ref:0 Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x0000000166D37388                                                                                                                                     
10/04/2013 17:07:40 spid4s         SPID: 52 ECID: 0 Statement Type: DELETE Line #: 1                                                                                                                                                                                                       
10/04/2013 17:07:40 spid4s         Input Buf: Language Event: (@p0 int,@p1 int,@p2 int,@p3 varchar(16),@p4 char(4))DELETE FROM [dbo].[Table] WHERE ([ColumnID] = @p0) AND ([2ColumnID] = @p1) AND ([3Column] = @p2) AND ([4Column] = @p3) AND ([5Column] = @p4)                    
10/04/2013 17:07:40 spid4s       Grant List 2:                                                                                                                                                                                                                                             
10/04/2013 17:07:40 spid4s       Requested By:                                                                                                                                                                                                                                             
10/04/2013 17:07:40 spid4s         ResType:LockOwner Stype:'OR'Xdes:0x0000000162F99350 Mode: X SPID:82 BatchID:2 ECID:0 TaskProxy:(0x00000001638A4598) Value:0xf9381180 Cost:(0/0)                                                                                                         
10/04/2013 17:07:40 spid4s                                                                                                                                                                                                                                                                 
10/04/2013 17:07:40 spid4s      Victim Resource Owner:                                                                                                                                                                                                                                     
10/04/2013 17:07:40 spid4s       ResType:LockOwner Stype:'OR'Xdes:0x0000000162F99350 Mode: X SPID:82 BatchID:2 ECID:0 TaskProxy:(0x00000001638A4598) Value:0xf9381180 Cost:(0/0)

and here is the graph from profiler

enter image description here

and here is the Execution plan

enter image description here

Original XML with redacted information for 'People' Table

<deadlock-list>
 <deadlock victim="processf7a868">
  <process-list>
   <process id="process42e718" taskpriority="0" logused="0" waitresource="OBJECT: 6:1629964883:0 " waittime="10781" ownerId="66865028" transactionname="user_transaction" lasttranstarted="2013-04-10T17:07:16.827" XDES="0x166d37350" lockMode="IX" schedulerid="1" kpid="3720" status="suspended" spid="52" sbid="2" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-04-10T17:07:30.170" lastbatchcompleted="2013-04-10T17:07:30.163" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="1908" loginname="" isolationlevel="serializable (4)" xactid="66865028" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="106" sqlhandle="0x0200000033ce8c2b2096f1641075275d5409c725104fdbc0">
DELETE FROM [dbo].[People] WHERE ([PeopleID] = @p0) AND ([CompanyID] = @p1) AND ([Age] = @p2) AND ([BinaryColumn] = @p3) AND ([PIN] = @p4)     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p0 int,@p1 int,@p2 int,@p3 varchar(16),@p4 char(4))DELETE FROM [dbo].[People] WHERE ([PeopleID] = @p0) AND ([CompanyID] = @p1) AND ([Age] = @p2) AND ([BinaryColumn] = @p3) AND ([PIN] = @p4)    </inputbuf>
   </process>
   <process id="processf7a868" taskpriority="0" logused="0" waitresource="KEY: 6:72057598466916352 (2700f60ea813)" waittime="4375" ownerId="66866310" transactionname="user_transaction" lasttranstarted="2013-04-10T17:07:30.380" XDES="0x162f99350" lockMode="X" schedulerid="3" kpid="4380" status="suspended" spid="82" sbid="2" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-04-10T17:07:30.410" lastbatchcompleted="2013-04-10T17:07:30.400" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="3524" loginname="" isolationlevel="serializable (4)" xactid="66866310" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="106" sqlhandle="0x0200000033ce8c2b2096f1641075275d5409c725104fdbc0">
DELETE FROM [dbo].[People] WHERE ([PeopleID] = @p0) AND ([CompanyID] = @p1) AND ([Age] = @p2) AND ([BinaryColumn] = @p3) AND ([PIN] = @p4)     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p0 int,@p1 int,@p2 int,@p3 varchar(16),@p4 char(4))DELETE FROM [dbo].[People] WHERE ([PeopleID] = @p0) AND ([CompanyID] = @p1) AND ([Age] = @p2) AND ([BinaryColumn] = @p3) AND ([PIN] = @p4)    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="1629964883" subresource="FULL" dbid="6" objectname="SERVER.dbo.People" id="lock2a163c280" mode="SIX" associatedObjectId="1629964883">
    <owner-list>
     <owner id="processf7a868" mode="SIX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process42e718" mode="IX" requestType="convert"/>
    </waiter-list>
   </objectlock>
   <keylock hobtid="72057598466916352" dbid="6" objectname="SERVER.dbo.People" indexname="PK_People" id="lock2a8b03d80" mode="RangeS-U" associatedObjectId="72057598466916352">
    <owner-list>
     <owner id="process42e718" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processf7a868" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
Improve this question

1 Answer 1

Reset to default
6

Mode:RangeS-U

Range locks indicate SERIALIZABLE isolation level. Are you using plain new TransactionScope()? Unless you really really really want serializable isolation (which is never the case...) consider reducing the isolation level to something more manageable, start from read committed.

Mode:SIX

This is an entire object Shared lock (the S in SIX). The UPDATE could not had acquire it as it is, given the selectivity of the predicate [ColumnID] = @p and the primary key constraint on ColumnID. It must had been acquired by a previous scan on the whole table in the same transaction, and again we see a very high isolation level (at least repeatable, most likely again serializable).

First advice: post the deadlock XML, always. Do not post some picture of it, is useless. The XML contains critical information we would like to see (like, trivial example, the isolation level used...).

Second advice: post what else is the transaction doing before issuing the UPDATE.

Last: review your isolation level. Read the linked article about new TransactionScope() considered harmful. Always use read committed, unless very very very clear requirement state you need higher isolation levels. SQL Server 2005 already offers row version based isolation levels, investigate moving to it.

Improve this answer
3
  • I am unable to post the entire XML without sanitising so I thought that would be enough infomration.
    – SQLDBAWithABeard
    Apr 11, 2013 at 9:14
  • The XML has lots of useful info. But I think in your case the problem is evident.
    – Remus Rusanu
    Apr 11, 2013 at 9:20
  • 1
    Thanks for posting the XML. As I suspected: isolationlevel="serializable (4)". You have some SELECT in the transaction, prior to the UPDATE, that locks the entire table (SIX), likely due to lack of indexes (table scan). Can't propose a fix w/o knowing what that SELECT does. None the less moving to READ COMMITTED or SNAPSHOT would be highly advisable (even after fixing the missing index).
    – Remus Rusanu
    Apr 11, 2013 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.