Nested transaction can't be rolled back

Question

I have SQL Server 2008 and want to do such a transaction:

begin transaction oo;

......

begin try        
    save transaction xx;
    alter table ....; -- this will fail
    alter table ....;
    alter table ....;
end try
begin catch  
    rollback transaction xx; -- error here
end catch;

......

commit transaction oo;

At rollback transaction xx;, I get the message

3931 The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

What am I doing wrong here?

Update To explain the scenario:

There is a big transaction "oo", which will change the table structures of the database from product version X to product version Y.
In the nested transactions, user-specific-tables should be tried to be changed (= inner transaction).
If an user-specific-table is somehow corrupted, the whole product-upgrade process should not be rolled back.
On the other hand, the user-specific-tables should not be upgraded if something else failed during the main product table upgrade (outer transaction).

Nested transactions are a myth. You can do some things that can sort of mimic a nested transaction but it is far better to just accept they don't exist and rethink your process. There are hundreds of pages explaining the myth of nested transactions. Here are a couple of them. http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/ http://sqlwithmanoj.com/2015/05/26/sql-myth-nested-transactions-in-sql-server-and-hidden-secrets/ — Sean Lange, Feb 09 '16 at 14:34

score 2 · Accepted Answer · edited May 23 '17 at 12:16

Reference

you have to use this line inside CATCH block

ROLLBACK TRANSACTION;

which will rollback all transaction, when you use this one in your above statement (posted in Q) then it will give us error

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

for it you have to put this line in TRY block

COMMIT TRANSACTION oo;

then finally your statement like that

BEGIN TRANSACTION oo;

BEGIN TRY        
    SAVE TRANSACTION xx;
    CREATE TABLE test (ID INT); -- this will fail from second time
    SELECT 3;

    COMMIT TRANSACTION oo;
END TRY

BEGIN catch  

    ROLLBACK TRANSACTION; 
END CATCH;

UPDATE after comment

BEGIN TRY        
    BEGIN TRANSACTION xx1;
    select 1; -- this will always success
    COMMIT TRANSACTION xx1;

    BEGIN TRANSACTION xx2;
    CREATE TABLE test (id int); -- this will fail from second time
    COMMIT TRANSACTION xx2;

    BEGIN TRANSACTION xx3;
    select 3; -- this will fail from second time
    COMMIT TRANSACTION xx3;


END TRY

BEGIN catch  

    ROLLBACK TRANSACTION 
END CATCH;

Thanks for your answer. Alas, this is not exactly what I am looking for. I have following scenario: There is a big transaction "oo", which will change the table structures of the database from product version X to product version Y. In the nested transactions, user-specific-tables should be tried to be changed. If an user-specific-table is somehow corrupted, the whole product-upgrade process should not be ruined. On the other hand, the user-specific-tables should not be upgraded if something else failed during the main product tables (outer transaction). — Daniel Marschall, Feb 09 '16 at 15:06

Nested transaction can't be rolled back

1 Answers1