DuckDB INSERT ON CONFLICT: unclear guarantees when two sessions upsert the same key concurrently

Question

I am designing a write path on DuckDB (0.8.0 or newer) where a client may retry an insert after a timeout. The plan is to rely on INSERT ... ON CONFLICT DO NOTHING (or DO UPDATE) against a table with a primary key, so a retried statement cannot create a duplicate row.

The single-session case is clear: with a unique constraint in place, re-issuing the same upsert leaves exactly one row. What I cannot pin down from the documentation is the concurrent case. When two transactions attempt an upsert on the same key at the same time, the statements appear to be serialized, but the exact isolation guarantees are not spelled out — in particular whether ON CONFLICT DO UPDATE can lose an update from the other transaction, and whether a serialization failure can surface to the client instead of a clean conflict resolution.

This matters because I need to know whether the upsert alone makes retries safe, or whether I still need application-level retry logic for deadlock/serialization errors on top of it.

Specifically:

Is INSERT ... ON CONFLICT in DuckDB atomic with respect to a concurrent upsert on the same key, or can a duplicate-key error or lost update still occur?
Can the statement fail with a serialization error that the application must retry, and if so, how is that distinguishable from a real constraint violation?
Is there a documented way (e.g., EXPLAIN output or logging) to confirm the conflict-check path is actually being used?

Tasadduq Burney · Answer

Direct answersAtomicity for the same key. Within a single DuckDB transaction, INSERT ... ON CONFLICT DO NOTHING or DO UPDATE against a primary key / unique constraint is atomic: re-issuing the statement leaves exactly one row and no duplicate-key error is raised.Concurrent sessions. DuckDB uses optimistic MVCC with serializable snapshot isolation. Two concurrent transactions that upsert the same key can both pass the in-transaction conflict check against their own snapshots and then conflict at commit time. In that case DuckDB aborts one of the transactions with a transaction-conflict error, e.g. "Transaction aborted due to concurrent modification". The abort is not a constraint violation resolved by ON CONFLICT; it is a serialization failure that the client must retry.DO UPDATE and lost updates. ON CONFLICT DO UPDATE does not provide a merge. If the second transaction is allowed to commit after the first, its SET expression is evaluated against the row as it exists in its snapshot. A pattern like SET col = col + 1 can therefore lose the first transaction’s increment unless the application retries or uses an explicit merge expression such as EXCLUDED.col. This is a logical limitation of the upsert expression, not a bug.Distinguishing errors. A conflict resolved by ON CONFLICT produces no error to the client. A serialization abort due to concurrent writes produces a transaction-level error that requires the whole transaction to be retried. DuckDB does not expose PostgreSQL-style SQLSTATE codes for this path; the error message and the fact that the transaction is aborted is the diagnostic.Verifying the path. There is no documented Conflict Check node name in DuckDB EXPLAIN output. EXPLAIN will show an Insert plan that references the target table and its unique index. The presence of ON CONFLICT in the statement is the only observable confirmation that conflict handling is enabled. DuckDB does not document a supported setting to log lock waits or a dedicated conflict-check trace.Likely explanation vs confirmed factsConfirmed: DuckDB supports INSERT ... ON CONFLICT DO NOTHING/DO UPDATE with primary keys and unique constraints, and it uses optimistic concurrency with serializable snapshot isolation. A transaction conflict can abort a concurrent writer.Likely but not definitively documented in public releases: the exact timing of when the unique index is checked relative to commit, and whether the engine always serializes two upserts on the same key without abort. Practical testing shows aborts can occur under contention, so retries are required for safe retries after timeouts.Minimal steps for this caseKeep the upsert as the idempotency mechanism for single-session retries.Wrap the upsert in an explicit transaction and be prepared to retry the whole transaction on a DuckDB transaction-aborted / concurrent modification error.Avoid read-modify-write in DO UPDATE unless you accept last-writer-wins or add application-level merging and retry.Do not rely on isolation-level settings to change this behavior; DuckDB does not expose SET TRANSACTION ISOLATION LEVEL or READ COMMITTED mode.One missing diagnostic detail that changes the recommendation: what DuckDB version and storage mode are you using, and are the two sessions writing to the same persistent file database with WAL enabled? In-memory databases and older 0.8.x releases have different concurrency characteristics, and the retry advice depends on whether you can observe transaction abort errors in your client.

DuckDB INSERT ON CONFLICT: unclear guarantees when two sessions upsert the same key concurrently

1 answer

Direct answers

Likely explanation vs confirmed facts

Minimal steps for this case

Your perspective belongs here.

0 question comments