October 29, 2026 at 9:43 am
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.
October 29, 2026 at 8:56 pm
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.
October 29, 2026 at 9:14 pm
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.
October 30, 2026 at 2:23 pm
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
October 30, 2026 at 3:02 pm
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