SqlBulkCopy：传递SqlBulkCopyOptions.UseInternalTransaction和不传递它之间的区别是什么？

Question

我正在尝试找出使用带有SqlBulkCopyOptions.UseInternalTransaction复制选项和不使用它之间的区别，但在我的测试应用程序中，我没有检测到任何区别。如果BatchSize为0，并且我添加了100条记录（在DataTable中），其中第50条记录在将其添加到数据库表时引发错误，则在表中获取0条记录。例如，如果BatchSize设置为10，则会获得40条记录（10条记录的4个批次，第五个批次包括错误记录并导致批量复制中止）。无论是否设置SqlBulkCopyOptions.UseInternalTransaction，我始终获得相同的结果。似乎批处理总是在内部事务中复制。

如果您对我的测试应用程序感兴趣，在这里：SqlBulkCopy-Error-and-Transaction-Test.zip

我的问题是：

1. SqlBulkCopyOptions.UseInternalTransaction是否已过时，因为SqlBulkCopy始终使用内部事务？
2. 如果不是：这个选项的实际含义是什么？在哪些情况下会有所不同？

希望有人能澄清一下。

编辑：根据答案和评论，我认为我的问题不够清晰。我知道文档中写到：“默认情况下，批量复制操作是自己的事务。”并且每个批次在传递UseInternalTransaction时都使用自己的事务。但如果这意味着默认情况下批量复制操作仅使用一个事务来处理整个批量（而不是每个批次一个事务），那么如果我将BatchSize设置为一定大小，并且后面的批次出现错误，我就无法将记录保存到数据库中。如果只使用一个事务，所有添加到事务日志中的记录都将被回滚。但我得到了位于包含错误记录的批次之前的批次的记录。根据这个，似乎默认情况下每个批次都在自己的事务中执行。这意味着无论我是否传递UseInternalTransaction，都没有区别。如果我在这里走错了路，我真的很希望有人能澄清一下。

有一个事实可能很重要：我使用的是SQL Server 2012。也许SQL Server 2008的行为不同，我会检查一下。

编辑：感谢usr的回复，我想我找到了答案：我进行了调试和分析，发现如果没有定义UseInternalTransaction，则私有字段_internalTransaction确实未设置。然后SqlBulkCopy不使用自己的（内部）事务。但是分析表明，SqlBulkCopy使用TDS（表格数据流）来复制数据（无论BatchSize是什么）。我没有找到关于SQL Server特别是TDS的太多信息，但我假设SQL Server在内部事务中执行TDS批量复制操作。因此，对于SQL Server来说，UseInternalTransaction似乎有点冗余，但为了安全起见，我会将其设置。

- Jürgen Bayer

---

http://msdn.microsoft.com/en-us/library/tchktcdk%28v=vs.80%29.aspx - Tim Schmelter

谢谢Tim。是的，文档说“默认情况下，批量复制操作是自己的事务”。稍后它说：“您可以在SqlBulkCopy类构造函数中显式指定UseInternalTransaction选项，以明确导致批量复制操作在其自己的事务中执行，从而使每个批量复制操作批次在单独的事务中执行。”这很令人困惑。我的测试应用程序显示没有任何区别！请参见我对Arion答案的评论。只有当我传递自己的事务时，在出现错误的情况下，我才会添加0条记录。试试我的测试应用程序... - Jürgen Bayer

Tim Schmelter：链接无关紧要。它没有澄清如何在没有事务的情况下向服务器编写批处理的问题。如果我理解OP的问题，那么它不是关于如何使整个批量复制操作成为一个事务的问题。我确认Jurgen的发现。在这种情况下，UseInternalTransaction无效。 - JohnC

我刚使用SQL Profiler进行了检查，据我所见，使用UseInternalTransaction和不使用它之间的区别在于：如果您没有指定它，BulkCopy将使用默认的自动提交事务模式（即没有BEGIN TRAN / COMMIT TRAN语句），如果您使用UseInternalTransactions，则在BulkCopy语句周围显式地有BEGIN TRAN / COMMIT TRAN。 - Steve Ford

我应该补充说明的是，实际上这意味着它们在本质上是相同的。 - Steve Ford

Answer 1

如果您设置了此选项，则SQLBulkCopy类将添加一个

_internalTransaction = _connection.BeginTransaction();

在每个批次周围。

但是对于SQL Server来说，这个选项没有什么实际意义，因为事务默认会以自动提交模式运行。

唯一可观察到的区别是它执行验证，以确保您没有尝试传入外部事务。

以下操作将成功并回滚所有批次。

var transaction = sourceConnection.BeginTransaction();             
using (SqlBulkCopy bulkCopy =
    new SqlBulkCopy(sourceConnection, SqlBulkCopyOptions.Default, transaction))

{
    bulkCopy.BatchSize = 50;

    bulkCopy.DestinationTableName = "dbo.foobar";
        bulkCopy.WriteToServer(dt);
}

transaction.Rollback();

传递SqlBulkCopyOptions.UseInternalTransaction失败并出现错误。

不得同时指定SqlBulkCopyOption.UseInternalTransaction和传递外部事务。

我想知道，如果之前在连接上运行了SET IMPLICIT_TRANSACTIONS ON;来关闭自动提交模式，是否会有所不同，但是SqlBulkCopy构造函数的重载采用连接对象返回“意外现有的事务。”无论如何，在两种情况下都会出错-而采用连接字符串的重载只创建一个新连接。

- Martin Smith

---

如果在处理过程中的某个批次失败怎么办？如果没有使用UseInternalTransaction，它将中止后续批次；而使用将每个批次包装在单独的显式事务中的UseInternalTransaction，我希望它不会中止后续批次，对吗？ - BornToCode

@BornToCode - 如果你有一个可测试的假设，为什么不测试一下，如果你发现现有答案中没有涵盖到的内容，就添加一个答案呢？ - Martin Smith

Answer 2

BatchSize的注释描述了区别：

如果SqlBulkCopy实例在没有启用UseInternalTransaction选项的情况下声明，则每次将BatchSize行发送到服务器，但不执行与事务相关的操作。如果启用了UseInternalTransaction，则每个批次的行都将作为单独的事务插入。

换句话说，启用UseInternalTransaction意味着分批上传，然后整体插入；禁用UseInternalTransaction意味着上传并插入第一批，然后依此类推。

- Edward Brey