Alex Rivera | Logout

TransactionScope Prematurely Completed

Asked 2010-05-27T14:18:06.900
67

I have a block of code that runs within a TransactionScope and within this block of code I make several calls to the DB. Selects, Updates, Creates, and Deletes, the whole gamut. When I execute my delete I execute it using an extension method of the SqlCommand that will automatically resubmit the query if it deadlocks as this query could potentially hit a deadlock.

I believe the problem occurs when a deadlock is hit and the function tries to resubmit the query. This is the error I receive:

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

This is the simple code that executes the query (all of the code below executes within the using of the TransactionScope):

using (sqlCommand.Connection = new SqlConnection(ConnectionStrings.App))
{
    sqlCommand.Connection.Open();
    sqlCommand.ExecuteNonQueryWithDeadlockHandling();
}

Here is the extension method that resubmits the deadlocked query:

public static class SqlCommandExtender
{
    private const int DEADLOCK_ERROR = 1205;
    private const int MAXIMUM_DEADLOCK_RETRIES = 5;
    private const int SLEEP_INCREMENT = 100;

    public static void ExecuteNonQueryWithDeadlockHandling(this SqlCommand sqlCommand)
    {
        int count = 0;
        SqlException deadlockException = null;

        do
        {
            if (count > 0) Thread.Sleep(count * SLEEP_INCREMENT);
            deadlockException = ExecuteNonQuery(sqlCommand);
            count++;
        }
        while (deadlockException != null && count < MAXIMUM_DEADLOCK_RETRIES);

        if (deadlockException != null) throw deadlockException;
    }

    private static SqlException ExecuteNonQuery(SqlCommand sqlCommand)
    {
        try
        {
            sqlCommand.ExecuteNonQuery();
        }
        cat
Edit
Report

1 Answer

6

Confirmed this error can also be caused by a transaction timeout. Just to add to what Marcus + Rolf have stated, if you haven't explicitly set a timeout on the TransactionScope, the timeout TimeSpan will assume a default value. This default value is the smaller of:

  1. If you've overridden the local app.config / web.config setting, e.g.

    <system.transactions>
    <defaultSettings timeout="00:05:00" />
    </system.transactions>
    
  2. But this is then 'capped' at the machine.config setting <machineSettings maxTimeout="00:10:00" />

answered 2013-03-11T13:46:40.310

Your Answer