Is Rollback automatic in a "Using" scope with C# SQL Server calls?

Question

When you create 'using' blocks for your SQL Connection, Transaction, and Command, it is well known that the connection, transaction, or command that the using block is associated with is disposed on its own properly after you leave the using block.

If an exception occurs in one of these blocks though, for instance in the command block - Would the transaction be rolled back on its own, or do developers need to do a try catch inside of the command 'using' block, and add a rollback transaction statement in the catch for this try?

score 5 · Answer 1 · answered Aug 22 '13 at 19:22

5

The transaction is rolled back automatically as long as you haven't successfully called Commit. So your using blocks can look something like this, and the transaction will be rolled back if an exception is thrown before the Commit.

using (IDbConnection connection = ...)
{
    connection.Open();
    using (IDbTransaction transaction = connection.BeginTransaction())
    {
        using (IDbCommand command = ...)
        {
            command.Connection = connection;
            command.Transaction = transaction;
            ...
        }
        ...
        transaction.Commit();
    }
}

answered Aug 22 '13 at 19:22

Joe

122,218
32
205
338

1

I would argue that there is in fact a condition in which is will not get rolled back. However slight it may be, the `Dispose(bool)` logic of the `SqlTransaction` is quite fragile. – Mike Perrenoud Aug 22 '13 at 19:24
And I would argue that the above is the correct pattern to use in your code. Obviously there are cases when the rollback won't occur - e.g. loss of connectivity to the database - but there's nothing you can do about that, and the database server will be responsible for tidying up (which will no doubt include a rollback). – Joe Aug 23 '13 at 05:00
I can see your perspective friend. And you may be right about the database server issuing a rollback if connectivity is lost. I guess I'm just a bit appalled at the Microsoft code above and how fragile it is. There are times when it will fail to rollback. Further, yes the structure of the code is perfect. – Mike Perrenoud Aug 23 '13 at 11:17
@TheSolution - " I'm just a bit appalled at the Microsoft code above and how fragile it is" - I don't agree with you there. I suspect it was developed by top notch engineers who carefully considered what is possible and what you have to leave to the database to sort out. An application developer can rely on Dispose to roll back an uncommitted transaction if it's at all possible. – Joe Aug 23 '13 at 16:00

Mike Perrenoud · Accepted Answer · 2013-08-22T19:27:04.053

It's not guaranteed to get disposed. The Dispose(bool) method of the SqlTransaction will in fact roll it back conditionally:

// System.Data.SqlClient.SqlTransaction
protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        SNIHandle target = null;
        RuntimeHelpers.PrepareConstrainedRegions();
        try
        {
            target = SqlInternalConnection.GetBestEffortCleanupTarget(this._connection);
            if (!this.IsZombied && !this.IsYukonPartialZombie)
            {
                this._internalTransaction.Dispose();
            }
        }
        catch (OutOfMemoryException e)
        {
            this._connection.Abort(e);
            throw;
        }
        catch (StackOverflowException e2)
        {
            this._connection.Abort(e2);
            throw;
        }
        catch (ThreadAbortException e3)
        {
            this._connection.Abort(e3);
            SqlInternalConnection.BestEffortCleanup(target);
            throw;
        }
    }
    base.Dispose(disposing);
}

and if you notice, it would only happen if this._internalTransaction.Dispose(); got called. The problem here is that if GetBestEffortCleanupTarget throws an exception it won't get cleaned up.

In your case, as long as an exception isn't thrown as already stated, you will fall into the category of being Zombied and so it will then actually issue a Rollback call in the _internalTransaction.Dispose() call.

Finally, if this is called with false it will most certainly not get disposed.

Now, unless I'm really missing something here I'm a bit appalled at how fragile this code is.

An interesting note is that I think the MSDN documentation is actually wrong because it states, for the Rollback() method:

The transaction can only be rolled back from a pending state (after BeginTransaction has been called, but before Commit is called). The transaction is rolled back in the event it is disposed before Commit or Rollback is called.

[GetBestEffortCleanupTarget](https://referencesource.microsoft.com/system.data/R/09d7a185c9726087.html) has no code that could throw. And "into the category of being Zombied" should say **not** Zombied. Furthermore, if a transaction is dropped by the client and `XACT_ABORT` is set ON, then the [server will rollback anyway](https://dba.stackexchange.com/questions/10912/sql-server-transaction-timeout) when the connection is aborted, this is why `XACT_ABORT` is recommended. — Charlieface, Feb 14 '21 at 12:21

Is Rollback automatic in a "Using" scope with C# SQL Server calls?

2 Answers2

Linked

Related