MERGE vs SELECT/INSERT: Choosing an Idempotent Update Strategy Under Azure SQL Retry Policies

Question

When a transient error (e.g., 4060, 40197) interrupts a write, Azure SQL’s retry policy can re‑issue the command. The goal is to guarantee that a retry does not produce duplicate rows.
Azure’s retry policy applies to individual SQL statements but not automatically to multi‑statement stored procedures, which can lead to partial re‑execution and unintended duplicates if the logic is not idempotent.
Two documented patterns exist: a single MERGE statement that updates or inserts based on a unique key, and a SELECT‑then‑INSERT sequence guarded by a transaction and optimistic concurrency. Each has trade‑offs around concurrency, trigger behavior, and transaction size.
Will MERGE reliably avoid duplicates when retried after a transient error in the presence of triggers or cascading foreign keys? Does the SELECT/INSERT pattern provide stronger idempotency guarantees when the entire stored procedure is retried? Under Azure SQL’s retry policy, which pattern better handles partial re‑execution of multi‑statement procedures without duplicating writes?

Muntasir · Answer

Answer
MERGE will not create duplicate rows when retried, but it will re‑execute any AFTER triggers or cascading foreign‑key actions, which can duplicate side‑effects such as audit rows or child updates. The SELECT‑then‑INSERT pattern does not give stronger row‑level idempotency by itself; without additional locking or a unique‑violation check it can still insert twice under retries or concurrent calls.
Confirmed facts

A keyed MERGE against a unique/primary key matches the existing row on retry and performs an UPDATE, so the target table ends with a single row.
If the original transaction committed but the client lost the acknowledgment, retrying MERGE fires the same triggers and cascades again, potentially duplicating their side effects.
SELECT‑then‑INSERT inside a transaction can still race: two executions may both see no row and both insert unless you add UPDLOCK/HOLDLOCK, a unique constraint, or handle error 2601/2627 as success.
Azure SQL’s retry policy is client‑side; a failed batch or stored procedure may be re‑submitted in full, so partial re‑execution of a multi‑statement procedure can repeat work unless the procedure itself is made idempotent.

Recommended steps for this scenario

Define a business or idempotency key (e.g., a GUID passed from the caller) and add a unique constraint or index on that key in a helper table or the target table.
Wrap the upsert in an explicit transaction with SET XACT_ABORT ON;.
If triggers and cascading FKs are idempotent (or can be made so), a single MERGE statement is sufficient; treat any unique‑violation error (2601/2627) as success when the existing row matches the supplied values.
If triggers or cascades are not idempotent, add procedure‑level idempotency: before the upsert, check an idempotency‑key table (or a processed flag) and skip work when the key already exists, or use sp_getapplock to serialize execution for the same key.
Handle transient errors (4060, 40197, 1205) as retryable; treat deadlock (1205) and unique‑violation (2601/2627) as retryable only after confirming the row state.

Missing diagnostic detail
Are the AFTER triggers or cascading foreign‑key actions on the target table idempotent, or can they be modified to be so? If they are not, the procedure‑level idempotency step above is required; otherwise a plain MERGE suffices.

MERGE vs SELECT/INSERT: Choosing an Idempotent Update Strategy Under Azure SQL Retry Policies

1 answer

Answer

Confirmed facts

Recommended steps for this scenario

Missing diagnostic detail

Your perspective belongs here.

0 question comments