1

Why, when my SAVE TRAN SavePoint is at location (A) do I get these errors on the third instance of falling into the CATCH and beyond...

"Msg 6401, Level 16, State 1 ... Cannot roll back SavePoint. No transaction or savepoint of that name was found."

...but I do not get such errors when SAVE TRAN SavePoint; is at location (B)?

I can only imagine it's something to do with the context of the cursor loop, but I don't understand why.

-- Premable stuff, opening cursor, etc. here...

BEGIN TRAN

SAVE TRAN SavePoint;  -- (A)

WHILE @@FETCH_STATUS = 0
BEGIN;

  -- SAVE TRAN SavePoint;  -- (B)

  BEGIN TRY

    -- UPDATE statement here which may fail

    SAVE TRAN SavePoint;

  END TRY
  BEGIN CATCH

    ROLLBACK TRAN SavePoint;

  END CATCH;

  FETCH NEXT FROM Cursor INTO @X, @Y, @Z;

END;

-- CLOSE & DEALLOCATE stuff here

IF @DryRun = 1
  ROLLBACK TRAN;
ELSE
  COMMIT TRAN;
1
  • Well you need to save the transaction anew every time you roll back to the savepoint. What errors are you getting that you need such complex rollback logic anyway? Why could the UPDATE fail? Nov 20, 2023 at 17:14

1 Answer 1

1

When you roll back to a save point, that save point is used up.

In position A, you only set it once, so it can only be used once.

You are allowed to set a save point with the same name more than once.

When you do that in position B, you ensure it is available on every iteration of the loop.

The behaviour is a little weird, and save points aren't widely used for many reasons.

This prints your error:

BEGIN TRANSACTION;
    SAVE TRANSACTION SP1; -- only set once
    ROLLBACK TRANSACTION SP1;
    PRINT 'Success!'

    ROLLBACK TRANSACTION SP1;
    PRINT 'Less success'
COMMIT TRANSACTION;

This does not cause an error:

BEGIN TRANSACTION;
    SAVE TRANSACTION SP1;
    ROLLBACK TRANSACTION SP1;
    PRINT 'Success!'

    SAVE TRANSACTION SP1;
    ROLLBACK TRANSACTION SP1;
    PRINT 'More success!'
COMMIT TRANSACTION;

Somewhat curiously, neither does this:

BEGIN TRANSACTION;
    SAVE TRANSACTION SP1;
    SAVE TRANSACTION SP1;
    ROLLBACK TRANSACTION SP1;
    PRINT 'Success!'

    ROLLBACK TRANSACTION SP1;
    PRINT 'More success!'
COMMIT TRANSACTION;
2
  • Thank you. The important bit I didn't realise, and, in my defence, I don't think the docs make entirely clear, is rolling back to a save point "uses it up". Nov 21, 2023 at 14:18
  • @Chris They do rather less than make it "entirely clear" 😀
    – Paul White
    Nov 21, 2023 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.