Application‑level retry with ON CONFLICT DO NOTHING versus DuckDB’s automatic SERIALIZABLE retry for exactly‑once writes

Answer

For predictable behavior across DuckDB versions, prefer an explicit application-level retry loop with an idempotent INSERT ... ON CONFLICT DO NOTHING. Treat any engine-level automatic SERIALIZABLE retry as an optimization only, not as the correctness mechanism for exactly-once writes.

Confirmed facts

• ON CONFLICT DO NOTHING is idempotent only when a PRIMARY KEY or UNIQUE constraint covers the columns that a retry could duplicate. Without that constraint, retries can create duplicate business rows.
• A unique constraint must encode a real business invariant, not just a retry workaround. The idempotency key should be deterministic from the payload, e.g., a natural key or a stable derived key. Do not use autoincrement/rowid as the idempotency key because retries may allocate new values.
• ON CONFLICT DO NOTHING suppresses duplicate-key errors but does not make non-unique duplicate rows safe and cannot repair partial multi-statement transactions. Keep the logical write in one transaction and make the final INSERT the single idempotent step.

Likely explanation and version sensitivity

I cannot confirm from memory that DuckDB automatically retries SERIALIZABLE transactions or returns SQLSTATE 40001 in all builds. DuckDB transaction and concurrency behavior has changed across releases, and ON CONFLICT syntax and conflict-target support vary by version. Relying on a hidden, non-configurable engine retry policy is therefore a weak foundation for exactly-once semantics.

Application-level retry keeps the policy explicit and testable. The engine-level approach hides the retry policy, which may evolve between releases, and still requires a primary key or unique index to avoid duplicate inserts when a retry occurs.

Schema design impact of the uniqueness requirement

1. Choose a deterministic natural key from the payload, or derive a stable surrogate key, and declare it NOT NULL UNIQUE or PRIMARY KEY before writers start.
2. If no natural key exists, add an explicit key column or use a staging table with a unique constraint plus deduplication.
3. Do not rely on SELECT-then-INSERT alone unless it is inside a serializable transaction and backed by a unique constraint.

Verification steps for this case

• Create the table with the intended PRIMARY KEY/UNIQUE constraint, run the same INSERT ... ON CONFLICT DO NOTHING twice and after a rollback, then COUNT(*) to confirm one row.
• In a scratch database, run a conflicting INSERT without ON CONFLICT and print the client error class/SQLSTATE/message; compare it with 40001 for your exact version.
• Run the retry loop with a forced transient failure such as an aborted transaction, then confirm the second attempt commits exactly one row with no orphan rows.

One missing diagnostic detail that changes the recommendation: the exact DuckDB version you are targeting and whether writers are single-process embedded or multi-process. Multi-process file access changes locking and whether a retry can succeed at all.