title: Deadlock on bulk inserts
body: <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>