Dead end on volumetric inserts
I have several clients running an external executable using a C ++ DLL to do bulk inserts though OLEDB into the same SQL Server table. There are no indexes on the table, just a check constraint and default value. After I upgraded from SQL Server 2005 to 2008, all of a sudden I see deadlocks.
XDL deadlock (anonymous):
<deadlock-list>
<deadlock victim="process8057f048">
<process-list>
<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">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x02000000d976c02496c28e4391ad91e2cf367700360cb812">
insert bulk table1(...) </frame>
</executionStack>
<inputbuf>
insert bulk bulk table1(...) </inputbuf>
</process>
<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">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x02000000d976c02496c28e4391ad91e2cf367700360cb812">
insert bulk bulk table1(...) </frame>
</executionStack>
<inputbuf>
insert bulk bulk table1(...) </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="0" objid="1176443315" subresource="FULL" dbid="21" objectname="[...].dbo.table1" id="lock2dd099180" mode="IX" associatedObjectId="1176443315">
<owner-list>
<owner id="processfffdc8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process8057f048" mode="Sch-M" requestType="wait"/>
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="1176443315" subresource="FULL" dbid="21" objectname="[...].dbo.table1" id="lock2dd099180" mode="IX" associatedObjectId="1176443315">
<owner-list>
<owner id="process8057f048" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="processfffdc8" mode="Sch-M" requestType="wait"/>
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
Note that 1176443315 is the object ID for the table itself.
As Remus pointed out, the problem was the DESIGN CHECK on the table (note "SetCnstNotTrusted"). Luckily, I no longer needed this constraint (it was old, legacy and decrepit) and removing it did the trick.
source to share
The SCH-M lock is that the bulk insert statement must mark the constraint as "not trusted" (hence transactionname="SetCnstNotTrusted"
), which is a DDL operation. Disable the constraint before bulk insert, then enable it or force the constraint during bulk insert. See Controlling Constraint Validation Using Bulk Import Operations .
source to share