IDbTransaction Rollback Timeout

Question

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

Answer 1

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