Problems with TransactionScope in ASP.NET

Question

I've build a class to synchronize data between two different datasources. This synchronization is divided into multiple parts (and methods). Every method has his own TransactionScope and the methods are run sequentially.

Everytime I Run this code I get the following errormessage:

"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."

The following code is an example of such a method with a TransactionScope:

private void SomeMethod()
{
        try
        {
            using (var _transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
            {
                using (SqlConnection _connection = new SqlConnection(connectionstring))
                {
                    _connection.Open();

                    DoSomething()...
                }

                _transactionScope.Complete();
            }
        }
        catch (TransactionAbortedException e)
        {
            nlog.Error(string.Format("The transaction has been aborted: {0}", e.Message));
            throw e;
        }
        catch (Exception e)
        {
            throw e;
        }
}

It seems that the call "_transactionScope.Complete()" isn't enough to kill the transactionscope.. Does anyone have a clue what i'm doing wrong?

Thanks in advance!

UPDATE Thanks for your replies. After a few tests I discovered that this problem only exists when there are multiple queries in one method. for example:

 try
    {
        using (TransactionScope _transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
        {
            using (SqlConnection _connection = new SqlConnection(connectionstring))
            {
                _connection.Open();

                //new method:
                using (TransactionScope _transactionScope = new TransactionScope(TransactionScopeOption.Suppress))
                {
                    //a selectquery
                }

                //an update or insert query

            _transactionScope.Complete();
        }
    }

This error can occur if the transaction runs for a longer period than the maxTimeout. See this blogpost: http://thecodesaysitall.blogspot.se/2012/04/long-running-systemtransactions.html or this answer: http://stackoverflow.com/a/10017056/205023 — Marcus, Apr 04 '12 at 18:24
There are some transactions I think that last more then 10 minutes, but I handled that by setting the 'transactionOptions.Timeout' property and de SqlCommand.CommandTimeout property to one hour. — user740229, May 07 '11 at 09:51
Just a technical note... don't "throw e", use just "throw". This preserves the call stack. — Sprague, Feb 01 '16 at 12:07

score 5 · Answer 1 · answered May 05 '11 at 15:58

Try changing the constructor.

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required,
                new TransactionOptions()
                { 
                    IsolationLevel = System.Transactions.IsolationLevel.Serializable,
                    Timeout = TimeSpan.FromSeconds(120)
                }))

score 3 · Answer 2 · answered May 05 '11 at 23:54

I did a method for creating a Max Timeout value on a transaction scope

public static TransactionScope CreateDefaultTransactionScope(TransactionScopeOption option = TransactionScopeOption.Required)
    {
        var transactionOptions = new TransactionOptions();
        transactionOptions.Timeout = TimeSpan.MaxValue;
        transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
        return new TransactionScope(option, transactionOptions);
    }

and then you would use it:

using (TransactionScope transaction = TransactionHelper.CreateDefaultTransactionScope())

Check out this link as well: http://social.msdn.microsoft.com/forums/en-US/windowstransactionsprogramming/thread/250b40b9-0838-4142-a8ff-d9d26690083b/ — Bongo Sharp, May 06 '11 at 16:42

Problems with TransactionScope in ASP.NET

2 Answers2