批量插入时出现死锁 [英] Deadlock on bulk inserts
问题描述
我有几个客户端使用C ++ DLL运行外部可执行文件，以通过OLEDB批量插入同一SQL Server表中.该表没有索引，只有检查约束和默认值.从SQL Server 2005升级到2008后，突然间我看到了死锁.
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.
死锁XDL(匿名):
<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>
请注意1176443315是表本身的对象ID.
Note that 1176443315 is the object ID for the table itself.
正如Remus指出的那样，问题出在表上的"CHECK CONSTRAINT"(请注意"SetCnstNotTrusted").幸运的是，我什至不再需要该约束(它是旧的，继承的和衰弱的)，而删除它就可以了.
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.
推荐答案
SCH-M锁定是因为大容量插入语句必须将约束标记为不可信"(因此为transactionname="SetCnstNotTrusted")，这是DDL操作. .在批量插入之前禁用约束，然后重新启用它，在批量插入期间或强制执行约束.请参见通过批量导入操作控制约束检查.
The SCH-M lock is because the bulk insert statement must mark the constraint as 'not trusted' (hence the transactionname="SetCnstNotTrusted"), which is a DDL operation. Disable the constraint before the bulk insert, then enable it back, or enforce the constraint during the bulk insert. See Controlling Constraint Checking by Bulk Import Operations.
这篇关于批量插入时出现死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
