How to handle if DbTransaction's Commit() or Rollback() throw exception?

Question

I am working with Visual Studio 2012 and MS SQL Server 2008 R2.

In my code I am using DbConnection and DbTransaction. This is my code:

DbConnection dbConnection = null;
DbTransaction dbTransaction = null;

try
{
   dbConnection = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateConnection();
   dbConnection.ConnectionString = connectionString;
   dbConnection.Open();
   dbTransaction = dbConnection.BeginTransaction();
   // do my work using dbConnection and dbTransaction
   dbTransaction.Commit();
} 
catch (MyWorkFailedException mwfe)
{
   dbTransaction.Rollback();
   throw;
}
finally
{
   if (dbConnection != null)
   {
      dbConnection.Close();
      dbConnection.Dispose();
   }
}

Is it possible that dbTransaction.Commit(); or dbTransaction.Rollback(); throws an exception?

If yes then how to handle it in my code? How c# programmers usually handles this situation? Or they dont handle this situation?

Answer 1

Yes, both Commit and Rollback can throw exceptions. However, these should probably be propagated up and either logged or displayed as an error to the user. How you want to handle the error is entirely up to you, but typically the errors will be due to a closed connection. Secondly, you should leverage using

using (var dbConnection  = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateConnection())
{
    dbConnection.ConnectionString = connectionString;
    dbConnection.Open();
    using (var dbTransaction = dbConnection.BeginTransaction())
    {
        //Do work here

        dbTransaction.Commit();
    }
}

DbTransaction will automatically rollback on its dispose method (assuming it hasn't been committed). Exceptions thrown by this code typically are not something you can gracefully handle. For the most part, they would come from SQL errors (invalid syntax, FK errors, etc), or a closed connection.

As long as you've got a good logging system in place, the above code should be all you need.

Answer 2

Use TransactionScope instead which has only Complete(). Once it has been disposed it will rollback the underlying transaction. Any exception will cause the underlying transaction to be rolledback as well:

using (var scope = new TransactionScope())
using (var connection = new SqlConnection(...))
{
    // do stuff

    scope.Complete();
}