Transaction context in use by another session

Question

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

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

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

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?

does there happen to also be a trigger on SomeLinkedSrv.Catalog.Dbo.OtherTable? — Gratzy, May 22 '12 at 15:01
Is It happening in an explicit transaction? Is DTC turned on on both servers? — Gratzy, May 22 '12 at 15:13
Have you tried implementing this as an instead of trigger? What type of transaction are you using for the original insert? Have you considered logging this locally and using a background process to update the remote server (this way the remote server does not complicate the primary transaction, and the primary transaction does not have to wait for the remote operation). — Aaron Bertrand, May 22 '12 at 15:13
@Gratzy for the moment I'm testing this on my dev machine; I'm connected to the local SQLSserver instance and the linked server points to tha tsame instance. MSDTC is running. — Cristian Lupascu, May 22 '12 at 15:16
@AaronBertrand **1.** `instead of` fails with the same error. **2.** No (explicit) transaction; just a plain `insert MyTable () values ()`. **3.** Yes, that will surely do it, but it involves some work. I was wondering if it's possible to solve this directly in TSQL. — Cristian Lupascu, May 22 '12 at 15:21
Another question about "Transaction context in use by another session": https://stackoverflow.com/questions/2858750/what-is-the-reason-of-transaction-context-in-use-by-another-session — Jon Schneider, Aug 17 '17 at 20:46

score 26 · Accepted Answer · answered May 22 '12 at 15:27

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

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/en-us/library/ms188716(SQL.105).aspx

score 1 · Answer 2 · answered Nov 20 '15 at 17:45

1

I solve It. I was using the same linked server to call the second procedure and then into the procedure I was using the same linked server.

It's very Easy, only we have to know the restricctions of linked servers.

answered Nov 20 '15 at 17:45

David Castro

1,773
21
21

score 1 · Answer 3 · answered Nov 15 '16 at 19:47

1

I have resolved it by removing linked server used in the stored procedure and then called stored procedure by the same linked server. It wasnt working in DEV environement.

answered Nov 15 '16 at 19:47

Kalpesh S

11
1

score 1 · Answer 4 · answered May 19 '20 at 12:22

One of causes of this situation is a trigger that works for linked-sever database table. An also SQL version of SQL-Server which processes database matters. To avoid this ERROR during sql query execution we should temporarily disable and after execution enable triggers for tables updated. All with database name check. Here is an example:

     Select * From People  where PersonId In (@PersonId, @PersonIdRight)
     IF 'DOUBLE' = DB_NAME()
        ALTER TABLE [dbo].[PeopleSites] DISABLE TRIGGER [PeopleSites_ENTDB_UPDATE]

     Update PeopleSites Set PersonId = @PersonIdRight Where  PersonId = @PersonId

     IF 'DOUBLE' = DB_NAME()
        ALTER TABLE [dbo].[PeopleSites] ENABLE TRIGGER [PeopleSites_ENTDB_UPDATE]


     Select * From PeopleSites where PersonId In (@PersonId, @PersonIdRight)

score 0 · Answer 5 · answered Oct 29 '14 at 11:34

0

I also got the same error in our DEV environemnt, moving the linked databases to another sql instance resolved the issue. In our production environment these databases are already on separate instances

answered Oct 29 '14 at 11:34

sumit chohan

36
2

score 0 · Answer 6 · answered Feb 12 '16 at 16:38

In my case I was using SQL 2005 and got "transaction context in use by another session" when running Insert....exec over a linked server. The fix for me was to patch from SP2 build 3161 to SP3. SP2 cumulative 5 is supposed to fix though.

https://support.microsoft.com/en-us/kb/947486

score 0 · Answer 7 · answered Sep 05 '16 at 10:07

0

When remote database sits on the same server,configure the linked server without specifying the database server ip / hostname and port. Just the database name should be sufficient.

answered Sep 05 '16 at 10:07

crathour

60
6

score 0 · Answer 8 · answered Jul 17 '19 at 15:10

I was getting the same "transaction context in use by another session error" when trying to run an UPDATE query:

 BEGIN      TRAN
--ROLLBACK  TRAN
--COMMIT    TRAN
UPDATE  did 
SET     did.IsProcessed = 0,
        did.ProcessingLockID = NULL
FROM    [proddb\production].DLP.dbo.tbl_DLPID did (NOLOCK)
WHERE   did.dlpid IN ('bunch of GUIDs')
--WHERE   did.DLPID IN (SELECT DLPID FROM @TableWithData)

However I didn't realize I was already trying to run this on the DLP database on the ProdDb\Production server. Once I removed that "[proddb\production].DLP.dbo." prefix from the query, it worked fine.

Transaction context in use by another session

8 Answers8

Linked