October 3, 2018 at 2:24 am
Hi,
I have a deadlock I'm investigating on behalf of colleagues. I believe I have the full details of the deadlock, etc. which I've included below.
There are 2 tables involved, with 3 processes.
2 of the processes are select statements, 1 is a delete. The delete wins, the 2 selects are rolled back.
The delete is removing a single record from TableB - there is no join to TableA.
The selects are joining TableA to TableB.
The contention is around the clustered index on TableA, and a specific page in TableB.
The delete statement is taking an exclusive lock on the clustered index of TableA. I'm a bit confused about this part.
I'm aware that an update/insert to TableB would do an FK lookup to TableA to check that it's not violating the constraint. Does it do this for a delete as well? I'd say not, though the lock on TableB from the delete statement has me doubting it.
I have profiled the locks taken during the delete, and can't see it locking anything in TableB. I
The only other thing I can think of is that it's part of a multi-statement transaction and that actually, I don't have all the information available?
Any thoughts on this? Or am i getting this wrong? Is there an obvious way to resolve this deadlock?
BTW, I have updated the deadlock detail to remove sensitive info - I hope I haven't messed it up for a repro...
Many thanks
create table TableA (MtId int primary key clustered, SSSchemes varchar(20), cardsd int)
create table TableB (MtId int primary key clustered references TableA(MtID), MName varchar(200))
insert into TableA values (2018, 'test test', 1)
insert into TableB values (2018, 'abc')
<deadlock>
<victim-list>
<victimProcess id="process1370688c8"/>
<victimProcess id="process8fd4b088"/>
</victim-list>
<process-list>
<process id="process1370688c8" taskpriority="0" logused="0" waitresource="KEY: 5:72057594052345856 (cb85b56688e1)" waittime="3638" ownerId="3246889648" transactionname="SELECT" lasttranstarted="2018-10-02T12:30:17.687" XDES="0x173c1ba20" lockMode="S" schedulerid="2" kpid="6584" status="suspended" spid="94" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-10-02T12:30:17.693" lastbatchcompleted="2018-10-02T12:30:17.693" lastattention="2018-10-02T11:32:09.790" hostname="XXXXX" hostpid="17592" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="3246889648" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="50" stmtend="636" sqlhandle="0x02000000bea8d903322541dcdd9832c74f453f24466374160000000000000000000000000000000000000000">
unknown
</frame>
<frame procname="adhoc" line="1" stmtend="554" sqlhandle="0x02000000b0bdae2dd7f27063b736e5403b362267aac7ad620000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>
SELECT dbo.TableB.MtID, dbo.TableB.SSSchemes FROM dbo.TableA INNER JOIN dbo.TableB ON dbo.TableA.MtID=dbo.TableB.MtID WHERE dbo.TableB.cardsd=1 AND dbo.TableA.MName='abc';
</inputbuf>
</process>
<process id="process8fd4b088" taskpriority="0" logused="0" waitresource="KEY: 5:72057594052345856 (cb85b56688e1)" waittime="3713" ownerId="3246889505" transactionname="SELECT" lasttranstarted="2018-10-02T12:30:17.613" XDES="0x157573bb0" lockMode="S" schedulerid="1" kpid="15212" status="suspended" spid="413" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-10-02T12:30:17.620" lastbatchcompleted="2018-10-02T12:30:17.620" lastattention="2018-10-02T11:13:43.840" hostname="XXXXX" hostpid="17592" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="3246889505" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="50" stmtend="636" sqlhandle="0x02000000bea8d903322541dcdd9832c74f453f24466374160000000000000000000000000000000000000000">
unknown
</frame>
<frame procname="adhoc" line="1" stmtend="562" sqlhandle="0x02000000a7a4ab2ba2cb64ff98f56245d2e70284e7d000ee0000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>
SELECT dbo.TableB.MtID, dbo.TableB.SSSchemes FROM dbo.TableA INNER JOIN dbo.TableB ON dbo.TableA.MtID=dbo.TableB.MtID WHERE dbo.TableB.cardsd=1 AND dbo.TableA.MName='abc';
</inputbuf>
</process>
<process id="processacd1a8c8" taskpriority="0" logused="4404" waitresource="PAGE: 5:1:4894 " waittime="3383" ownerId="3246889346" transactionname="implicit_transaction" lasttranstarted="2018-10-02T12:30:17.567" XDES="0xff9f7000" lockMode="IX" schedulerid="8" kpid="7160" status="suspended" spid="125" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-10-02T12:30:17.950" lastbatchcompleted="2018-10-02T12:30:17.947" lastattention="1900-01-01T00:00:00.947" clientapp="SPI .NET Apps" hostname="XXXXX" hostpid="7156" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="3246889346" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="16" stmtend="120" sqlhandle="0x02000000be961c021b401290201f94e2e3edda09883f9c600000000000000000000000000000000000000000">
unknown
</frame>
<frame procname="adhoc" line="1" stmtend="100" sqlhandle="0x02000000412ea904937817e75254c85e2626b8dcfd6b20a90000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>
DELETE FROM dbo.TableB WHERE MtID=2018
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594052345856" dbid="5" objectname="XXXXX.dbo.TableA" indexname="TableAConstraint" id="lock155cde100" mode="X" associatedObjectId="72057594052345856">
<owner-list>
<owner id="process8fd4b088" mode="S" requestType="wait"/>
</owner-list>
<waiter-list>
<waiter id="process1370688c8" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594052345856" dbid="5" objectname="XXXXX.dbo.TableA" indexname="TableAConstraint" id="lock155cde100" mode="X" associatedObjectId="72057594052345856">
<owner-list>
<owner id="processacd1a8c8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process8fd4b088" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<pagelock fileid="1" pageid="4894" dbid="5" subresource="FULL" objectname="XXXXX.dbo.TableB" id="lock17f6dba00" mode="SIU" associatedObjectId="72057594052214784">
<owner-list>
<owner id="process1370688c8" mode="S"/>
<owner id="process8fd4b088" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="processacd1a8c8" mode="IX" requestType="convert"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
October 4, 2018 at 5:41 am
anyone? thanks
October 4, 2018 at 2:29 pm
If you (almost) always look up on tableB by the MName, then cluster the table on that. That would prevent a full scan of the table from happening every time you look up a single name. You could add a non-clus index, but that's a huge waste of space, duplicating almost all of the shown table; I'll of course assume other columns are present in TableB that are not shown, but even then, a non-clus index on such a long column will be a big dup of data.
Long column or not, if MName is how you (almost) always do lookups on that table, then it should be the lead clustering key.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply