How to manage nested transactions using try catch

Question

--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

Mistake:

Msg 3931, Level 16, State 1, Line 17 The current transaction cannot be committed and cannot be returned to the save point. Rollback the entire transaction.

How to handle this.

Answer 1

When a certain type of error occurs, you cannot roll back to the save point. See Martin Smith's answer to Rollback Transactions to save a point when ALTER TABLE ... ADD CONSTRAINT fails . The way you discover this is to check Xact_state().

, . SQL, .

• . . ( )

, 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 Database Engine

• transaction_name ROLLBACK TRANSACTION .

. SQL Server : (26/30)

, , " " Xact_state catch .

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