MERGE in a nested TRY/CATCH won't roll back

  • I have a section of code similar to the following abstract:

    DECLARE
    @loop [int] = 0,
    @savepoint [varchar](4)

    BEGIN TRY
    BEGIN TRANSACTION _OUTER_

    WHILE (@loop < 10)
    BEGIN
    SELECT
    @loop = @loop + 1,
    @savepoint = '_' + CAST(@loop AS [varchar](2))

    BEGIN TRY
    BEGIN TRANSACTION
    SAVE TRANSACTION @savepoint

    MERGE ... -- This transaction works

    MERGE ... -- This transaction fails with "The MERGE statement attempted to UPDATE or DELETE the same row more than once."

    COMMIT TRANSACTION @savepoint
    END TRY
    BEGIN CATCH
    -- INNER CATCH CODE
    IF @@TRANCOUNT <> 0
    IF XACT_STATE() <> -1
    ROLLBACK TRANSACTION @savepoint
    ELSE
    THROW;
    END CATCH
    END --loop

    COMMIT TRANSACTION _OUTER_
    END TRY
    BEGIN CATCH
    -- OUTER CATCH CODE
    IF @@TRANCOUNT <> 0
    IF XACT_STATE() <> -1
    ROLLBACK TRANSACTION _OUTER_
    END CATCH

    When this runs, the "INNER CATCH CODE" throws the error to the "OUTER CATCH CODE" rather than simply rolling back the two MERGE updates. Without the XACT_STATE() check, the code fails with "The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction."

    My question is, why can't the MERGE be rolled back so that other work performed in the procedure can be retained? I need the outer transaction and try/catch due to other functionality of the code (single instance running) but the main data updates are occurring inside the loop and it's those I'd like to roll back.

    Thanks.

  • Unlike Oracle, you can't really have nested transactions.

    https://www.brentozar.com/archive/2023/02/can-you-nest-transactions-in-sql-server/

    Your best bet is to break this into separate transactions with support for continuing or stopping, and then potentially some logic to decide if you continue with another transaction, flag something for users to check, etc.

    Merge isn't a great performing construct in any case, so think about using insert/update/deletes separately, with some sort of transaction control that makes sense.

  • Unlike Oracle, you can't really have nested transactions.

    https://www.brentozar.com/archive/2023/02/can-you-nest-transactions-in-sql-server/

    Your best bet is to break this into separate transactions with support for continuing or stopping, and then potentially some logic to decide if you continue with another transaction, flag something for users to check, etc.

    Merge isn't a great performing construct in any case, so think about using insert/update/deletes separately, with some sort of transaction control that makes sense.

  • The reason a savepoint doesn't permit a partial rollback is because the error you’re hitting dooms the transaction.  Once a transaction is doomed the db engine cannot guarantee its internal consistency — therefore the only permitted operation is a full rollback.  Savepoints, XACT_STATE() checks, nested TRY/CATCH — none of it can recover a doomed transaction and turn it into a partial rollback.  This isn’t specific to MERGE; any dooming error produces the same behavior

    For the full mechanics of how errors, transactions, and TRY/CATCH interact, Erland Sommarskog’s series is the definitive reference:

    https://www.sommarskog.se/error_handling/Part1.html

    Erland is a member of this site and maybe he will hopefully chime in

    For what you’re trying to achieve — per-iteration atomicity inside a loop — to share a code pattern would require an opinionated explanation imo.  It can be done in various ways and I'm not suggesting one way is the best way.  Only that it seems to work in similar situations afaik and iirc.  With the warning that this might be preachy you could possibly:

    1) Move the loop body into its own stored procedure. That proc owns its own transaction with SET XACT_ABORT ON at entry, SET XACT_ABORT OFF at exit.

    TRY block: BEGIN TRANSACTION, do the work, COMMIT.

    CATCH block: capture error metadata into OUTPUT parameters, ROLLBACK, done. No XACT_STATE() inspection, no conditional logic. Either the work committed or it didn’t.

    2) Make the outer proc loop, calls the inner proc, read the OUTPUT to log success/failure, and/or continue

    In general keep your logging and single-instance guard outside of the inner transaction entirely.  The inner proc doesn’t need to know about them imo.  This approach eliminates the savepoints, the nested TRY/CATCH, and the XACT_STATE() branching. Each iteration either commits fully or rolls back fully.

    SET XACT_ABORT ON collapses all error severities to a single behavior: if the transaction is doomed, you ROLLBACK in the CATCH, done. No XACT_STATE() inspection needed because the outcome is deterministic

    Separately — the MERGE duplicate-row error means your source is matching multiple rows to the same target row.  Deduplicate the source.  Or better yet switch to explicit INSERT/UPDATE/DELETE statements and avoid that specific error regardless of transaction structure

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Great advice from Steve, and more detailed than my response.

    The one thing I have done if the updates aren't too large is save off the data from each loop, so that if I really need all these transactions going forward to backwards, I have a path. You can also use something like CDC/CT/audit to recreate the data in a pinch.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply