Transaction does not rollback all changes

Question

I've ran into a procedure in SQL Server 2017 that has a transaction within a try-catch block. It isn't nested, just got an identity table filled and cycled using cursor. So try-catch is within a loop, some other procedure is called. Sometimes that procedure fails with constraint violation error and it's perfectly fine to save whatever succeeded prior to it's inner exception. And then I bumped into commit in catch clause. It made me wondering and I written this code:

DECLARE @Table TABLE (ID INT NOT NULL PRIMARY KEY)
DECLARE @Input TABLE (ID INT)

INSERT INTO @Input 
VALUES (1), (1), (2), (NULL), (3)

DECLARE @Output TABLE (ID INT)

--SET XACT_ABORT OFF

DECLARE @ID int

DECLARE [Sequence] CURSOR LOCAL FAST_FORWARD FOR
    SELECT ID FROM @Input

OPEN [Sequence]

FETCH NEXT FROM [Sequence] INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        BEGIN TRAN

        DECLARE @Msg nvarchar(max) = 'Inserting '''  + TRY_CAST(@ID as varchar(11)) + ''''
        RAISERROR (@Msg, 0, 0) WITH NOWAIT

        -- Order is important
        --INSERT INTO @Table VALUES (@ID)
        INSERT INTO @Output VALUES (@ID)
        INSERT INTO @Table VALUES (@ID)

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        SET @Msg = 'Caught ' + CAST(ERROR_NUMBER() as varchar(11)) + ' : ' + ERROR_MESSAGE()
        RAISERROR (@Msg, 1, 1) WITH NOWAIT
        IF XACT_STATE() = -1
        BEGIN
            SET @Msg = 'Uncommitable transaction [-1]'
            RAISERROR (@Msg, 1, 1) WITH NOWAIT
            ROLLBACK TRAN
        END
        IF XACT_STATE() = 1
        BEGIN
            SET @Msg = 'Commitable transaction [1]'
            RAISERROR (@Msg, 1, 1) WITH NOWAIT
            COMMIT TRAN
        END
    END CATCH
    FETCH NEXT FROM [Sequence] INTO @ID
END

SELECT * FROM @Table
SELECT * FROM @Output

So as I tried interchanging the order of @Output and @Table inserts, I got different results, no matter what XACT_ABORT is set to or whether I commit or rollback transaction in the catch block. I was always sure, that everything gets rolled back and both @Output and @Table tables will be equal....

What I am doing wrong here? I this a default transaction behavior?

You commit if all is ok. You only attempt a rollback if the transaction is uncommitable. What about if it IS commitable BUT has thrown an error? An error that triggers the CATCH doesn't always mean the transaction state will be -1. For example if you put a SELECT 1/0 prior to the COMMIT TRAN you'll trigger the catch block but the transaction will be perfectly fine. — Dave Brown

Ben Thul Ben Thul · Accepted Answer · 2019-01-03T15:36:36

This is a fun one, but your code does what I'd expect it to. Table variables do not obey transactional semantics. Temporary tables do though! So if you need the ability to roll back mutations to your temporary "thing", use a table and not a variable.

Note though that your sequence will still have values pulled from it. Even it you also put that in the transaction.

Transaction does not rollback all changes

2 Answers