Part 3 — Changing data › Chapter 20

UPSERT, MERGE and idempotent writes

ON CONFLICT, ON DUPLICATE KEY, MERGE — and why “check then insert” is a race, not a pattern.

From the advert

“Esperienza nello sviluppo ETL con SSIS” — the actual daily problem of an ETL job is not transforming data. It is making the load safe to re-run after it failed halfway through at 03:40.

“Check then insert” is a race, not a pattern

Why check-then-insert is a race Two sessions both check whether a row exists, both correctly find that it does not, and both then insert it. The gap between the check and the insert is the window. No isolation level below serialisable closes it, because the row that would have to be locked does not exist yet. SESSION A SESSION B TIME ↓ SELECT … WHERE ref = 'X' not found SELECT … WHERE ref = 'X' also not found INSERT … 'X' succeeds INSERT … 'X' duplicate row THE WINDOW nothing to lock yet
The fix is not a higher isolation level. It is a UNIQUE constraint, which turns the second insert from a duplicate row into an error you can catch.
🧒 Simple

Two people look in the fridge, both see there is no milk, and both go and buy milk. Looking and acting were two separate moments, and something changed in between. The fix is not to look harder — it is to make “buy the milk if there isn't any” a single indivisible action.

The race everybody writes at least once: check, then act, with a gap in between.
-- The bug, written by everyone at least once:
SELECT id FROM customers WHERE email = ?;              -- statement 1: "not found"
INSERT INTO customers (email, name) VALUES (?, ?);     -- statement 2: so insert it
-- Another session ran BOTH of those between your two statements.
-- Result: a duplicate row, or a unique-violation exception — in production only.
Why testing never finds this
  • The two statements are separate, so there is a real interval between them. On a laptop with one user the interval is never contended; with fifty concurrent requests it is contended constantly.
  • Wrapping both in a transaction does not fix it at READ COMMITTED: the second session's insert is invisible to your SELECT until it commits, and then it exists.
  • The fix is not to look harder — it is to make “insert if absent” a single indivisible statement, backed by a unique constraint. The constraint does the work; the syntax is just how you ask.
🎓 Professional

The gap between the check and the act is a time-of-check to time-of-use race, and no isolation level below SERIALIZABLE closes it — because the row does not exist yet, so there is nothing to lock. The two real fixes are: perform the operation atomically with an upsert, or let a unique constraint catch it and handle the violation. Both require the unique constraint. It is the constraint doing the work, not the syntax.

The four spellings

The four spellings of upsert, and what differs between them.
-- PostgreSQL, SQLite 3.24+ — the clearest of the four
INSERT INTO customers (email, name, city_id)                 -- the row we propose…
VALUES (?, ?, ?)                                             -- …with bound parameters
ON CONFLICT (email) DO UPDATE                                -- name the CONSTRAINT's columns
  SET name = EXCLUDED.name, city_id = EXCLUDED.city_id       -- EXCLUDED = the proposed row
RETURNING id;                                                -- the id either way, insert or update
--       ^ DO NOTHING is the other option, when a re-delivery should be a no-op.

-- MySQL / MariaDB
INSERT INTO customers (email, name, city_id) VALUES (?, ?, ?)   -- the same proposal…
ON DUPLICATE KEY UPDATE name = VALUES(name),                    -- …VALUES(col) is MySQL's EXCLUDED
                        city_id = VALUES(city_id);              -- one assignment per column
--                                ^ it fires on ANY unique key, not one you name. That is the risk.

-- SQL Server / Oracle — MERGE
MERGE INTO customers AS t                                    -- the target table
USING (VALUES (?, ?, ?)) AS s (email, name, city_id)         -- the source: an inline one-row table
   ON t.email = s.email                                      -- how a target row is matched
WHEN MATCHED THEN UPDATE                                     -- the row exists…
  SET t.name = s.name, t.city_id = s.city_id                 -- …so update it
WHEN NOT MATCHED THEN INSERT (email, name, city_id)          -- it does not exist…
  VALUES (s.email, s.name, s.city_id);                       -- …so insert it
--  SQL Server: the terminating semicolon is REQUIRED, and the error if you forget
--  it is unhelpful.
What actually makes these safe — and what does not
  • All three rely on a unique constraint. Without one there is nothing to conflict with, and ON CONFLICT is a syntax error rather than a silent risk — which is the good outcome.
  • EXCLUDED (PostgreSQL) and VALUES(col) (MySQL) both mean “the value that would have been inserted”. Referring to the plain column name instead gives you the value already stored, which is how an upsert quietly becomes a no-op.
  • MySQL's version fires on any unique key. With two unique columns you cannot tell which one collided, so the update may target the wrong row entirely.
  • MERGE is the most flexible and the most dangerous: in several engines it is not atomic against concurrent inserts without HOLDLOCK, so two sessions can both decide “not matched”. Prefer the native upsert where one exists.
⚠ Trap — MERGE has a reputation, and it earned it

SQL Server's MERGE has a documented history of bugs and surprises, and several well-known SQL Server MVPs advise avoiding it outright:

  • It is not atomic against concurrent inserts unless you take an explicit HOLDLOCK / SERIALIZABLE hint on the target — the default can still raise a duplicate-key error under concurrency.
  • If the USING source produces more than one row per target row, it raises error 8672 sometimes, depending on the plan.
  • It has historically interacted badly with triggers, filtered indexes and foreign keys.

Knowing that MERGE exists and that the safe SQL Server pattern is often a plain UPDATE followed by an INSERT … WHERE NOT EXISTS in one transaction is a genuinely senior answer.

Idempotency: the actual requirement

🎓 What “idempotent” buys you

An operation is idempotent if running it twice has the same effect as running it once. That is what lets you retry safely — and every distributed system you will ever touch retries: the ETL job that failed at 03:40, the payment webhook delivered twice, the message queue that guarantees at-least-once. Without idempotency, “just re-run it” is a sentence that creates duplicate invoices.

The three mechanisms

1 · A natural business key with a unique constraint. The most robust, because the database enforces it whatever the application does.

Idempotency by constraint: the reference database's payment table, and the re-delivered webhook it survives.
-- The payments table in the reference database does exactly this:
external_ref TEXT NOT NULL,                          -- the payment provider's own id
CONSTRAINT uq_payments_ref UNIQUE (external_ref)     -- "the same payment, once"

-- so a re-delivered webhook becomes a no-op rather than a second payment:
INSERT INTO payments (order_id, amount_cents, method, external_ref)  -- name the columns
VALUES (?, ?, ?, ?)                                                 -- all four bound
ON CONFLICT (external_ref) DO NOTHING;                              -- second delivery: 0 rows
Why the constraint, and not the application, is the right place
  • Every payment provider retries. Delivery is “at least once”, never “exactly once”, so the receiver must be idempotent — and the only component that can guarantee it under concurrency is the one that serialises writes.
  • DO NOTHING and not DO UPDATE: a payment that has already been recorded must not be modified by a retry. The second delivery affects 0 rows, and the handler can return success honestly.
  • The unique key is the provider's reference, not one you generate. An id you invent per request is different on every retry, which is exactly the property you do not want.
  • Check the row count if the caller needs to know: 1 means recorded, 0 means already had it.

2 · A watermark. For incremental loads: record the highest source timestamp or id you have processed, and next time start from there.

The watermark pattern: load what is new, and record how far you got, in one transaction.
-- Load only what is new since last time, and record the new high-water mark
-- INSIDE THE SAME TRANSACTION as the load.
BEGIN;                                                    -- both statements, or neither
  INSERT INTO fact_orders (...)                           -- the load…
  SELECT ... FROM staging.orders                          -- …from the source…
  WHERE updated_at > (SELECT watermark                    -- …restricted to what is new
                      FROM etl_state WHERE job = 'orders');  -- one marker row per job

  UPDATE etl_state                                        -- move the marker…
  SET watermark = (SELECT MAX(updated_at) FROM staging.orders)  -- …to the newest row seen
  WHERE job = 'orders';                                   -- one row per job
COMMIT;                                                   -- now both are permanent
Why the two statements must share a transaction
  • Commit the load and crash before the update, and the next run reprocesses those rows. Commit the update and crash before the load, and the rows are lost forever with nothing reporting it. One transaction removes both cases.
  • > and not >=: with >= the boundary row is loaded again every run. With > you lose rows sharing the exact watermark timestamp — which is why the load itself should still be idempotent (an upsert), so either choice is survivable.
  • MAX(updated_at) over an empty source is NULL, and setting the watermark to NULL makes the next comparison UNKNOWN and loads nothing, forever. Guard it: COALESCE(MAX(updated_at), watermark).
  • The source must never write a timestamp older than one already loaded — a clock skew between application servers is enough to break that assumption.
⚠ Trap — the strictly-greater-than watermark

> watermark loses rows whose timestamp ties with the maximum but which were committed after you read it. >= reprocesses the boundary — which is fine, and correct, precisely because the load is idempotent. This is the pairing that matters: an idempotent load lets you use the safe, overlapping window. If your load is not idempotent you are forced into the lossy comparison.

3 · Delete-then-insert by partition. For a full daily reload of one slice:

Delete-then-insert by slice: idempotent by construction.
BEGIN;                                                     -- one unit
  DELETE FROM fact_sales WHERE sale_date = ?;              -- the whole slice, whatever is in it
  INSERT INTO fact_sales SELECT ... WHERE sale_date = ?;   -- rebuild it from the source
COMMIT;                                                    -- readers see old or new, never half
-- Naturally idempotent: re-running replaces the slice with itself. On a
-- partitioned table this becomes a partition swap and is nearly instant. See ch. 31.
What it costs, and when it is the right shape
  • Re-running is safe by construction: the delete removes whatever a previous partial run left behind. No watermark, no upsert key, no merge logic.
  • The cost is a full delete and re-insert of the slice — fully logged, and holding locks on those rows for the duration. Fine for a nightly slice; not fine for a slice being read by users during business hours, unless the engine gives readers a consistent snapshot (chapter 22).
  • On a partitioned table the same idea becomes TRUNCATE PARTITION or a partition swap: a metadata change instead of millions of logged deletes.
  • The two ? parameters must receive the same value. Bind it once in the application, or you have written a very efficient way to delete data and not replace it.

Upserting a batch, not a row

Upserting a batch: one set-based statement, and a filter that skips rows nothing changed in.
-- Load a staging table first, then ONE set-based upsert. Row-at-a-time upsert
-- from application code is the N+1 problem wearing a different hat.
INSERT INTO customers (email, name, city_id)                 -- the target columns
SELECT s.email, s.name, s.city_id                            -- …from staging, not from VALUES
FROM staging_customers s                                     -- the whole batch, one statement
WHERE s.email IS NOT NULL                                    -- the conflict key must exist
ON CONFLICT (email) DO UPDATE                                -- same conflict clause as one row
  SET name = EXCLUDED.name, city_id = EXCLUDED.city_id       -- take the incoming values
  WHERE customers.name    IS DISTINCT FROM EXCLUDED.name     -- …but only if something changed…
     OR customers.city_id IS DISTINCT FROM EXCLUDED.city_id; -- …null-safely compared
The WHERE on the DO UPDATE is the interesting part
  • Without it, a nightly load rewrites every row whether or not anything changed: dirty pages, write-ahead log, replication traffic, and triggers firing on a no-op. With it, an unchanged night writes nothing.
  • IS DISTINCT FROM and not <>: comparing NULLs with <> yields UNKNOWN, so a row whose city_id went from NULL to a value would look unchanged and be skipped. IS DISTINCT FROM is null-safe and always TRUE or FALSE.
  • SQL Server has no IS DISTINCT FROM; there the portable spelling is EXISTS (SELECT a EXCEPT SELECT b), which uses the set operators' NULL rules.
  • Everything here is set-based: one statement, one pass, one transaction, for any number of rows. That is the difference between a load that takes a second and one that takes an hour.
✔ That WHERE clause is the senior detail

A nightly load where 99% of rows are unchanged still rewrites every row without it — bloating the table, filling the log, and firing every trigger. Adding the “only if different” predicate turns a two-hour job into a two-minute one, and it is the kind of thing nobody notices until somebody looks.

Prove it in the playground: the same insert, twice, with and without the conflict clause.
-- First, prove the constraint is real:
INSERT INTO payments (order_id, amount_cents, method, external_ref)  -- a payment already recorded
VALUES (1, 5000, 'card', 'PAY-00000001');                            -- with an existing reference
-- UNIQUE constraint failed: payments.external_ref  ← the write is REFUSED

-- Now the same insert, told what to do about it:
INSERT INTO payments (order_id, amount_cents, method, external_ref)  -- identical row…
VALUES (1, 5000, 'card', 'PAY-00000001')                             -- …identical reference…
ON CONFLICT (external_ref) DO NOTHING;                               -- …handled deliberately
-- 0 rows. The re-delivered webhook has been dealt with.
What the two results tell you
  • The first statement fails. That is the database refusing to hold two payments with one provider reference — the guarantee no amount of application code can offer under concurrency.
  • The second reports 0 rows. Nothing was written and nothing failed, which is exactly what a retry handler wants to see.
  • The difference between an error and a no-op is one clause. The safety came from the constraint in both cases.
  1. Check-then-insert is a race. No isolation level below serialisable closes it, because the row does not exist to be locked.
  2. An upsert is only safe because of the unique constraint. The constraint does the work; the syntax is convenience.
  3. ON CONFLICT (PostgreSQL/SQLite) names the constraint; ON DUPLICATE KEY (MySQL) fires on any of them.
  4. SQL Server's MERGE needs HOLDLOCK to be concurrency-safe, and has a long list of known issues. Update-then-insert in a transaction is often better.
  5. Idempotent means running twice equals running once — which is what makes “just re-run it” safe.
  6. Three mechanisms: a unique business key, a watermark, or delete-and-reload by slice.
  7. Update the watermark in the same transaction as the load, and use >= because an idempotent load can absorb the overlap.
  8. Upsert a batch from a staging table, not a row at a time, and skip rows that have not changed.

Interview questions this chapter answers

How do you make a nightly import safe to re-run?

Give every source record a stable business key, put a unique constraint on it, and load with an upsert — so a re-run updates rather than duplicates. For incremental loads, advance the watermark inside the same transaction as the insert, and use an inclusive comparison, which is safe precisely because the load is idempotent.

What is wrong with SELECT-then-INSERT?

There is a window between the two in which another session can insert the same row. It is a race that only appears under concurrency, so it passes every test and fails in production. Use an atomic upsert, or let a unique constraint reject the duplicate and handle the violation.

Would you use MERGE?

In Oracle, yes. In SQL Server I am cautious: it is not concurrency-safe without HOLDLOCK, it has a documented history of bugs, and it errors unpredictably when the source has duplicate keys. For a simple upsert I would often write an update followed by an insert-where-not-exists in one transaction, which is easier to reason about.