SQL Server appears ' other session is using transaction context ' for the cause of the problem, what is a loopback linked server? (reproduced)

I go through the Baidu search and test myself to get the problem reason:

On MSDN I read that SQL Server does not support the presence of a local linked server in distributed transactions (loopback linked server) by simply stating that you might not fully understand the meaning of the loopback link server . Just a few pieces of code down there, and you'll understand.

1. Open the native SQL Server Management Studio tool to connect to a database server

The native database server is used here.

2. The address of a linked server connection on the native database server is the native server with the name Ls_test.

3. Assume that the local database server has

Database A

Database b

Dbo.test table in database B

4. Create the following stored procedure in database a

CREATE PROCEDURE [dbo]. [prps_testa]  as SET  on BEGIN TRAN    EXEC Ls_test. B.dbo.prtestb    COMMITTRAN

5. Create the following stored procedure under database B

CREATE PROCEDURE [dbo]. [prtestb] @OV_ReturnMss VARCHAR (+) OUTPUT  as BEGIN            SELECT *  from Ls_test. B.dbo.testEND

6. Execute the stored procedure under database a Dbo.prtesta

EXEC dbo. Prtesta

The following error will appear:

The context of the transaction is being used by another session.

I believe you already understand what is called loopback link Server , when we change the dbo.prtestb of the stored procedure in database B to cross-library access, such as:

SELECT *  from B.dbo.test

The problem is solved.

Therefore, you must pay attention to avoid this problem in the future use of business, I hope to help you!

Original link

SQL Server appears ' other session is using transaction context ' for the cause of the problem, what is a loopback linked server? (reproduced)