t-sql-upserts-merge

# Write an upsert that survives concurrency, and refuse the dangerous MERGE An upsert looks like a one-statement problem and is a concurrency problem. Both of the shapes a model reaches for first, `IF EXISTS ... UPDATE ELSE INSERT` and a bare `MERGE`, are races. They pass every single-session test and fail the first time two callers arrive together. Measured on 2026-08-28 against a live engine reporting `EngineEdition` 5 and Edition `SQL Azure`. Open [the measured runs](references/merge-hazards-and-concurrency.md) when a number below is disputed, or before changing one. ## What the race actually costs Eight sessions, twenty increments each, one hot key. A correct upsert ends at 160. | Pattern | Result | Cost | |---|---|---| | `UPDATE` then `INSERT` when `@@ROWCOUNT = 0`, no hint | one session died on `Msg 2627`, final value **140** | 20 writes lost; the caller saw one error, not twenty | | `MERGE` with no lock hint, 4 sessions | 2 of 4 raised `Msg 2627`, final value **2** of 4 | half the batch rolled back | | `IF EXISTS` on a table with **no unique key**, 2 sessions | **2 rows for one key, no error at all** | silent duplicates, nothing in the log to find later | | `MERGE WITH (HOLDLOCK)` on a unique key | 160, no failures, no deadlocks | correct | | `UPDATE WITH (SERIALIZABLE)` then `INSERT` | 160, no failures | correct | | `UPDATE`, then `INSERT` inside `TRY` catching 2627 | 160, no failures | correct | The third row is the one to fear. The others raise an error someone can see. That one returns success to both callers and leaves the table wrong. ## Why the naive shape is a race ```sql -- WRONG. Two sessions can both take the ELSE branch. IF EXISTS (SELECT 1 FROM dbo.counters WHERE k = @k) UPDATE dbo.counters SET n = n + 1 WHERE k = @k; ELSE INSERT INTO dbo.counters (k, n) VALUES (@k, 1); ``` The `SELECT` takes a shared lock and releases it immediately, under every isolation level below serializable, so there is a window between the test and the insert. `BEGIN TRAN` does not close it: a transaction makes the pair atomic on failure without making the read block anyone. Verified with a two second gap in the window, both sessions reported `INSERT branch`. ## Three upserts that hold Each reached 160 of 160 under eight concurrent sessions. **1. MERGE, when the statement genuinely needs more than one action.** ```sql BEGIN TRAN; MERGE dbo.counters WITH (HOLDLOCK) AS t USING (SELECT @k AS k) AS s ON t.k = s.k WHEN MATCHED THEN UPDATE SET t.n = t.n + 1 WHEN NOT MATCHED THEN INSERT (k, n) VALUES (s.k, 1); COMMIT; ``` The hint goes on the target, before the alias. Two things make it safe and both are required. `HOLDLOCK` is documented as a synonym for `SERIALIZABLE`, and it is what holds a range lock across the match and the insert. **The unique index on the join key is what makes that range lock narrow.** Without it the lock covers far more of the table: four concurrent sessions against a table with no unique key produced `Msg 1205` deadlocks and rolled two of them back. **2. UPDATE first, insert only if nothing was updated.** Fewer moving parts, and the better choice when the row usually already exists. ```sql BEGIN TRAN; UPDATE dbo.counters WITH (SERIALIZABLE) SET n = n + 1 WHERE k = @k; IF @@ROWCOUNT = 0 INSERT INTO dbo.counters (k, n) VALUES (@k, 1); COMMIT; ``` `SERIALIZABLE` on the `UPDATE` is not decoration. Without it this is the row that ended at 140. **3. Catch the duplicate key and treat it as the update signal.** Best when the row usually does not exist, because the common path takes no extra lock. ```sql UPDATE dbo.counters SET n = n + 1 WHERE k = @k; IF @@ROWCOUNT = 0 BEGIN BEGIN TRY INSERT INTO dbo.counters (k, n) VALUES (@k, 1); END TRY BEGIN CATCH IF ERROR_NUMBER() IN (2601, 2627) UPDATE dbo.counters SET n = n + 1 WHERE k = @k; ELSE THROW; END CATCH END ``` All three still depend on a unique constraint or unique index on the key being upserted. **Without one, none of them is safe**, because there is nothing for the range lock to hold and nothing to raise 2627 against. Sizing that key is `design-azure-sql-schema`. ## When to refuse MERGE outright Say no and write separate statements. Each is verified or documented, not folklore. - **A partial batch with `WHEN NOT MATCHED BY SOURCE THEN DELETE`.** That clause belongs to a full reload, never to an upsert of whichever rows this request happens to carry. Verified: a target holding `A`, `B`, `C` and a source naming only `A` ended with one row and no error. - **A source that can contain the same key twice.** `Msg 8672`, "The MERGE statement attempted to UPDATE or DELETE the same row more than once". Deduplicate the source first, with `GROUP BY` or a window function, rather than loosening the `ON` clause. - **Any filter in the `ON` clause.** Documented: put only the columns being matched there. Verified: adding `AND t.qty > 100` to `ON` made an existing row look unmatched, so the `NOT MATCHED` branch fired and hit `Msg 2627` on the key that already existed. Row filters go in the `WHEN` clause or in a view or common table expression over the source. - **A columnstore target.** Documented: avoid any form of columnstore index as a `MERGE` target, and instead stage, then delete and insert in batches. - **High concurrency at scale.** Documented: at scale `MERGE` may introduce complicated concurrency issues, and separate statements can perform better with less blocking. - **Literal values in a frequently executed statement.** Simple parameterization is not applied to `MERGE`, so literals compile a new plan every execution. Pass parameters through `sys.sp_executesql`. Building the statement by concatenation instead is `prevent-sql-injection`. `MERGE` also requires a semicolon terminator, and omitting it is `Msg 10713` by number rather than a generic syntax error. ## The indexed view question, stated honestly A long standing warning says a `MERGE` can leave an indexed view inconsistent with its base tables, and a routine consistency check will not report it. Half of that is current. **The half that holds.** An integrity check performs only **physical** consistency checks on an indexed view. The logical checks, which compare the materialized rows against the base tables, run only under `EXTENDED_LOGICAL_CHECKS`. So a view that disagrees with its base tables is invisible to a routine check, whatever put it there. Documented for Azure SQL Database today, and why this defect class was ever severe. **The half that did not reproduce.** The published minimal reproduction and four further shapes, including an aggregated view over a two table join, stayed consistent at compatibility levels 120 through 170. So do not tell a developer their indexed views are being corrupted today. Say instead that this defect raises no error and fails no check, so an indexed view over a `MERGE` target has to be verified deliberately. The repair, if the two ever disagree, is to drop and recreate the view's clustered index. ## Check it worked One session proves nothing, so run the upsert from two shells at once and compare the final value against the arithmetic total. `sqlcmd -b` exits non-zero on an error, and `-m-1` prints every message, including the severity 10 ones that otherwise arrive with no number at all. **`-m-1` is an ODBC `sqlcmd` instruction**, meaning the 18.x build from `mssql-tools18` or the Microsoft command line utilities, and `localhost,1433` is exactly where a reader is most likely to be holding the other one. Measured 2026-09-05, go-sqlcmd 1.10.0, the 1.x build `brew install sqlcmd` and `winget install sqlcmd` install, prints no `Msg` header on a severity 10 message at any `-m` value. `Msg 2627` below is severity 14 and arrives with its number on either build, so this check still works; what go-sqlcmd will not show you is a severity 10 message from the statements around it. The ODBC build is inside the container image at `/opt/mssql-tools18/bin/sqlcmd`, one `docker exec` away. ```bash UPSERT="BEGIN TRAN; UPDATE dbo.counters WITH (SERIALIZABLE) SET n = n + 1 WHERE k = N'hot'; IF @@ROWCOUNT = 0 INSERT INTO dbo.counters (k, n) VALUES (N'hot', 1); COMMIT;" S=(-S localhost,1433 -d appdb -C -b -m-1) sqlcmd "${S[@]}" -Q "CREATE TABLE dbo.counters (k NVARCHAR(50) NOT NULL PRIMARY KEY, n INT NOT NULL);" for i in 1 2; do sqlcmd "${S[@]}" -Q "DECLARE @i INT = 0; WHILE @i < 50 BEGIN $UPSERT SET @i += 1; END" & done wait sqlcmd "${S[@]}" -Q "SELECT n, COUNT(*) OVER () AS rows_for_key FROM dbo.counters WHERE k = N'hot';" ``` Expected: `n` is 100 and `rows_for_key` is 1. Below 100 means writes were lost, more than one row means the key carries no unique index. Delete `WITH (SERIALIZABLE)` and rerun: the count falls short, or one shell exits non-zero on `Msg 2627`. Against Azure SQL Database only `-S` and the authentication flags change. Check an indexed view over the target separately, because nothing else will tell you. ```sql -- Cheapest first: read the materialized rows, then recompute from the base table. SELECT region, total, n FROM dbo.v_totals WITH (NOEXPAND) ORDER BY region; SELECT region, SUM(amt), COUNT_BIG(*) FROM dbo.sales GROUP BY region ORDER BY region; -- Or ask for the logical checks by name. Without this option they do not run. DBCC CHECKTABLE ('dbo.v_totals') WITH EXTENDED_LOGICAL_CHECKS; ``` The two result sets must match row for row. `DBCC` must report 0 allocation and 0 consistency errors. ## Do not - Do not present `IF EXISTS ... UPDATE ELSE INSERT` as an upsert. It is a race, and wrapping it in a transaction does not fix it. - Do not add `HOLDLOCK` and stop there. Without a unique index on the join key the range lock is wide, and four sessions produced `Msg 1205`. - Do not translate `ON CONFLICT DO UPDATE` into `MERGE` reflexively. Two of the three safe patterns are plain statements and both are easier to review. - Do not use `READPAST` on a `MERGE` target with an insert branch. Documented: it can produce inserts that violate unique constraints. - Do not claim an indexed view is being corrupted by `MERGE` today without reproducing it, and do not treat a clean integrity check as evidence that one agrees with its base tables. - Do not design the retry loop here. A deadlock victim needs a retry rather than a louder message, and which errors deserve one is separate work. ## References - Open [the measured runs](references/merge-hazards-and-concurrency.md) when a number here is challenged, or when you need the message text for 2627, 8672, 10713 or 1205 as this engine printed it. Every concurrency run, every loud `MERGE` shape, all five indexed view attempts.