I have two stored procedures running on separate threads running on SQL Server 2005. One procedure inserts new rows into a set of tables, and the other procedure deletes old data from one set of tables. These procedures start at a standstill on the DLevel and Model tables. Here is the diagram:
Scrolling image http://www.barramsoft.com/pub/images/DeadLock2.jpg
DFile table: primary key = DFileID
DLevel table: primary key = DLevelID, foreign key: DFileID
Table Model : primary key = model identifier, foreign key: DFileID
ELement table: primary key = ElementID element, foreign key1: DFileID, foreign key2: DLevelID
I highlighted the exact two SQL statements (one from each stored procedure) that cause a dead end. I saw a dead end reported by any of the procedures. I use top (1000) in both cases, and both statements are executed in a loop until they contain the rows left to delete / insert.
SQL statement 1:
while (...) begin delete top (1000) from DLevel where DFileID = @fileID1 ... end
SQL statement 2:
while (...) begin insert into Element (ElementID, DFileID, LevelNum, ...) select top (1000) el.ElementID, el.DFileID, el.LevelNum, ... from ElementLoader el with (nolock) left outer join Element e with (nolock) on e.ElementID = el.ElementID where el.DFileID = @fileID2 and e.ElementID is null order by el.ElementID ... end
Note. The values for @ fileID1 and @ fileID2 are always guaranteed to be different. The DLevel table has an average of approx. 60 lines for a single DFileID and thus complete the deletion of all lines in a single pass.
How can I avoid a deadlock between these two SQL statements?
Edit 1: Rewritten to clarify the problem; added image; simplified SQL and a remote link to a DLevel table that did not contribute to a deadlock.
Edit 2: Added deadlock XML.
A dead end is now found on the model table. The same instruction and deletion scheme for the model as for the DLevel table.
<deadlock victim="process2bae38"> <process-list> <process id="process2bae38" taskpriority="0" logused="4760" waitresource="PAGE: 11:1:1946" waittime="46" ownerId="4127445" transactionname="DELETE" lasttranstarted="2010-06-24T16:19:00.107" XDES="0xffffffff90552ae0" lockMode="S" schedulerid="1" kpid="14252" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-06-24T16:19:00.107" lastbatchcompleted="2010-06-24T16:19:00.107" clientapp=".Net SqlClient Data Provider" hostname="LT0103" hostpid="1668" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="4127445" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="CadExplorer5.dbo.pCleanUpOldFiles" line="364" stmtstart="23718" stmtend="23834" sqlhandle="0x03000b00fb1c2229b1a7f7009f9d00000100000000000000"> delete top (@batchSize) from Model where DFileID = @fileID </frame> </executionStack> <inputbuf> Proc [Database Id = 11 Object Id = 690101499] </inputbuf> </process> <process id="process2c95b8" taskpriority="0" logused="283388" waitresource="KEY: 11:72057594039304192 (8100bdf15e8b)" waittime="78" ownerId="4127412" transactionname="INSERT" lasttranstarted="2010-06-24T16:19:00.103" XDES="0xffffffff81d5ef18" lockMode="S" schedulerid="2" kpid="8460" status="suspended" spid="63" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-06-24T16:18:59.413" lastbatchcompleted="2010-06-24T16:18:59.413" clientapp=".Net SqlClient Data Provider" hostname="LT0103" hostpid="1668" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="4127412" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="CadExplorer5.dbo.pLoadElements" line="288" stmtstart="28796" stmtend="33194" sqlhandle="0x03000b00a689fe2b2c5107019f9d00000100000000000000"> insert into Element ( ElementID, DFileID, ModelID, ElementTypeID, CADElementID, ParentID, LevelNum, Depth, NumChildren, Color, Weight, Style, Xlo, Ylo, Zlo, Xhi, Yhi, Zhi, Diagonal, XCenter, BitFlags, ElementModTime ) select top (@batchSize) el.ElementID, el.DFileID, el.ModelID, el.ElementTypeID, el.CADElementID, parent.ElementID as ParentID, (case when el.LoaderType = 1 and et.IsGraphical = 1 then el.LevelAttrib else null end) as LevelNum, --l.LevelNum, el.Depth, el.NumChildren, el.Color, el.Weight, el.Style, el.Xlo, el.Ylo, el.Zlo, el.Xhi, el.Yhi, el.Zhi, el.Diagonal, el.XCenter, </frame> </executionStack> <inputbuf> Proc [Database Id = 11 Object Id = 738101670] </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="1946" dbid="11" objectname="CadExplorer5.dbo.Element" id="lockffffffff86ffd080" mode="IX" associatedObjectId="72057594039107584"> <owner-list> <owner id="process2c95b8" mode="IX"/> </owner-list> <waiter-list> <waiter id="process2bae38" mode="S" requestType="wait"/> </waiter-list> </pagelock> <keylock hobtid="72057594039304192" dbid="11" objectname="CadExplorer5.dbo.Model" indexname="PK_Model" id="lockffffffff8d66db80" mode="X" associatedObjectId="72057594039304192"> <owner-list> <owner id="process2bae38" mode="X"/> </owner-list> <waiter-list> <waiter id="process2c95b8" mode="S" requestType="wait"/> </waiter-list> </keylock> </resource-list> </deadlock>