Databases
StackOverflow2013
Postsdbo
PODeadlock on bulk inserts
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODeadlock on bulk inserts
    text
    copied!<p>I have several clients running an external executable using a C++ DLL to do bulk inserts though OLEDB into the same SQL Server table. The table has no indices, just a check constraint and a default. After I upgraded from SQL Server 2005 to 2008, all of a sudden I'm seeing deadlocks. </p> <p>The deadlock XDL (anonymized):</p> <pre><code>&lt;deadlock-list&gt; &lt;deadlock victim="process8057f048"&gt; &lt;process-list&gt; &lt;process id="process8057f048" taskpriority="0" logused="0" waitresource="OBJECT: 21:1176443315:0 " waittime="3329" ownerId="33387042" transactionname="SetCnstNotTrusted" lasttranstarted="2011-10-05T09:15:12.227" XDES="0x1afc7c3b0" lockMode="Sch-M" schedulerid="2" kpid="2840" status="suspended" spid="66" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-10-05T09:12:15.753" lastbatchcompleted="2011-10-05T09:12:15.750" clientapp="Ventyx Prosym" hostname="..." hostpid="5892" loginname="..." isolationlevel="read committed (2)" xactid="33371350" currentdb="21" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt; &lt;executionStack&gt; &lt;frame procname="adhoc" line="1" sqlhandle="0x02000000d976c02496c28e4391ad91e2cf367700360cb812"&gt; insert bulk table1(...) &lt;/frame&gt; &lt;/executionStack&gt; &lt;inputbuf&gt; insert bulk bulk table1(...) &lt;/inputbuf&gt; &lt;/process&gt; &lt;process id="processfffdc8" taskpriority="0" logused="0" waitresource="OBJECT: 21:1176443315:0 " waittime="44248" ownerId="33383515" transactionname="SetCnstNotTrusted" lasttranstarted="2011-10-05T09:14:31.310" XDES="0x1f7f503b0" lockMode="Sch-M" schedulerid="1" kpid="4940" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-10-05T09:12:15.720" lastbatchcompleted="2011-10-05T09:12:15.720" clientapp="Ventyx Prosym" hostname="EPMWIN7QA64-05" hostpid="8232" loginname="HESIAppUser" isolationlevel="read committed (2)" xactid="33371331" currentdb="21" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt; &lt;executionStack&gt; &lt;frame procname="adhoc" line="1" sqlhandle="0x02000000d976c02496c28e4391ad91e2cf367700360cb812"&gt; insert bulk bulk table1(...) &lt;/frame&gt; &lt;/executionStack&gt; &lt;inputbuf&gt; insert bulk bulk table1(...) &lt;/inputbuf&gt; &lt;/process&gt; &lt;/process-list&gt; &lt;resource-list&gt; &lt;objectlock lockPartition="0" objid="1176443315" subresource="FULL" dbid="21" objectname="[...].dbo.table1" id="lock2dd099180" mode="IX" associatedObjectId="1176443315"&gt; &lt;owner-list&gt; &lt;owner id="processfffdc8" mode="IX"/&gt; &lt;/owner-list&gt; &lt;waiter-list&gt; &lt;waiter id="process8057f048" mode="Sch-M" requestType="wait"/&gt; &lt;/waiter-list&gt; &lt;/objectlock&gt; &lt;objectlock lockPartition="0" objid="1176443315" subresource="FULL" dbid="21" objectname="[...].dbo.table1" id="lock2dd099180" mode="IX" associatedObjectId="1176443315"&gt; &lt;owner-list&gt; &lt;owner id="process8057f048" mode="IX"/&gt; &lt;/owner-list&gt; &lt;waiter-list&gt; &lt;waiter id="processfffdc8" mode="Sch-M" requestType="wait"/&gt; &lt;/waiter-list&gt; &lt;/objectlock&gt; &lt;/resource-list&gt; &lt;/deadlock&gt; &lt;/deadlock-list&gt; </code></pre> <p>Note that 1176443315 is the object ID for the table itself.</p> <p>As pointed out by Remus, the problem was a CHECK CONSTRAINT on the table (note "SetCnstNotTrusted"). Luckily, I don't even need that constraint anymore (it was old, inherited and decrepit), and removing it did the trick.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload