The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

如果你的存盤程序或其他腳本出現下面這個錯誤，一般是因為ROLLBACK TRANSACTION在邏輯上缺少匹配的BEGIN TRANSACTION或者沒有開始一個事務（也有可能此事務已經提交），但是你做了事務回滾操作（ROLLBACK TRANSACTION），否則就可能出現這種錯誤，

Msg 3903, Level 16, State 1, Line 22

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

出現這種錯誤有很多種可能性，下面我們來通過一些案例來簡單介紹一下這個錯誤，這些案例都是一些特殊案例的簡化版本，

案例1：

CREATE PROCEDURE PRC_EXC

AS

BEGIN

    SELECT 1/0  --僅僅模擬存盤程序出現例外，

END;

GO 

CREATE PROCEDURE PRC_TEST

AS

BEGIN

    BEGIN TRY

        BEGIN TRAN TT

            UPDATE dbo.TEST SET NAME='k3' WHERE object_id =9;

        COMMIT TRAN TT;

        EXEC dbo.PRC_EXC

    END TRY

    BEGIN CATCH

        ROLLBACK TRAN TT;

    END CATCH

END

如果你執行存盤程序PRC_TEST，如下所示，因為執行存盤程序dbo.PRC_EXC時遇到例外被捕獲，此時在BEGIN CATCH部分執行ROLLBACK TRAN TT，但是實際上，此事務已經提交，資料庫根本沒有這樣一個事務，然后你又要回滾事務，所以出錯，可能讓人好奇的是為什么存盤程序dbo.PRC_EXC不放在事務里面，這里僅僅是簡單模擬生產環境的一個案例，正確的做法應該將dbo.PRC_EXC放入事務當中，或者將dbo.PRC_EXC放入另外一個BEGIN TRY ... END TRY里面去，

如果要在捕獲一個事務里面出現例外的正確的做法如下所示，個人更傾向于第二種寫法，

BEGIN TRANSACTION;

BEGIN TRY

   ...................

   ...................

   --執行所有業務邏輯后，最后提交

   COMMIT;

END TRY

BEGIN CATCH

   --if an exception occurs execute your rollback, also test that you have had some successful transactions

   IF @@TRANCOUNT > 0 ROLLBACK;  

END CATCH

BEGIN TRY

   BEGIN TRANSACTION;

   ....................

   ....................

   --執行所有業務邏輯后，最后提交

   COMMIT;

END TRY

BEGIN CATCH

   --if an exception occurs execute your rollback, also test that you have had some successful transactions

   IF @@TRANCOUNT > 0 ROLLBACK;  

END CATCH

案例2：

CREATE PROCEDURE PRC_TEST2

AS 

SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRANSACTION

    UPDATE dbo.TEST SET NAME='k3' WHERE object_id =9;    --這里用簡單的UPDATE替換復雜的業務邏輯，

    IF @@Error != 0 GOTO ERROR_HANDLER

     UPDATE dbo.TEST SET NAME='k3' WHERE object_id =15; --這里用簡單的UPDATE替換復雜的業務邏輯，

    IF @@Error != 0 GOTO ERROR_HANDLER

COMMIT TRANSACTION

ERROR_HANDLER: ROLLBACK TRANSACTION

SET NOCOUNT OFF

RETURN 0

GO

上面錯誤的原因，在于沒有例外或錯誤時，事務提交后，這一句“ERROR_HANDLER: ROLLBACK TRANSACTION”總是會被執行，邏輯上已經沒有事務了，所以正確的做法，事務提交后，直接RETURN，避免正常情況下執行ERROR_HANDLER: ROLLBACK TRANSACTION，或者將回滾邏輯放到IF條件之后，不要用GOTO這種寫法.

正確的SQL：

ALTER PROCEDURE PRC_TEST2

AS 

SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRANSACTION

    UPDATE dbo.TEST SET NAME='k3' WHERE object_id =9;    --這里用簡單的UPDATE替換復雜的業務邏輯，

    IF @@Error != 0 GOTO ERROR_HANDLER

     UPDATE dbo.TEST SET NAME='k3' WHERE object_id =15;   --這里用簡單的UPDATE替換復雜的業務邏輯，

IF @@Error != 0 GOTO ERROR_HANDLER

COMMIT TRANSACTION

SET NOCOUNT OFF

RETURN 0;

ERROR_HANDLER: ROLLBACK TRANSACTION

SET NOCOUNT OFF

RETURN 0

GO

這里來一個簡單的演示，你可以體會一下，所謂的BEGIN TRAN與ROLLBACK  TRANSACTION并不是指數量匹對，而是邏輯上事務回滾前，必須有一個未提交的事務，

SELECT * INTO test FROM sys.objects

SELECT  @@TRANCOUNT;--值為0

BEGIN TRAN

UPDATE  TEST SET     name = 'kkk' WHERE   object_id =7;

SELECT  @@TRANCOUNT;--值為1,

COMMIT TRAN

ROLLBACK TRAN;  --事務其實已經結束，突然來一個回滾事務，沒有匹配的BEGIN TRAN，所以出現報錯"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

案例3：

下面這種錯誤，純屬菜鳥級別犯的錯誤或粗心大意所致，

CREATE PROCEDURE PRC_TEST4

AS 

SET NOCOUNT ON

BEGIN

BEGIN TRY

    UPDATE dbo.TEST SET NAME='k3' WHERE object_id =9;    --這里用簡單的UPDATE替換復雜的業務邏輯，

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

    ROLLBACK TRANSACTION

END CATCH

END;

總結：

實際案例中，如果存盤程序里面有復雜的業務邏輯，尤其出現嵌套呼叫存盤程序的時候，特別是多層嵌套時，這種問題排查起來也相當麻煩，所以盡量少用嵌套呼叫存盤程序，簡化業務邏輯！另外，出現這種錯誤時，需要仔細檢查代碼邏輯才能找出這些出錯的地方，似乎也沒有其它更好的方法，

標籤：SQL Server

上一篇：SQL Server 2014下@@SERVERNAME回傳NULL值淺析

下一篇：sql存盤程序簡單實體陳述句