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.

Answer 1

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

Answer 2

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:

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