Concurrency-safe upserts in SQL Server, PostgreSQL and SQLite

Insert-or-update, usually called upsert, shows up everywhere: syncing a product catalog, recording when a device was last seen, maintaining a counter. The logic usually starts as an existence check followed by either an UPDATE or an INSERT. It passes every single-user test, then fails under concurrency, when two sessions handle the same new key at the same moment.

Each of these engines now has a proper tool for the job, and each tool has its own trap: MERGE in SQL Server, the choice between ON CONFLICT and MERGE in PostgreSQL, and INSERT OR REPLACE in SQLite. This article covers why the naive version breaks, the concurrency-safe pattern for each engine, and the details that cause trouble in production.

Check-then-act: the race in the naive version

The naive version looks like this:

IF EXISTS (SELECT 1 FROM dbo.Stock WHERE Sku = @Sku)
    UPDATE dbo.Stock SET Qty = @Qty WHERE Sku = @Sku;
ELSE
    INSERT dbo.Stock (Sku, Qty) VALUES (@Sku, @Qty);

Two sessions run it at the same moment for a new SKU. Both find no row, both go to the INSERT, and one of them fails with a primary key violation. Without a unique constraint, you get a duplicate row instead, which is worse. Wrapping the code in a transaction doesn't help under the default READ COMMITTED isolation, because the shared locks from the existence check are released right away. The check and the action have to be atomic.

How the tooling evolved

For a long time there was no good built-in answer. In SQL Server, the choices were the race-prone pattern above, a retry loop that catches the duplicate-key error, or MERGE, which comes with its own concurrency caveats. Before PostgreSQL 9.5, the options were PL/pgSQL loops or writable CTEs. In SQLite, INSERT OR REPLACE was the usual choice, and it does something different from what most people assume. Each engine now has a better option.

SQL Server

UPDLOCK + SERIALIZABLE

The pattern recommended by many experienced SQL Server practitioners looks like this:

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.Stock WITH (UPDLOCK, SERIALIZABLE)
SET Qty = @Qty
WHERE Sku = @Sku;

IF @@ROWCOUNT = 0
    INSERT dbo.Stock (Sku, Qty) VALUES (@Sku, @Qty);

COMMIT TRANSACTION;

SERIALIZABLE takes a key-range lock even when the row doesn't exist, so a second session can't slip an insert into that gap. UPDLOCK makes the two sessions queue up instead of deadlocking, which is what happens when both take shared range locks and then both try to convert them. When the row exists, this costs one seek. When it doesn't, it costs one seek plus one insert. You need a unique index on Sku, both for correctness and so the range lock covers a narrow key range instead of a whole table.

On SQL Server 2025 with optimized locking, these hints are still honored, but statements that carry them don't get lock after qualification. That's an acceptable trade here, because correctness is the point of the hints.

MERGE

MERGE dbo.Stock WITH (HOLDLOCK) AS t
USING (VALUES (@Sku, @Qty)) AS s (Sku, Qty)
    ON t.Sku = s.Sku
WHEN MATCHED THEN
    UPDATE SET Qty = s.Qty
WHEN NOT MATCHED THEN
    INSERT (Sku, Qty) VALUES (s.Sku, s.Qty);

Without HOLDLOCK, MERGE has the same race as IF EXISTS. Microsoft's own MERGE documentation says that HOLDLOCK prevents unique key violations in this scenario. The same page also warns that at scale MERGE "might introduce complicated concurrency issues", and that separate statements may perform better under heavy concurrency. Add to that a long history of community-reported bugs, documented in detail over the years, and my recommendation is to use MERGE for carefully tested batch synchronization jobs, not for hot single-row upserts.

PostgreSQL

INSERT ... ON CONFLICT (9.5+)

INSERT INTO stock (sku, qty)
VALUES ('A-100', 5)
ON CONFLICT (sku) DO UPDATE
SET qty        = EXCLUDED.qty,
    updated_at = now()
WHERE stock.qty IS DISTINCT FROM EXCLUDED.qty;

The documentation is explicit: ON CONFLICT DO UPDATE guarantees an atomic insert-or-update outcome, even under high concurrency. EXCLUDED refers to the row you tried to insert. The optional WHERE turns a no-change update into a no-op, so no new row version gets written when nothing changed. The conflict target must match a unique index or constraint. Use DO NOTHING if you only want "insert if missing".

MERGE (15+)

MERGE INTO stock AS t
USING (VALUES ('A-100', 5)) AS s (sku, qty)
    ON t.sku = s.sku
WHEN MATCHED THEN
    UPDATE SET qty = s.qty
WHEN NOT MATCHED THEN
    INSERT (sku, qty) VALUES (s.sku, s.qty)
RETURNING merge_action(), t.sku, t.qty;

PostgreSQL 15 added MERGE. PostgreSQL 17 added RETURNING with merge_action() and WHEN NOT MATCHED BY SOURCE, which makes a full sync including deletes possible in one statement. The PostgreSQL docs point you to INSERT ... ON CONFLICT when concurrent inserts are a concern, though. MERGE follows ordinary isolation rules, so two sessions inserting the same new key can still hit a unique violation. My rule: ON CONFLICT for concurrent upserts, MERGE for batch reconciliation.

PostgreSQL 18: OLD and NEW in RETURNING

Telling whether a row was inserted or updated used to rely on the xmax = 0 trick, which depends on internals. PostgreSQL 18 makes it explicit:

INSERT INTO stock (sku, qty)
VALUES ('A-100', 5)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty
RETURNING old.qty AS old_qty,
          new.qty AS new_qty,
          (old.sku IS NULL) AS inserted;

For a plain insert, every old value is NULL. On the update path of ON CONFLICT DO UPDATE, it holds the previous row.

SQLite

INSERT OR REPLACE: a delete plus an insert

REPLACE is a conflict resolution strategy. When a unique or primary key constraint fails, SQLite deletes the existing row and inserts the new one. The consequences:

• Columns you didn't specify revert to their defaults or NULL. The old values are lost.
• If the conflict is on a unique column other than the INTEGER PRIMARY KEY, the new row can get a different rowid.
• Delete triggers fire only when recursive_triggers is enabled. The update hook isn't called for the deleted row.
• Foreign keys that reference the row see a delete followed by an insert, which is not something you want happening near cascading actions. Test it against your schema.

Native UPSERT (3.24.0+)

INSERT INTO stock (sku, qty)
VALUES ('A-100', 5)
ON CONFLICT (sku) DO UPDATE
SET qty        = excluded.qty,
    updated_at = CURRENT_TIMESTAMP
WHERE excluded.qty <> stock.qty;

UPSERT arrived in SQLite 3.24.0 (2018-06-04). Since 3.35.0 (2021-03-12), you can chain multiple ON CONFLICT clauses, and the last one can be DO UPDATE without a conflict target. Inside DO UPDATE, a bare column name means the existing value, and excluded. means the value you tried to insert.

There's a parser quirk when the values come from a SELECT. The ON can be read as a join condition, so the docs recommend always adding a WHERE clause, even WHERE true:

INSERT INTO stock (sku, qty)
SELECT sku, qty FROM staging WHERE true
ON CONFLICT (sku) DO UPDATE SET qty = excluded.qty;

UPSERT doesn't work on virtual tables, and it only handles uniqueness conflicts. NOT NULL, CHECK and foreign key failures still raise errors.

Pitfalls

• No unique constraint. Every pattern in this post depends on one. Without it, nothing stops duplicates.
• Duplicate keys in one batch. PostgreSQL refuses to have ON CONFLICT DO UPDATE affect the same row twice in one statement, and MERGE raises a cardinality violation. Deduplicate the input first.
• Sequence gaps. In PostgreSQL, a default nextval() is evaluated before the conflict is detected, so upserts consume IDs. That's harmless, but people notice it.
• Retrying without understanding. A catch-and-retry loop on duplicate key errors can work, but it adds round trips and hides design problems.

Summary and checklist

In PostgreSQL and SQLite, INSERT ... ON CONFLICT ... DO UPDATE is the right default, and in PostgreSQL it is documented as atomic under concurrency. In SQL Server, UPDATE WITH (UPDLOCK, SERIALIZABLE) followed by a conditional INSERT inside one transaction is the safe pattern for single-row upserts. Keep MERGE for tested batch work, and keep INSERT OR REPLACE away from rows whose identity matters.

1. Put a unique constraint on the business key.
2. PostgreSQL and SQLite: INSERT ... ON CONFLICT (key) DO UPDATE, with EXCLUDED/excluded for the incoming values.
3. SQL Server: UPDATE WITH (UPDLOCK, SERIALIZABLE), then INSERT if nothing was updated, all in one transaction.
4. Use MERGE for tested batch syncs, and in SQL Server always with HOLDLOCK.
5. In SQLite, never use INSERT OR REPLACE when you mean "update".
6. Test with two sessions running at the same time, not just one.