CODEWITHSUNDEEP
c#.net.net-core
Ilya Chernomordik
Ilya Chernomordik

Reputation: 30365

Do I need to explicitly rollback transaction if the connection is dipsosed?

I was reading about transactions and saw this example from Microsoft docs. The gist from the example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction sqlTran = connection.BeginTransaction();
         
    try
    {
        // Execute some commands
        sqlTran.Commit();
    }
    catch (Exception ex)
    {   
        try
        {
            // Attempt to roll back the transaction.
            sqlTran.Rollback();
        }
        catch (Exception exRollback)
        {
            Console.WriteLine(exRollback.Message);
        }
    }
}

What I don't understand here is why do we need an explicit rollback? As far as I can see the using statement will close the connection and rollback the transaction that was not committed?

The answers for a question here are contradicting, one saying that transaction is not rolled back, while the other saying that Ado.Net connection pooling will fix it and transaction is rolled back before connection is returned to the pool (which makes sense actually).

P.S. I have actually tried to check it and it seems the disposing of the connection is enough for the transaction to be rolled back. But I guess there are some circumstances where that might not be enough

Upvotes: 0

Views: 1955

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131729

That's an unfortunate example. Perhaps they wanted to illustrate how to handle errors with try/catch before rolling back ?

The safe way to use transactions is to use a using block. You really, really don't want a transaction to remain active for any longer than necessary, as it accumulates locks which end up blocking other connections or can even cause deadlocks.

In fact, the easy way to demonstrate a deadlock is to read/modify the same data in a different order using transactions.

The following snipped would rollback the transaction immediately in case of error:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    //Do stuff that doesn't need a transaction,
    //eg read some data
    using(var sqlTran = connection.BeginTransaction())
    {
        //Do stuff that does need a transaction
        //Eg multiple UPDATEs or INSERTs
        sqlTran.Commit();
    }
}

Knowing that you're trying to work with explicit app locks in the database, you should be allergic to anything that could affect or delay a transaction's lifetime. Unlike regular row locks, app locks are very few and the chances of collision very high.

Upvotes: 5

Related Questions