C# – IDbTransaction Rollback Timeout

ado.netcrollbacksql servertransactions

I am dealing with an interesting situation where I perform many database updates in a single transaction. If these updates fail for any reason, the transaction is rolled-back.

IDbTransaction transaction
try {
    transaction = connection.BeginTransaction();

    // do lots of updates (where at least one fails)

    transaction.Commit();
} catch {
    transaction.Rollback(); // results in a timeout exception
} finally {
    connection.Dispose();
}

I believe the above code is generally considered the standard template for performing database updates within a transaction.

The issue I am facing is that whilst transaction.Rollback() is being issued to SQL Server, it is also timing out on the client.

Is there anyway of distinguishing between a timeout to issue the rollback command and a timeout on that command executing to completion?

Thanks in advance,
Ben

Best Answer

You should specify particular catches i.e. Look at the different exceptions that are thrown. If there is an error whilst processing the sql command then a SqlException should be thrown so catch the different exceptions to differentiate in your project.

Further more you could also catch and program for

Exception - An error occurred while trying to commit the transaction.

InvalidOperationException - The transaction has already been committed or rolled back. -or- The connection is broken.

Andrew

Related Topic