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
and here is the Execution plan
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>