如何使用try catch管理嵌套事务 [英] how to manage nested transaction with try catch

问题描述

--Drop Table Tab1

Begin Transaction TR1; 
Save Transaction TR1;
    Create Table Tab1(f1 decimal(10,0));
    Begin Transaction TR2
    Save Transaction TR2
        insert into Tab1 values(1);
        Begin Transaction TR3;
        Save Transaction TR3;
            insert into Tab1 values(2);
            Begin Try 
                insert into Tab1 values('OK');
                Commit Transaction TR3;
            END TRY
            BEGIN Catch
                print 'catch'
                RollBack Transaction TR3;
            End Catch

        insert into Tab1 values(3);
        Commit Transaction TR2
    insert into Tab1 values(4);
Commit Transaction TR1;
--Commit Transaction;
select * from Tab1;
Drop Table Tab1

Select @@TRANCount

错误发生:

信息3931，第16级，状态1，第17行 当前事务无法提交，也无法回滚到保存点.回滚整个交易.

Msg 3931, Level 16, State 1, Line 17 The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

如何处理.

推荐答案

出现某些类型的错误时，您将无法回滚到保存点.请参阅马丁史密斯的对

When certain type of errors get raised you cannot rollback to a save point. See Martin Smith's answer to Rollback transaction to savepoint on failing ALTER TABLE … ADD CONSTRAINT. The way you detect this is to test Xact_state().

但是，您的问题有所不同，因为您还尝试使用嵌套事务.嵌套事务实际上并不像我们期望的那样在SQL中有效.

However your problem is somewhat different because you're also trying to use nested transactions. Nested transactions don't really work in SQL as we would expect them to.

• 您只能命名最外面的交易. 请参阅交易记录(数据库引擎)

例如，此操作失败，并显示Cannot roll back TR2. No transaction or savepoint of that name was found.

For example this fails with Cannot roll back TR2. No transaction or savepoint of that name was found.

    BEGIN TRANSACTION TR1; 
    BEGIN TRANSACTION TR2
    ROLLBACK TRANSACTION TR2
    COMMIT Transaction TR1

来自嵌套交易

• SQL Server数据库引擎忽略提交内部事务

• Committing inner transactions is ignored by the SQL Server Database Engine

ROLLBACK TRANSACTION语句的transaction_name参数引用一组命名嵌套事务的内部事务是不合法的. transaction_name只能引用最外层交易的交易名称

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction

Paul S. Randal在

Paul S. Randal explores this further in A SQL Server DBA myth a day: (26/30) nested transactions are real

最好的办法是使用保存点"，然后在捕获中以及最后检查Xact_state.

The best you can do is use Save points instead and check the Xact_state in your catch and at the end.

BEGIN TRANSACTION tr1; 

SAVE TRANSACTION tr2; 

CREATE TABLE tab1 
  ( 
     f1 DECIMAL(10, 0) 
  ); 

SAVE TRANSACTION tr3 

INSERT INTO tab1 
VALUES     (1); 

SAVE TRANSACTION tr4; 

INSERT INTO tab1 
VALUES     (2); 

BEGIN try 
    -- change the order of the follwoing two lines around to see the difference
    INSERT INTO tab1 VALUES (1 / 0); --Results in a rollback to savepoint
    INSERT INTO tab1 VALUES ('OK');  --Results in a complete rollback

    COMMIT TRANSACTION tr4; 
END try 

BEGIN catch 
    IF Xact_state() = -1 
      BEGIN 
          PRINT 'rollback transaction no other work can be done' 

          ROLLBACK TRANSACTION; 
      END 
    ELSE 
      BEGIN 
          PRINT 'rollback to savepoint' 

          ROLLBACK TRANSACTION tr4 
      END 
END catch 

IF Xact_state() > 0 
  BEGIN 
      INSERT INTO tab1 
      VALUES     (3); 

      INSERT INTO tab1 
      VALUES     (4); 

      COMMIT TRANSACTION tr1; 

      SELECT * 
      FROM   tab1; 

      DROP TABLE tab1 
  END 

这篇关于如何使用try catch管理嵌套事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！