Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Problems with TransactionScope in ASP.NET

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();
        }
    }
like image 936
user740229 Avatar asked May 05 '11 15:05

user740229


2 Answers

Try changing the constructor.

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required,
                new TransactionOptions()
                { 
                    IsolationLevel = System.Transactions.IsolationLevel.Serializable,
                    Timeout = TimeSpan.FromSeconds(120)
                }))
like image 123
William Xifaras Avatar answered Nov 15 '22 14:11

William Xifaras


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())
like image 44
Bongo Sharp Avatar answered Nov 15 '22 15:11

Bongo Sharp