Why is a nested transaction committed even if TransactionScope.Complete() is never called?

Question

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.

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?

The database is SQL Server 2008 R2.

Dowsabel

If you are going to use one TransactionScope per query, why do you use them at all ? – Lorica

Answer 1

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

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

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

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"

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...

Adalineadall

Answer 2

Your second Command object is being created on conn1, not conn2, so it's very much like the other question - the connection on which you're running the command was opened before the second transaction scope was opened.

Mural