为什么即使从未调用 TransactionScope.Complete() 也会提交嵌套事务? [英] Why is a nested transaction committed even if TransactionScope.Complete() is never called?

问题描述

我正在测试嵌套事务的工作原理，并发现了这种令人不安和意外的行为.

I was testing to see how nested transactions work, and uncovered this disturbing and unexpected behavior.

using(TransactionScope otx = new TransactionScope())
using(SqlConnection conn1 = new SqlConnection("Server=S;Database=DB;Trusted_Connection=yes"))
using(SqlCommand cmd1 = conn1.CreateCommand())
{
    conn1.Open();
    cmd1.CommandType = CommandType.Text;
    cmd1.CommandText = "INSERT INTO FP.ACLs (ChangeToken,ACL) VALUES (1,0x)";
    cmd1.ExecuteNonQuery();

    using(TransactionScope itx = new TransactionScope(TransactionScopeOption.RequiresNew))
    using(SqlConnection conn2 = new SqlConnection("Server=S;Database=DB;Trusted_Connection=yes"))
    using(SqlCommand cmd2 = conn1.CreateCommand())
    {
        conn2.Open();
        cmd2.CommandType = CommandType.Text;
        cmd2.CommandText = "INSERT INTO FP.ACLs (ChangeToken,ACL) VALUES (2,0x)";
        cmd2.ExecuteNonQuery();
        // we don't commit the inner transaction
    }

    otx.Complete(); // nonetheless, the inner transaction gets committed here and two rows appear in the database!
}

我看到了另一个问题，但该解决方案不适用.

I saw this other question, but the solution did not apply.

如果我不指定TransactionScopeOption.RequiresNew(即我不使用嵌套事务，只使用嵌套作用域)，那么当内部作用域未完成时整个事务回滚，调用时会出错otx.Complete().这很好.

If I don't specify TransactionScopeOption.RequiresNew (i.e. I don't use a nested transaction, just a nested scope), then the entire transaction is rolled back when the inner scope is not completed, and an error occurs when calling otx.Complete(). This is fine.

但我当然不希望嵌套事务在未成功完成时提交！有人知道这里发生了什么以及我如何获得预期的行为吗?

But I certainly don't expect a nested transaction to be committed when it did not complete successfully! Does anybody know what is going on here and how I can get the expected behavior?

数据库为 SQL Server 2008 R2.

The database is SQL Server 2008 R2.

推荐答案

首先，有 在 SQL Server 中没有嵌套事务之类的东西.这很重要.

First off, there is no such thing as a nested transaction in SQL Server. This is important.

其次，两个 TransactionScope 都使用 conn1，因此您(在 SQL Server 级别)为每个 BEGIN TRANSACTION

Second, both TransactionScopes use conn1 so you are (at the SQL Server level) incrementing @@TRANCOUNT for each BEGIN TRANSACTION

简单解释:当外层事务提交时，内层事务也被提交，因为回滚内部事务会回滚both事务

Simple explanation: the inner transaction is committed when the outer transaction commits because rolling back the inner would rollback both transactions

也就是说，COMMIT TRANSACTION(由 .Complete 和 .Dispose 暗示)递减 @@TRANCOUNT 而ROLLBACK TRANSACTION(仅由 .Dispose 暗示)将其归零.所以内部回滚被抑制，因为没有嵌套事务"

That is, COMMIT TRANSACTION (implied by .Complete and .Dispose) decrements @@TRANCOUNT while ROLLBACK TRANSACTION (implied by .Dispose only) takes it back to zero. So the inner rollback is suppressed because of "no such thing as nested transactions"

如果您在内部 ' 范围内正确使用了 conn2，它将按预期工作，因为这 2 个事务在数据库服务器级别是不相关的.这才是重要的地方...

If you'd used conn2 correctly in the inner 'scope it would work as expected because the 2 transactions are unrelated at the database server level. Which is where it matters...

这篇关于为什么即使从未调用 TransactionScope.Complete() 也会提交嵌套事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！