在一个事务中为多个相关表使用SqlBulkCopy [英] Using SqlBulkCopy in one transaction for multiple, related tables

问题描述

在这里，我试图将大量数据(总计一百万条记录)从三个相关表复制到同一数据库中的其他三个相关表中有些卡住了.

I'm a bit stuck here trying to copy lots of data (a million records total) from three related tables to other three related tables in the same database.

我的桌子设计如下:

我需要的是能够在一个事务中将数据从草稿表复制到非草稿表，如果有任何问题，我可以回滚.这是必需的，因为如果批量复制复制失败，我们不希望在数据库中存在 Billing 和 BillingPriceLine 记录.

What I need is to be able to copy data from the draft tables over to the non-draft tables in one transaction which I'm able to roll back if anything goes wrong. This is needed because we don't want i.e. Billing and BillingPriceLine records to exist in the database, if the bulk insertion of BillingPriceLineSpecificationDraft copy failed.

但是，由于我使用 SqlBulkCopy 复制记录，因此无法获得新的ID来在三个新表之间建立正确的关系.如果我对事务中的 Billing 表执行读取操作以获得正确的 Billing ID，则会超时，这是预期的，因为这些表已锁定在交易.

However, since I'm using SqlBulkCopy for copying the records, I am not able to get a hold of the new IDs to make the correct relations between the three new tables. If I perform a read on i.e. the Billing table in the transaction to get the correct Billing ID, I get a time out, which is expected since the tables are locked within the transaction.

我曾尝试在交易中设置 IsolationLevel 枚举(实际上，我发疯了，并尝试了所有这些;-))，但看上去它们什么也没做.

I have tried setting the IsolationLevel enum on the transaction (in fact, I went crazy and tried them all ;-)), but they didn't do anything it seems.

有什么好的方法可以使我丢失吗?

Is there any good way of doing this that I'm missing?

谢谢.

推荐答案

使用批量复制实际上无法做到这一点.

You can not really do that with bulk copy.

您也不需要-用SqlBulkCopy接触实际表是非常糟糕的做法，因为无论写那件事的人都不了解SQL Server锁定机制.

You also do not need to - it is a very bad practice to touch real tables with SqlBulkCopy because whoever wrote that thing, has no understanding of SQL Server locking mechanisms.

• 创建3个与目标表具有相同表结构的临时表.
• 将SqlBulkCopy复制到这些表中(从而避免SqlBulkCopy锁定使您烦恼，并避免事务问题).
• 然后，在一次事务中，使用临时表中的数据作为源发出3条INSERT INTO语句.

结果是两全其美.一旦将数据存储在临时表中(可能带有伪造的ID)，您就可以提取一些非常有趣的SQL，以便在一个事务中使用下一个表的相关内部ID来更新源表.SQL变得很复杂，但这是您所能做的最好的事情.

Result is the best of both worlds. Once you have the data in the temporary tables (with possibly fake ID's) you can pull some really interesting SQL to upate the source table in one transaction with the relevant internal ID's for the next table. The SQL gets complex, but this is the best you can do.

说明SqlBulkCopy不好的原因:

Explanation why SqlBulkCopy is bad:

SqlBulkCopy确实存在很大的问题-它确实进行了螺钉锁定.它需要一个排他的表锁(这是可以的").它要求它-但它永远不会等待(即，如果表上有任何锁，这将失败)，然后它将在一段时间后再次尝试....直到超时到期.结果是，在具有活动的表上，实际上很难使SqlBulkCopy获得任何锁.正确的代码是等待排他锁...好吧.

SqlBulkCopy really is highly problematic - it does screw locking. It demands an exclusive table lock (which is "sort of ok"). It asks for it - but it never waits (i.e. if there is any lock on the table, this fails), then it tries again.... after some time.... until a timeout expires. The result is that on a table with activity it is really hard to get SqlBulkCopy to get ANY lock. The proper code would be to wait for the exclusive lock... well.

这篇关于在一个事务中为多个相关表使用SqlBulkCopy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！