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?

@SergRogovtsev is not the way I am using try finally same as using using for DbConnection? If yes then it is just a preference and old habbit. — ChumboChappati
– ChumboChappati, Commented Oct 30, 2015 at 12:33
It's mostly the same, but you can make mistakes easily (for example, in your code you could try to rollback a nonexistent transaction). using is good pattern you should leverage. — Sergei Rogovtcev
– Sergei Rogovtcev, Commented Oct 30, 2015 at 14:38
@sergRogovtsev "using is good pattern you should leverage" -> not always in case of WCF client (known bug) should you wrap it within try catch finnaly to handle the dispose explicitly — bet
– bet, Commented Mar 17, 2019 at 22:28

Rob · Accepted Answer · 2015-10-29 22:37:35Z

6

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.

answered Oct 29, 2015 at 22:37

Rob♦

27.5k16 gold badges89 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

abatishchev Over a year ago

Why do you need this rather than the clear new SqlConnection()?

ChumboChappati Over a year ago

I do need to propagate up exceptions and display as an error to the user. I made the change in my code above to throw MyWorkFailedException again after rollback. Assume I have a custom exception InfrastructureException; how would you throw this exception in your code if commit or rollback failed?

abatishchev · Accepted Answer · 2015-10-29 22:43:10Z

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();
}

answered Oct 29, 2015 at 22:43

abatishchev

101k88 gold badges303 silver badges443 bronze badges

2 Comments

ChumboChappati Over a year ago

I thought TransactionScope is only needed if your transaction span multiple databases?

Sergei Rogovtcev Over a year ago

Actually not, it works with any compliant provider, and its somewhat cleaner than using DbTransactions

Sergei Rogovtcev · Accepted Answer · 2015-10-29 22:32:40Z

1

You handle the exception in any of the methods (both Commit and Rollback) as failure in transaction. If you want/have to, you can clean up something in your C# code. Database cleanup will be handled automatically (at least, for ACID-compliant DB).

answered Oct 29, 2015 at 22:32

Sergei Rogovtcev

5,8302 gold badges24 silver badges35 bronze badges

1 Comment

Sergei Rogovtcev Over a year ago

The one in Rob's answer will do.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related