.net “另一个会话正在使用事务上下文”是什么原因

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

提问by Shrike

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:

更新：错误从 SQL Server 2008 返回为 SqlException。出现错误的地方似乎是单线程的。但可能我有单元测试交互，因为我得到了一次运行多个测试的错误（VS2008sp1 中的 MSTest）。但失败的测试看起来像：

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

• 创建一个对象并将其保存在 DB-transaction 中（提交）
• 创建事务范围
• 试图打开一个连接 - 在这里我得到 SqlException 与这样的堆栈跟踪：

.

.

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

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

我实际上可以在线程之间共享系统事务。甚至还有特殊的机制 - DependentTransaction 类和 Transaction.DependentClone 方法。

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

1. 主线程创建 DTC 事务，接收 DependentTransaction（在主线程上使用 Transaction.Current.DependentClone 创建
2. 子线程 1 通过创建基于依赖事务的事务范围（通过构造函数传递）在此 DTC 事务中登记
3. 子线程 1 打开一个连接
4. 子线程 2 通过创建基于依赖事务的事务范围（通过构造函数传递）在 DTC 事务中登记
5. 子线程2打开一个连接

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.

它在 Complete 调用根 TransactionScope 时失败。但错误是不同的：未处理的异常：System.Transactions.TransactionInDoubtException：事务有疑问。---> 累了。操作完成前超时时间已过或服务器未响应。

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

总结一下：我想了解“另一个会话正在使用的事务上下文”是什么意思以及如何重现它。

回答by Ilya Chidyakin

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

回答有点晚了 :) 但希望它对其他人有用。答案包含三个部分：

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

1. “另一个会话正在使用的事务上下文”是什么意思。
2. 如何重现错误“另一个会话正在使用事务上下文”。

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

1. “另一个会话正在使用的事务上下文”是什么意思。

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

重要提示：事务上下文锁在SqlConnection与 SQL Server交互之前获取并在交互之后立即释放。

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

当您执行一些 SQL 查询时，SqlConnection“看起来”是否有任何事务包装它。它可能是SqlTransaction（SqlConnection 的“本机”）或Transaction来自System.Transactions程序集。

When transaction found SqlConnectionuses it to communicate with SQL Server and at the moment they communicate Transactioncontext is exclusively locked.

当找到的事务SqlConnection使用它与 SQL Server 进行通信并且此时它们进行通信时Transaction上下文被独占锁定。

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

有什么作用TransactionScope？它创建Transaction并提供有关它的 .NET Framework 组件信息，因此包括 SqlConnection 在内的每个人都可以（并且按照设计应该）使用它。

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

所以声明TransactionScope我们正在创建新的 Transaction ，它可用于 current 中实例化的所有“transactable”对象Thread。

Described error means the following:

描述的错误含义如下：

1. We created several SqlConnectionsunder the same TransactionContext(which means they related to the same transaction)
2. We asked these SqlConnectionto communicate with SQL Server simultaneously
3. One of them locked current Transactioncontext and next one throwed error

1. 我们在SqlConnections同一个下创建了几个TransactionContext（这意味着它们与同一个交易相关）
2. 我们要求这些SqlConnection同时与 SQL Server 通信
3. 其中一个锁定当前Transaction上下文，下一个抛出错误

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

2. 如何重现错误“另一个会话正在使用事务上下文”。

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.

首先，在 sql 命令执行时使用（“锁定”）事务上下文。所以很难肯定地重现这种行为。

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:

但是我们可以尝试通过在单个事务下启动多个线程运行相对较长的 SQL 操作来实现。让我们[dbo].[Persons]在[tests]数据库中准备表：

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

并重现“另一个会话正在使用的事务上下文”。基于 Shrike 代码示例的 C# 代码错误

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 reproducibleerror 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 Serializablebut in most cases ReadCommittedis enough;
• Don't forget to Complete() TransactionScopeand DependentTransaction

• 尽可能避免多线程数据操作（无论是加载还是保存）。例如，保存SELECT/UPDATE的/ etc ...在一个单一的队列中的请求，并用单线程工作人员为他们服务;
• 在多线程应用程序中使用事务。总是。到处。即使是阅读；
• 不要在多个线程之间共享单个事务。它会导致奇怪的、不明显的、超然的且不可重现的错误消息：
  • “另一个会话正在使用的事务上下文。”：在一个事务下与服务器的多个同时交互；
  • “超时到期。在操作完成之前超时时间已过或服务器没有响应。”：未完成相关事务；
  • “交易存在疑问。”;
  • ......我假设还有很多其他......
• 不要忘记为 设置隔离级别TransactionScope。默认是Serializable但在大多数情况下ReadCommitted就足够了；
• 不要忘记 Complete()TransactionScope和DependentTransaction

回答by Triynko

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

换句话说，一个线程在一个连接上发出一个命令，并在事务上下文上持有某种锁。使用另一个连接的另一个线程尝试同时执行命令，并且无法锁定另一个线程正在使用的相同事务上下文。

回答by eglasius

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.

也许您正在调用的 sql 代码没有达到该提交事务指令。或者在那个层面上还涉及其他一些事情。也许您使用 SqlConnection 实例在 .net 代码中设置事务，并在使用 TransactionScope 的其他代码上重用相同的实例。尝试在适当的地方添加 using() 指令，以确保一切都按预期关闭。

回答by James Fleming

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()

在使用多对象构建 Linq 语句时，我如何处理这个问题是为每个类都有一个构造函数，它接受一个数据上下文，并在每个类中都有一个相应的 GetDataContext() 方法。组合类时，我会更新传入第一个类的 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;
        }

}

回答by Tochas

You must create a DependentTransactionfor each thread an then inside the thread create & open the db connection inside a TransacctionScopeusing the dependentTransactionin the ctor.

您必须DependentTransaction为每个线程创建一个，然后在线程内部TransacctionScope使用dependentTransactionctor 中的创建和打开 db 连接。

            //client code / main thread
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, timeout))
            {
                Transaction currentTransaction = Transaction.Current;
                currentTransaction.TransactionCompleted += OnCompleted;
                DependentTransaction dependentTransaction;
                int nWorkers = Config.Instance.NumDBComponentWorkers;
                for (int i = 0; i < nWorkers; i++)
                {
                    dependentTransaction = currentTransaction.DependentClone(DependentCloneOption.BlockCommitUntilComplete);
                    this.startWorker(dependentTransaction);
                }
                do
                {
                    //loop + wait
                    Thread.Sleep(150);
                } while (this.status == DBComponentStatus.Running);
                //No errors-commit transaction
                if (this.status == DBComponentStatus.Finished && this.onCanCommit())
                {
                    scope.Complete();
                }
            }

    //workers
    protected override void startWorker(DependentTransaction dependentTransaction)
    {
        Thread thread = new Thread(workerMethod);
        thread.Start(dependentTransaction);
    }

    protected override void workerMethod(object transaction)
    {
        int executedStatements = 0;
        DependentTransaction dependentTransaction;
        dependentTransaction = transaction as DependentTransaction;
        System.Diagnostics.Debug.Assert(dependentTransaction != null); //testing
        try
        {
            //Transaction.Current = dependentTransaction;
            using (TransactionScope scope = new TransactionScope(dependentTransaction))
            {
                using (SqlConnection conn = new SqlConnection(this.GetConnectionString(this.parameters)))
                {
                    /* Perform transactional work here */
                    conn.Open();
                    string statement = string.Empty;
                    using (SqlCommand cmd = conn.CreateCommand())
                    {

                    }
                }
                //No errors-commit transaction
                if (this.status == DBComponentStatus.Finished)
                {
                    scope.Complete();
                }
            }
        }
        catch (Exception e)
        {
            this.status = DBComponentStatus.Aborted;
        }
        finally
        {
            dependentTransaction.Complete();
            dependentTransaction.Dispose();
        }
    }

回答by Aaron

I have a multi-threaded application that does some data manipulation and stores the results in the database. Because different threads are working on different types of data, writing code to collect the results and flush it out to the database in one thread is more cumbersome than just having each thread write the results out itself when it is done.

我有一个多线程应用程序，它执行一些数据操作并将结果存储在数据库中。由于不同的线程处理不同类型的数据，因此在一个线程中编写代码来收集结果并将其刷新到数据库中，这比让每个线程在完成后自行写出结果要麻烦得多。

I wanted to run this in a transaction, so that I have the option to revert all of the work in the event that an error happens in any one of the child threads. Adding transactions started causing problems, which led me to this posting, but I was able to work through them. Multi-threaded database access in a single transaction is possible. I'm even using both LINQ-to-SQL and SqlBulkCopy together in the same transaction.

我想在一个事务中运行它，这样我就可以选择在任何一个子线程中发生错误时恢复所有工作。添加交易开始引起问题，这导致我发帖，但我能够解决它们。单个事务中的多线程数据库访问是可能的。我什至在同一个事务中同时使用 LINQ-to-SQL 和 SqlBulkCopy。

I found Ilya Chidyakin's answer to be very helpful. You need to pass a DependentTransaction to each thread, and use that to create a new TransactionScope. And, you need to remember to commit both the TransactionScope and the DependentTransaction in each thread. Finally, you must wait to commit your "original" transaction until all of the child work is done. (DependentTransaction should take care of this, actually, but I was already using Thread.Join to wait for all of the work to be done, before I added transactions to this project.)

我发现 Ilya Chidyakin 的回答非常有帮助。您需要将 DependentTransaction 传递给每个线程，并使用它来创建新的 TransactionScope。而且，您需要记住在每个线程中提交 TransactionScope 和 DependentTransaction。最后，您必须等到所有子工作完成后才能提交您的“原始”事务。（实际上，DependentTransaction 应该处理这个问题，但是在我向这个项目添加事务之前，我已经在使用 Thread.Join 来等待所有工作完成。）

The key thing is, only one thread can be accessing the database at any given time. I just used a semaphore to block access to the database to one thread at a time. Since my threads spend most of the time computing and only a little bit of time writing to the database, I didn't really incur a performance penalty because of this... However, if your threads are using the database frequently, this requirement may essentially remove the performance benefit of multi-threading, if you want everything contained in one transaction.

关键是，在任何给定时间只有一个线程可以访问数据库。我只是使用了一个信号量来一次阻止一个线程访问数据库。由于我的线程大部分时间都花在计算上，而只有一点点时间写入数据库，因此我并没有真正因此而招致性能损失……但是，如果您的线程经常使用数据库，则此要求可能如果您希望所有内容都包含在一个事务中，则基本上消除了多线程的性能优势。

If you have multiple threads accessing the database at once, you will get an Exception with the message "Transaction context in use by another session." If you forget to commit all of the transactions in each thread, you will get an Exception with the message "The transaction is in doubt" when you try to commit the outer-most transaction.

如果您有多个线程同时访问数据库，您将收到一个带有消息“另一个会话正在使用事务上下文”的异常。如果您忘记提交每个线程中的所有事务，当您尝试提交最外层的事务时，您将收到一个带有消息“事务有疑问”的异常。