是否可以将 System.Transactions.TransactionScope 与 SqlBulkCopy 一起使用?
非常简单的问题:是否可以将 System.Transactions.TransactionScope 与 SqlBulkCopy 一起使用?文档事务和批量复制操作没有提及任何内容(至少从 .NET 4.0 开始),并且我的测试表明它不会自动加入 TransactionScope
。
Very simple question: is it possible to use System.Transactions.TransactionScope
together with SqlBulkCopy
? The documentation Transaction and Bulk Copy Operations doesn't mention anything (at least as of .NET 4.0) and my testing indicates it does not automatically enlist with TransactionScope
.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
SqlBulkCopy
从不登记到事务中。SqlCommand
也不会这样做。常见的误解。登记是在调用SqlConnection.Open
时执行的。之后,在该连接上运行的任何内容都是隐式事务的一部分。事实上,不再允许传递显式交易。如果您希望 SqlBulkCopy 使用
TransactionScope
参与System.Transactions.Transaction
,则必须在打开连接时设置事务。这很容易做到:
您只需要这段代码即可。可能的错误:
SqlBulkCopy
的SqlTransaction
参数。传递null
。SqlBulkCopyOptions.UseInternalTransaction
。using
语句进行干净的代码和确定性清理。除非必要,否则请勿手动关闭或处置任何这些物体。这将是多余的。您可以使用任何您喜欢的批量大小,并且所有批量都将成为交易的一部分。因此,批处理的价值有限(特别是事务日志不能提前截断)。首先尝试完全不进行批处理。
SqlBulkCopy
never enlists into a transaction.SqlCommand
also does not do that. Common misconception. The enlistment is performed at the timeSqlConnection.Open
is called. After that, anything that runs on that connection is part of the transaction implicitly. In fact it is no longer allowed to pass an explicit transaction.If you want
SqlBulkCopy
to take part in aSystem.Transactions.Transaction
usingTransactionScope
the transaction must be set at the time you open the connection.It is very easy to do:
This code is all you need. Possible mistakes:
SqlTransaction
parameter ofSqlBulkCopy
. Passnull
.SqlBulkCopyOptions.UseInternalTransaction
.using
statement for clean code and deterministic cleanup. Do not manually close or dispose any of these objects unless you have to. This would be redundant.You can use any batch size you like and all batches will be part of the transaction. Therefore, batching has limited value (in particular the transaction log cannot be truncated early). Try no batching at all first.
要执行跨所有批次(并且可选地跨其他数据库语句)的原子 SqlBulkCopy 导入,我们需要使用事务。以下步骤概述了通过 SqlBulkCopy 使用事务的过程:
进入构造函数。
尝试...抓住块。
如果操作完成,则提交事务;如果失败,则回滚。
通过 SqlBulkCopy 使用事务
To perform atomic SqlBulkCopy imports that span across all batches (and, optionally, across other database statements) we need to use transactions. The following steps outline the process of using a transaction with SqlBulkCopy:
into the constructor.
Try...Catch block.
If the operation completes, commit the transaction; if it fails, roll it back.
Using Transactions with SqlBulkCopy
(据我所知)在批量加载中定义事务的唯一方法是指定批量大小。
批量加载的优点是可以获得批量更新锁(多线程读取和多线程写入)。当使用 bcp、批量插入、带有 (tablock) 的 ssis 数据流任务、insert(columns)select columns from openrowset (bulk) 或 sqlbulkcopy 时,您会得到此信息。当尝试最小化加载时间和事务日志大小时,这非常方便(仅当您满足最小日志记录要求时,这将为您节省数百万行的时间)。
每当加载数据时,事务日志都将成为瓶颈。如果时间至关重要,那么尽量减少记录的数量就很重要。
一旦满足批量大小(您指定提交的行数),事务就会提交并重新开始。如果指定批量大小为 0,则事务将覆盖整个文件,并在出现任何数据问题时回滚。
The only way to define the transaction in a bulk load (to my knowledge) is to specify the batchsize.
The advantage of the bulk load is that you get a bulk update lock (multi-threaded read and a multi-threaded write). You get this when using bcp, bulk insert, a ssis data flow task with (tablock), a insert(columns)select columns from openrowset (bulk), or a sqlbulkcopy. This is handy when trying to minimize both the time to load and the transaction log size (only if you have satisfied the minimally logged requirements, which will save you hours on millions of rows).
Anytime you load data, the transaction log is going to be the bottleneck. If time is of the essence, it's important to minimize how much gets logged.
Once the batchsize is satisfied (the number of rows that you specified to commit on) the transaction gets committed and starts over. If you specify a batchsize of 0, the transaction will cover the entire file and rollback if any data issues arise.