Is this TRANSACTION being ROLLBACK(ed) for me?

Question

If I cause an error by trying to create an existing table, the existing transaction appears to have already rolled back itself:

private void CreateSomeThings()
{
    SqlConnection SqlConn = new SqlConnection(ConnectionString);
    SqlConn.Open();

    (new SqlCommand("BEGIN TRANSACTION", SqlConn)).ExecuteNonQuery();

    try
    {
        (new SqlCommand("CREATE TABLE sometable ([some_id] [int] IDENTITY(1,1) NOT NULL)", SqlConn)).ExecuteNonQuery();

        // Create the table again, but carry on by catching the exception
        try
        {
            (new SqlCommand("CREATE TABLE sometable ([some_id] [int] IDENTITY(1,1) NOT NULL)", SqlConn)).ExecuteNonQuery();
        }
        catch (Exception)
        {
        }

        // If another exception is thrown
        (new SqlCommand("bingy bongy boo", SqlConn)).ExecuteNonQuery();

        (new SqlCommand("COMMIT TRANSACTION", SqlConn)).ExecuteNonQuery();
    }
    catch (Exception Ex)
    {
        try
        {
            // ... then this command will fail with "no corresponding BEGIN TRANSACTION"
            (new SqlCommand("ROLLBACK TRANSACTION", SqlConn)).ExecuteNonQuery();
        }
        catch (Exception Ex2)
        {
            throw;
        }
    }
}

I'd like to understand what's going on and why. I would expect that transaction rollback is my responsibility - with other errors it doesn't do that: for example, if I just call "bingy bongy" only the call throws an exception and I then ROLLBACK in the exception without any issues.

Please see the code comments. The `ROLLBACK TRANSACTION` fails in the exception handler. — noelicus, Oct 21 '13 at 11:13
And the problem is that "I don't get it" and I'd like to understand what's going on and why. I would expect that transaction rollback is my responsibility - with other errors it doesn't do that for me. — noelicus, Oct 21 '13 at 11:14
Every query you have here are individual transaction so no your rollback wont work. You need to make either use of transaction object or more generic function to wrap a transac SQL in BEGIN/ROLLBACK/COMMIT transaction with errorraise() and error trapping SQL SIDE. — Franck, Oct 21 '13 at 11:34
That may be so, but if I change my code to use a transaction object I get the same issue: if you try to CREATE a TABLE that already exists the transaction object will have been closed/rolled back on my behalf. If I do some *other* error (such as calling `bingy bongy boo`) the transaction remains valid and *I* am now responsible for the rollback. Doesn't seem very consistent to me. — noelicus, Oct 21 '13 at 11:42
Does this answer your question? [Is Rollback automatic in a "Using" scope with C# SQL Server calls?](https://stackoverflow.com/questions/18388852/is-rollback-automatic-in-a-using-scope-with-c-sharp-sql-server-calls) — Jim G., Jul 01 '21 at 19:20
@JimG. thanks for the thought, but I don't think so. The selected answer below answered it really. That link is about what happens in a dispose, not when SQL Server rolls back for you internally. — noelicus, Jul 07 '21 at 11:23

score 3 · Accepted Answer · answered Oct 21 '13 at 13:24

SQL Server can unilaterally decide to rollback your transaction. This is a severe design flaw in SQL Server because your app can never know whether the transaction is still active or not. It is not well documented what kinds of errors roll back and what kinds of errors don't. For example, I think I remember that unique key violations and other data errors do not roll back. But others do. Some errors even terminate the connection (that is rare and not a design flaw).

I recommend that you code in such a way that you abort the transaction at the first error and then either fail or retry everything. That saves you a lot of headaches. Prefer to execute one statement per batch, or you risk running the 2nd statement outside of a transaction.

If you really want to keep going after errors you must do two things:

Build a whitelist of errors that do not roll back. In that case you can keep going.
Check with SELECT @@TRANCOUNT whether the transaction is still live.

By executing one statement per batch, do you effectively recommend not using transactions? — Szymon, Oct 21 '13 at 13:56
I think you are confusing some terms here, although I'm not sure which ones. A connection can have multiple transactions which can have multiple batches which can have multiple statements. Every `ExecuteNonQuery` call is a batch, that can have multiple statements in its `CommandText`. Use transactions, but one statement per batch if possible. — usr, Oct 21 '13 at 15:31

Szymon · Answer 2 · 2013-10-21T11:27:35.067

0

You need to pass a transaction object to every command you're using to make them take part in the same transaction.

The usual pattern is:

using (var conn = new SqlConnection("your connection string here"))
{
    SqlTransaction trans = null;
    try
    {
        conn.Open();
        trans = conn.BeginTransaction();

        using (SqlCommand command = new SqlCommand("command text here", conn, trans))
        {
            // do your job
        }
        trans.Commit();
    }
    catch (Exception ex)
    {
        try
        {
            // Attempt to roll back the transaction.
            if (trans != null) trans.Rollback();
        }
        catch (Exception exRollback)
        {
            // Throws an InvalidOperationException if the connection  
            // is closed or the transaction has already been rolled  
            // back on the server.
        }
    }
}

edited Oct 21 '13 at 11:27

answered Oct 21 '13 at 11:16

Szymon

42,577
16
96
114

If I use C# connection.BeginTransaction instead the Transaction becomes invalid (the Connection becomes null inside the object) - i.e. appears to behave the same way. – noelicus Oct 21 '13 at 11:19
If part of that transaction is to create a table that already exists your transaction will no longer be valid - it will throw another exception if you *then try to do trans.Rollback()*. My question is ... why? – noelicus Oct 21 '13 at 11:23
That transaction has already been rolled back on the server. Try to run the same in SSMS - if there's an error in your queries that are inside the transaction, it is rolled back. I updated my answer to reflect that. – Szymon Oct 21 '13 at 11:28
1

But "bingy bongy boo" is hardly valid SQL, but that doesn't cause a rollback if that's the only call I make ... why the inconsistency? – noelicus Oct 21 '13 at 11:39
Not sure about that one. Maybe because it's not a valid sql and there's nothing to roll back? – Szymon Oct 21 '13 at 12:28
The problem is that SQL Server sometimes aborts the transaction without the client knowing about it. I don't think this protects against that. – usr Oct 21 '13 at 13:27

Is this TRANSACTION being ROLLBACK(ed) for me?

2 Answers2

Linked