Mismatched number of SQL Server Transactions in LinkedServer

ha5z0ras  于 2023-04-19  发布在  SQL Server
关注(0)|答案(1)|浏览(129)

I have a linked Server connected from ServerA to ServerB. In each server there is an SP that initiates a transaction and it is necessary for one ServerA.SP to be executed within ServerB.SP. My problem is that when doing that, I get the error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.

I have searched for information about that error but the questions/answers do not include linkedServers. That's why I'm here. The code is something like that, its really simple:

CREATE OR ALTER PROCEDURE #DB1_sp
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION 
            EXEC [LinkedServer].[db].[Schema].[SP]
                ... params ...
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
        EXEC [Logs].[SetError]
    END CATCH
END
GO

EXEC #DB1_sp

On the LinkedServer side, the SP is almost the same code. In fact all my code works if we remove the BEGIN TRANSACTION , but that is not an option due to the initial validations.

I have tried looking into DISTRIBUTED TRANSACTIONS , SAVE TRANSACTIONS and statements like SET XACT_ABORT ON all of that in the top stored procedure (because I can't access/edit the inner SP) but it doesn't work.

cbjzeqam

cbjzeqam1#

To use transaction over different instances of SQL Server or other RDBMS you have to activate MSDTC on all machines that executes SQL Server. This is knows has "two phase commit". The transaction coordinator will initiate a voting phase on all server that are concerned by the transaction and if all are OK, the a SQL COMMIT order is sent to all the machines. The second phase consist to apply the effective COMMIT on all machines...

BUT if one machine has crashed between phase 1 (vote) and phase 2 (apply) the the transtation is committed partially on the alive machines and not on this that is down.

The results will be a find mess and I wish you good luck in sorting out the valid data and the inconsistencies and restoring the situation

相关问题