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

Question 1

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

.net sql-server transactions transactionscope

Shrike · May 18, 2010 · Viewed 26.1k times · Source

Answer

Answer

It's a bit late for answer :) but hope it will be useful for others. Answer contains three parts:

What does it mean "Transaction context in use by another session."
How to reproduce error "Transaction context in use by another session."

1. What does it mean "Transaction context in use by another session."

Important notice: Transaction context lock is acquired just before and released immediately after interaction between SqlConnection and SQL Server.

When you execute some SQL Query, SqlConnection "looks" is there any transaction wrapping it. It may be SqlTransaction ("native" for SqlConnection) or Transaction from System.Transactions assembly.

When transaction found SqlConnection uses it to communicate with SQL Server and at the moment they communicate Transaction context is exclusively locked.

What does TransactionScope? It creates Transaction and provides .NET Framework Components infromation about it, so everyone including SqlConnection can (and by design should) use it.

So declaring TransactionScope we're creating new Transaction which is available to all "transactable" objects instantiated in current Thread.

Described error means the following:

We created several SqlConnections under the same TransactionContext (which means they related to the same transaction)
We asked these SqlConnection to communicate with SQL Server simultaneously
One of them locked current Transaction context and next one throwed error

2. How to reproduce error "Transaction context in use by another session."

First of all, transaction context is used ("locked") right at the time of sql command execution. So it's difficult to reproduce such a behavior for sure.

But we can try to do it by starting multiple threads running relatively long SQL operations under the single transaction. Let's prepare table [dbo].[Persons] in [tests] Database:

USE [tests]
GO
DROP TABLE [dbo].[Persons]
GO
CREATE TABLE [dbo].[Persons](
    [Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [nvarchar](1024) NOT NULL,
    [Nick] [nvarchar](1024) NOT NULL,
    [Email] [nvarchar](1024) NOT NULL)
GO
DECLARE @Counter INT
SET @Counter = 500

WHILE (@Counter > 0) BEGIN
    INSERT [dbo].[Persons] ([Name], [Nick], [Email])
    VALUES ('Sheev Palpatine', 'DarthSidious', '[email protected]')
    SET @Counter = @Counter - 1
END
GO

And reproduce "Transaction context in use by another session." error with C# code based on Shrike code example

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

namespace SO.SQL.Transactions
{
    public static class TxContextInUseRepro
    {
        const int Iterations = 100;
        const int ThreadCount = 10;
        const int MaxThreadSleep = 50;
        const string ConnectionString = "Initial Catalog=tests;Data Source=.;" +
                                        "User ID=testUser;PWD=Qwerty12;";
        static readonly Random Rnd = new Random();
        public static void Main()
        {
            var txOptions = new TransactionOptions();
            txOptions.IsolationLevel = IsolationLevel.ReadCommitted;
            using (var ctx = new TransactionScope(
                TransactionScopeOption.Required, txOptions))
            {
                var current = Transaction.Current;
                DependentTransaction dtx = current.DependentClone(
                    DependentCloneOption.BlockCommitUntilComplete);               
                for (int i = 0; i < Iterations; i++)
                {
                    // make the transaction distributed
                    using (SqlConnection con1 = new SqlConnection(ConnectionString))
                    using (SqlConnection con2 = new SqlConnection(ConnectionString))
                    {
                        con1.Open();
                        con2.Open();
                    }

                    var threads = new List<Thread>();
                    for (int j = 0; j < ThreadCount; j++)
                    {
                        Thread t1 = new Thread(o => WorkCallback(dtx));
                        threads.Add(t1);
                        t1.Start();
                    }

                    for (int j = 0; j < ThreadCount; j++)
                        threads[j].Join();
                }
                dtx.Complete();
                ctx.Complete();
            }
        }

        private static void WorkCallback(DependentTransaction dtx)
        {
            using (var txScope1 = new TransactionScope(dtx))
            {
                using (SqlConnection con2 = new SqlConnection(ConnectionString))
                {
                    Thread.Sleep(Rnd.Next(MaxThreadSleep));
                    con2.Open();
                    using (var cmd = new SqlCommand("SELECT * FROM [dbo].[Persons]", con2))
                    using (cmd.ExecuteReader()) { } // simply recieve data
                }
                txScope1.Complete();
            }
        }
    }
}

And in conclusion a few words about implementing transaction support in your application:

Avoid multi-threaded data operations if it's possible (no matter loading or saving). E.g. save SELECT/UPDATE/ etc... requests in a single queue and serve them with a single-thread worker;
In multi-threaded applications use transactions. Always. Everywhere. Even for reading;
Don't share single transaction between multiple threads. It causes strange, unobvious, transcendental and not reproducible error messages:
- "Transaction context in use by another session.": multiple simultaneous interactions with server under one transaction;
- "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.": not dependent transactions were completed;
- "The transaction is in doubt.";
- ... and I assume a lot of other ...
Don't forget to set Isolation Level for TransactionScope. Default is Serializable but in most cases ReadCommitted is enough;
Don't forget to Complete() TransactionScope and DependentTransaction

Question 2

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:

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:

Main thread creates DTC transaction, receives DependentTransaction (created using Transaction.Current.DependentClone on the main thread
Child thread 1 enlists in this DTC transaction by creating a transaction scope based on the dependent transaction (passed via constructor)
Child thread 1 opens a connection
Child thread 2 enlists in DTC transaction by creating a transaction scope based on the dependent transaction (passed via constructor)
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.

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

Answer

Related questions