UPSERT with INSERT … ON CONFLICT — how do you insert or update atomically without races?

Q: UPSERT with INSERT … ON CONFLICT — how do you insert or update atomically without races?

**Target Audience:** Backend developers who write "create or update" code and candidates who will be asked why the naive version is unsafe. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 (concurrency tests, the batch example) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples on this page). MySQL and SQL Server syntax notes are cited from their documentation, not run. > **How to read this doc:** Every idea is explained in plain language first. Then a `📌 Interview term:` callout gives the precise vocabulary an interviewer expects to hear. Every query result, error message and timing below comes from an actual run, and the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Two cashiers share one paper guest list at a party entrance. A guest called Priya walks up to cashier A while her twin walks up to cashier B with the same name on the ticket. Both cashiers glance at the list: "Priya? Not there yet." Both write her n

The problem

Question presented to candidate: "Two API servers receive the same 'add 5 units of SKU G-700' message at the same moment. Today the code does SELECT to see if the row exists, then INSERT or UPDATE. Why does that break under concurrency, and how would you write it with INSERT ... ON CONFLICT so it is atomic? What exactly does PostgreSQL do when the two statements collide?"

What a strong answer should cover:

Why check-then-act races: both sessions see "no row" in their own snapshot, both INSERT, the loser gets a unique violation (or, without a constraint, a duplicate row)
INSERT ... ON CONFLICT (key) DO UPDATE SET col = excluded.col (or DO NOTHING): one statement, decided at the unique index, so exactly one outcome per key
The conflict target must match a unique index or constraint; excluded is the row that was proposed; reference the old value with the table name (inventory.qty + excluded.qty)
What happens on a collision in PostgreSQL READ COMMITTED: the second session waits for the first to commit, then updates the committed row (or inserts if the first rolled back); under REPEATABLE READ it fails with a serialization error instead
Limits: one statement cannot touch the same key twice (deduplicate the batch first), MERGE is not a race-free replacement in PostgreSQL, and the syntax differs per engine (MySQL ON DUPLICATE KEY UPDATE, SQL Server MERGE plus locking hints)

Clarifying questions expected:

Is there already a unique constraint on the business key (here sku), or does it have to be added first?
On conflict, should the new value replace the old one, add to it, or be ignored?
Which isolation level does the application run at?

Code / implementation expected: Yes — an upsert with ON CONFLICT ... DO UPDATE and excluded, and an explanation (ideally a timeline) of two sessions colliding.

Schema used by the examples:

upsert
on-conflict
postgres

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Backend developers who write "create or update" code and candidates who will be asked why the naive version is unsafe. Difficulty: Medium Engines: PostgreSQL 16.15 (concurrency tests, the batch example) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples on this page). MySQL and SQL Server syntax notes are cited from their documentation, not run.

How to read this doc: Every idea is explained in plain language first. Then a 📌 Interview term: callout gives the precise vocabulary an interviewer expects to hear. Every query result, error message and timing below comes from an actual run, and the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Two cashiers share one paper guest list at a party entrance. A guest called Priya walks up to cashier A while her twin walks up to cashier B with the

The solution is hidden so you can try first

17 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLRestock: add to existing SKUs, insert new ones (ON CONFLICT DO UPDATE with excluded)

SQLPage-view counter: one statement increments or creates, RETURNING shows the result

SQLDO NOTHING: an existing key is left untouched and nothing is written

PostgreSQL: deduplicate a batch with GROUP BY before the upsert

WITH batch(sku, name, qty, updated_at) AS (
  VALUES ('A-100', 'USB-C cable', 1, '2024-05-20'),
         ('A-100', 'USB-C cable', 2, '2024-05-21'),
         ('F-600', 'Mouse pad',   4, '2024-05-21')
), up AS (
  INSERT INTO inventory (sku, name, qty, updated_at)
  SELECT sku, MIN(name), SUM(qty), MAX(updated_at)
  FROM batch
  GROUP BY sku
  ON CONFLICT (sku) DO UPDATE
  SET qty        = inventory.qty + excluded.qty,
      updated_at = excluded.updated_at
  RETURNING sku, qty, updated_at
)
SELECT sku, qty, updated_at FROM up ORDER BY sku;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.