另一个会话正在使用的事务上下文 [英] Transaction context in use by another session

问题描述

我有一个名为MyTable的表，在其上定义了一个触发器，如下所示:

I have a table called MyTable on which I have defined a trigger, like so:

CREATE TRIGGER dbo.trg_Ins_MyTable
   ON  dbo.MyTable 
   FOR INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    insert SomeLinkedSrv.Catalog.dbo.OtherTable 
        (MyTableId, IsProcessing, ModifiedOn)
    values (-1, 0, GETUTCDATE())
END
GO

每当我尝试在MyTable中插入一行时，都会出现以下错误消息:

Whenever I try to insert a row in MyTable, I get this error message:

消息3910，第16级，状态2，第1行 另一个会话正在使用的事务上下文.

Msg 3910, Level 16, State 2, Line 1 Transaction context in use by another session.

我已经将SomeLinkedSrv正确定义为链接服务器(例如，select * from SomeLinkedSrv.Catalog.dbo.OtherTable正常工作).

I have SomeLinkedSrv properly defined as a linked server (for example, select * from SomeLinkedSrv.Catalog.dbo.OtherTable works just fine).

如何避免错误并成功插入记录并执行触发器?

How can I avoid the error and successfully insert record+execute the trigger?

推荐答案

如果启用了MARS，则不能在分布式事务中使用环回链接服务器.

Loopback linked servers can't be used in a distributed transaction if MARS is enabled.

环回链接服务器不能在分布式事务中使用. 尝试从中对回送链接服务器进行分布式查询 分布式事务中的错误导致错误，例如错误3910: "[Microsoft] [ODBC SQL Server驱动程序] [SQL Server]中的事务上下文 由另一个会话使用." INSERT ... EXECUTE语句，由不具有连接的连接发出 启用了多个活动结果集(MARS)，针对 环回链接服务器.请注意，该限制在以下情况下仍然适用 在连接上启用了MARS.

Loopback linked servers cannot be used in a distributed transaction. Trying a distributed query against a loopback linked server from within a distributed transaction causes an error, such as error 3910: "[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session." This restriction does not apply when an INSERT...EXECUTE statement, issued by a connection that does not have multiple active result sets (MARS) enabled, executes against a loopback linked server. Note that the restriction still applies when MARS is enabled on a connection.

http://msdn.microsoft.com/zh-CN/library/ms188716(SQL.105).aspx

这篇关于另一个会话正在使用的事务上下文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！