2

I'm trying to use a Nested Transaction on SQL Server.

Here is my test table

CREATE TABLE [dbo].[Table2] (
    [Id] INT NOT NULL
);

And my query

BEGIN TRANSACTION masterTransaction
    BEGIN TRANSACTION test
        GO
            INSERT INTO [dbo].[Table2] VALUES (200)         
            INSERT INTO [dbo].[Table2] VALUES (201)
    COMMIT TRANSACTION test

    BEGIN TRANSACTION test2
        GO
            INSERT INTO [dbo].[Table2] VALUES (202)
            INSERT INTO [dbo].[Table2] VALUES (203)
    COMMIT TRANSACTION test2


    -- Everything before that must be commited
    BEGIN TRANSACTION test3
        GO
            INSERT INTO [dbo].[Table2] VALUES (204)
            INSERT INTO [dbo].[Table2] VALUES (205)
    ROLLBACK TRANSACTION test3

COMMIT TRANSACTION masterTransaction

My goal it's to have a main transaction and micro transactions inside that. If the user ask to abort, my masterTransaction will rollback all data inside. But the user have the option to abort and discard only the last transaction.

My error: Test3 can not be rolled back. No save or transaction point was found under this name.

What am I doing wrong?

Kevin Kouketsu
  • 786
  • 6
  • 20
  • 2
    There is a good read explaining it: [A SQL Server DBA myth a day: (26/30) nested transactions are real](https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/) – Piotr Palka May 20 '19 at 20:02
  • 2
    You cannot use name of nested transaction in the ROLLBACK statement. Either use the name of outer transaction or no name at all – Vasya May 20 '19 at 20:05
  • @Vasya https://learn.microsoft.com/pt-br/sql/t-sql/language-elements/rollback-transaction-transact-sql?view=sql-server-2017 here have the optional transaction_name or savepoint_name – Kevin Kouketsu May 20 '19 at 20:10
  • 1
    But listing the transaction_name has no effect. It will roll back the outer transaction regardless of the name you pass. Only using savepoint_name is effective, and you don't have any savepoints. – David Browne - Microsoft May 20 '19 at 20:42
  • Yes, I'm seeing that now. This link https://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling from other post helped me a lot – Kevin Kouketsu May 20 '19 at 20:43
  • I think this may be an XY problem. I can't think of a time that I want to let a user roll back a transaction. Instead, I'd prompt to save changes on the application side. – S3S May 20 '19 at 20:44
  • The user have the option to create playlists of N days. But he can abort in middle of the execution. And he have the option to let the days created before the abort and cancel only the actual day (probably will be incomplete). And the option to cancel everything. @scsimon – Kevin Kouketsu May 20 '19 at 20:46
  • 1
    Still seems like application layer logic. – S3S May 20 '19 at 20:47

0 Answers0