What is the reason of "Transaction context in use by another session"

Question

I'm looking for a description of the root of this error: "Transaction context in use by another session".

I get it sometimes in one of my unittests so I can't provider repro code. But I wonder what is "by design" reason for the error.

UPDATE: the error returns as SqlException from SQL Server 2008. A place where I get the error seems to be single-threaded. But probably I have unittests interaction as I get the error where run several tests at once (MSTest in VS2008sp1). But the failing test looks like:

• create an object and save it inside DB-transaction (commit)
• create TransactionScope
• trying to open a connection - here I get SqlException with such stacktrace:

.

System.Data.SqlClient.SqlException: Transaction context in use by another session.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()

I've found these posts:

• http://blogs.msdn.com/asiatech/archive/2009/08/10/system-transaction-may-fail-in-multiple-thread-environment.aspx
• http://msdn.microsoft.com/en-us/library/ff649002.aspx

But I can't understand what "Multiple threads sharing the same transaction in a transaction scope will cause the following exception: 'Transaction context in use by another session.'" means. All words are understandable but not the point.

I actually can share a system transaction between threads. And there is even special mechanism for this - DependentTransaction class and Transaction.DependentClone method.

I'm trying to reproduce a usecase from the first post:

1. Main thread creates DTC transaction, receives DependentTransaction (created using Transaction.Current.DependentClone on the main thread
2. Child thread 1 enlists in this DTC transaction by creating a transaction scope based on the dependent transaction (passed via constructor)
3. Child thread 1 opens a connection
4. Child thread 2 enlists in DTC transaction by creating a transaction scope based on the dependent transaction (passed via constructor)
5. Child thread 2 opens a connection

with such code:

using System;
using System.Threading;
using System.Transactions;
using System.Data;
using System.Data.SqlClient;

public class Program
{
    private static string ConnectionString = "Initial Catalog=DB;Data Source=.;User ID=user;PWD=pwd;";

    public static void Main()
    {
        int MAX = 100;
        for(int i =0; i< MAX;i++)
        {
            using(var ctx = new TransactionScope())
            {
                var tx = Transaction.Current;
                // make the transaction distributed
                using (SqlConnection con1 = new SqlConnection(ConnectionString))
                using (SqlConnection con2 = new SqlConnection(ConnectionString))
                {
                    con1.Open();
                    con2.Open();
                }
                showSysTranStatus();

                DependentTransaction dtx = Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete);
                Thread t1 = new Thread(o => workCallback(dtx));
                Thread t2 = new Thread(o => workCallback(dtx));
                t1.Start();
                t2.Start();
                t1.Join();
                t2.Join();

                ctx.Complete();
            }
            trace("root transaction completes");
        }
    }
    private static void workCallback(DependentTransaction dtx)
    {
        using(var txScope1 = new TransactionScope(dtx))
        {
            using (SqlConnection con2 = new SqlConnection(ConnectionString))
            {
                con2.Open();
                trace("connection opened");
                showDbTranStatus(con2);
            }
            txScope1.Complete();
        }   
        trace("dependant tran completes");
    }
    private static void trace(string msg)
    {
        Console.WriteLine(Thread.CurrentThread.ManagedThreadId + " : " + msg);
    }
    private static void showSysTranStatus()
    {
        string msg;
        if (Transaction.Current != null)
            msg = Transaction.Current.TransactionInformation.DistributedIdentifier.ToString();
        else
            msg = "no sys tran";
        trace( msg );
    }

    private static void showDbTranStatus(SqlConnection con)
    {
        var cmd = con.CreateCommand();
        cmd.CommandText = "SELECT 1";
        var c = cmd.ExecuteScalar();
        trace("@@TRANCOUNT = " + c);
    }
}

It fails on Complete's call of root TransactionScope. But error is different: Unhandled Exception: System.Transactions.TransactionInDoubtException: The transaction is in doubt. ---> pired. The timeout period elapsed prior to completion of the operation or the server is not responding.

To sum up: I want to understand what "Transaction context in use by another session" means and how to reproduce it.

Answer 1

"Multiple threads sharing the same transaction in a transaction scope will cause the following exception: 'Transaction context in use by another session.'"

Sounds pretty straightforward. If you enlist two different connections in the same transaction, then try to issue commands on each of the two connections, simultaneously, from different threads, a conflict could occur.

In other words, one thread is issuing a command on one connection and holds some kind of lock on the transaction context. The other thread, using the other connection, tries to execute commands at the same time, and cannot lock the same transaction context, which is being used by the other thread.

Answer 2

Take a step back and focus more on your code and less in the multiple threads info floating around.

If your scenario doesn't involve threading, it might relate to pieces that aren't closed as you expect it to.

Maybe the sql code you are calling doesn't reach that commit transaction instruction. Or there is something else involved at that level. Maybe you used a SqlConnection instance setting the transaction in the .net code, and are reusing that same instance on the other code that uses the TransactionScope. Try adding using() instructions where appropriate, to make sure everything is closed as you expect it.

Answer 3

How I'd deal with that issue when building Linq statements with mutlipe objects is to have a constructor for each class that takes in a data context and a coresponding GetDataContext() method in each class. when combining classes, I'd new up the class instances passing in the first class's GetContext()

  public class CriterionRepository : ICriterionRepository
    {

        private Survey.Core.Repository.SqlDataContext _context = new Survey.Core.Repository.SqlDataContext();

        public CriterionRepository() { }

        public CriterionRepository(Survey.Core.Repository.SqlDataContext context)
        {            
            _context = context;
        }

...


        public Survey.Core.Repository.SqlDataContext GetDataContext()
        {
            return _context;
        }

}