Reproducible Deadlock Using Cascading Constraints

I am looking for code to generate a reproducible deadlock that occurs from the use of Cascading Constraints. I can find references online for the specific problem, and I have answered dozens of questions on deadlocks where cascading constraints were in use, but none of them has a reproducible version of the deadlock.

I am not looking for information about how to troubleshoot a deadlock, capture or read the deadlock graph, or that I should use trace flags to get a deadlock graph. I got all that, I want to see the specifics of the locking that lead to this specific type of deadlock. If you can provide the spec开发者_StackOverflow社区ifics that allow me to write a reproduction of this deadlock, that's a good enough answer.

I have numerous scripts to reproduce other types of deadlocks involving Bookmark Lookups, inconsistent table access in stored procedures using explicit transactions, and existence checks under serializable isolation. I just can't figure out a reproduction for a Cascading Constraint deadlock.

I realize this post is rather old but I have such a situation. please find the deadlock info below and links to the tables involved at: http://pastebin.com/cxqtGMgh and http://pastebin.com/5E8kw6Kw

you will need to dismiss the foreign key references to all the tables I haven't included and probably create some test data. the page reference PAGE: 16:8:5031979 is resolved to the profmaster table as per the code below. please note I've used the Metadata ObjectId instead of the m_objId from the output since the latter doesn't resolve.

DBCC TRACEON(3604)
DBCC PAGE(16,8,5031979,1)
DBCC TRACEOFF(3604)

select name from sys.tables where object_id = 1889441805

here's the output of the page resolution report: http://pastebin.com/HfrVp2nP - and please note I'm running SQL Server 2008 R2 (SP1) X64

I'd be grateful for any guidance as to how the issue can be resolved.

-- e

<deadlock-list>
 <deadlock victim="process53f9f2bc8">
  <process-list>
   <process id="process53f9f2bc8" taskpriority="0" logused="4712" waitresource="KEY: 16:72057678434598912 (a60c363e20e5)" waittime="571706" ownerId="93354998" transactionname="user_transaction" lasttranstarted="2013-11-19T15:39:31.203" XDES="0x80011730" lockMode="U" schedulerid="2" kpid="2064" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-11-19T15:39:31.263" lastbatchcompleted="2013-11-19T15:39:31.260" clientapp="3E WebUI" hostname="LA-BAT-26-WAPI" hostpid="2728" loginname="sa" isolationlevel="read committed (2)" xactid="93354998" currentdb="16" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="140" sqlhandle="0x02000000d1e0f407f28e46e45feec1ce34fc8f3e09dcb3c5">
update n0t0 set ProfMaster = @0 , MatrixTaxCode = null , PrevProfMaster = @1 , LastProcItemID = @2 , TimeStamp = GETDATE ( ) from Timecard as n0t0 where ( ( ( ( n0t0 . WorkDate ) &lt; = ( @3 ) ) and ( ( ( n0t0 . IsActive ) = ( @4 ) ) and ( ( n0t0 . IsNB ) = ( @5 ) ) ) ) and ( ( ( ( ( n0t0 . ProfMaster ) is null ) and ( ( n0t0 . InvMaster ) is null ) ) and ( ( n0t0 . WIPRemoveDate ) is null ) ) and ( ( n0t0 . CurrProcItemID ) is null ) ) ) and ( ( n0t0 . Matter ) in ( select n1t1 . Matter from ProfMatter as n1t1 where ( n1t1 . ProfMaster ) = ( @6 ) ) )     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x0200000095131921232b4d814cc188595c176663442618b6">
UPDATE n0t0 SET ProfMaster = 252516,MatrixTaxCode = NULL,PrevProfMaster = 252516,LastProcItemID = &apos;939f3291-10a7-4704-885e-9021b2a39f15&apos;,TimeStamp = GETDATE() FROM Timecard AS n0t0 WHERE ( ( ( ( n0t0.WorkDate ) &lt;= ( &apos;11/04/2013 00:00:00&apos;) ) AND ( ( ( n0t0.IsActive ) = ( 1 ) ) AND ( ( n0t0.IsNB ) = ( 0 ) ) ) ) AND ( ( ( ( ( n0t0.ProfMaster ) IS NULL ) AND ( ( n0t0.InvMaster ) IS NULL ) ) AND ( ( n0t0.WIPRemoveDate ) IS NULL ) ) AND ( ( n0t0.CurrProcItemID ) IS NULL ) ) ) AND ( ( n0t0.Matter ) IN ( SELECT n1t1.Matter FROM ProfMatter AS n1t1 WHERE ( n1t1.ProfMaster ) = ( 252516 ) ) )/**0x2C621659**/     </frame>
    </executionStack>
    <inputbuf>
UPDATE n0t0 SET ProfMaster = 252516,MatrixTaxCode = NULL,PrevProfMaster = 252516,LastProcItemID = &apos;939f3291-10a7-4704-885e-9021b2a39f15&apos;,TimeStamp = GETDATE() FROM Timecard AS n0t0 WHERE ( ( ( ( n0t0.WorkDate ) &lt;= ( &apos;11/04/2013 00:00:00&apos;) ) AND ( ( ( n0t0.IsActive ) = ( 1 ) ) AND ( ( n0t0.IsNB ) = ( 0 ) ) ) ) AND ( ( ( ( ( n0t0.ProfMaster ) IS NULL ) AND ( ( n0t0.InvMaster ) IS NULL ) ) AND ( ( n0t0.WIPRemoveDate ) IS NULL ) ) AND ( ( n0t0.CurrProcItemID ) IS NULL ) ) ) AND ( ( n0t0.Matter ) IN ( SELECT n1t1.Matter FROM ProfMatter AS n1t1 WHERE ( n1t1.ProfMaster ) = ( 252516 ) ) )/**0x2C621659**/    </inputbuf>
   </process>
   <process id="process50b54c8" taskpriority="0" logused="43622588" waitresource="PAGE: 16:8:5031979" waittime="1575" ownerId="87921480" transactionname="user_transaction" lasttranstarted="2013-11-19T14:26:29.400" XDES="0x569d39950" lockMode="U" schedulerid="9" kpid="1304" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-11-19T15:49:01.277" lastbatchcompleted="2013-11-19T15:49:01.277" clientapp="3E WebUI" hostname="LA-BAT-27-WAPI" hostpid="2628" loginname="sa" isolationlevel="read committed (2)" xactid="87921480" currentdb="16" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="86" sqlhandle="0x020000006014e71626ea61a3e74df841cee6a59043438dc8">
update n0t0 set ProfStatus = @0 , LastProcItemID = @1 , TimeStamp = GETDATE ( ) from ProfMaster as n0t0 join #TempMatterProforma as z0 on ( ( n0t0 . ProfIndex ) = ( z0 . ProfIndex ) ) and ( ( z0 . IsNewProforma ) = ( @2 ) )     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x020000004d41a316b62a8171bbf8979051631fdc480c754b">
UPDATE n0t0 SET ProfStatus = N&apos;P&apos; ,LastProcItemID = &apos;d7eff077-6f52-435f-906a-6fc8f2ee929a&apos;,TimeStamp = GETDATE() FROM ProfMaster AS n0t0 JOIN #TempMatterProforma AS z0 ON ( ( n0t0.ProfIndex ) = ( z0.ProfIndex ) ) AND ( ( z0.IsNewProforma ) = ( 0 ) )/**0xA8DF8735**/     </frame>
    </executionStack>
    <inputbuf>
UPDATE n0t0 SET ProfStatus = N&apos;P&apos; ,LastProcItemID = &apos;d7eff077-6f52-435f-906a-6fc8f2ee929a&apos;,TimeStamp = GETDATE() FROM ProfMaster AS n0t0 JOIN #TempMatterProforma AS z0 ON ( ( n0t0.ProfIndex ) = ( z0.ProfIndex ) ) AND ( ( z0.IsNewProforma ) = ( 0 ) )/**0xA8DF8735**/    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057678434598912" dbid="16" objectname="TE_3E_BAT27B.dbo.Timecard" indexname="IDX__TimeIndex__2F97CBE5" id="lock1ffcaf600" mode="X" associatedObjectId="72057678434598912">
    <owner-list>
     <owner id="process50b54c8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process53f9f2bc8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <pagelock fileid="8" pageid="5031979" dbid="16" objectname="TE_3E_BAT27B.dbo.ProfMaster" id="lock3bf068600" mode="IX" associatedObjectId="72057678315388928">
    <owner-list>
     <owner id="process53f9f2bc8" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process50b54c8" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
  </resource-list>
 </deadlock>
</deadlock-list>