Double-submit race: identify concurrent requests that create one resource twice

The symptoms

• •Application or support reports two duplicate resource records created within milliseconds of each other, often sharing identical form payloads, user identifiers, or idempotency keys.
• •Database contains two rows with the same natural key (email, external order id, slug, etc.) created in the same second, yet a unique constraint on that key is defined in the schema.
• •Application logs show two near-simultaneous HTTP POST or PUT requests from the same session or user, both returning 200 OK or 201 Created rather than one failing with a uniqueness error.
• •Users report being charged or notified twice after retrying a slow submit button, double-clicking, or refreshing a checkout or signup flow.
• •Idempotency keys appear in logs attached to two distinct successful responses, instead of one response and one replay.
• •Server-side metrics show brief spikes in concurrent requests against the same create endpoint, with no corresponding increase in uniqueness-violation errors.

Likely causes

• •Uniqueness check is implemented as a SELECT-then-INSERT pair in application code rather than as a database-enforced unique index, so two concurrent transactions both read zero rows before either INSERT commits.
• •The application transaction isolation level allows non-repeatable reads, so a uniqueness probe executed outside the write transaction does not see in-flight inserts from sibling requests.
• •Write happens before the unique index sees both rows, for example with deferred constraint timing, batched writes, or a write-through cache that buffers and flushes asynchronously.
• •Idempotency key is checked against a cache or session store that has not yet observed the first request, so the second request bypasses dedupe and proceeds to the database.
• •A retry, double-click, or queued background job replays the same payload after the original request is in-flight but before its row is visible, and the dedupe layer keys on a transient value such as a process-local counter.
• •Logical duplication comes from a multi-step flow that creates the row, then creates a related row, with uniqueness only enforced on the related row, leaving the parent resource duplicated.

First ten minutes

1. 01Capture the two suspect request payloads, timestamps, and client identifiers from the access log or application log; confirm they target the same endpoint and resource type and that their timestamps differ by less than a second.
2. 02Query the database for the duplicated rows, recording their primary keys, insertion timestamps, and the values of any candidate natural key or idempotency key column.
3. 03Determine whether a unique index actually exists on the candidate natural key by inspecting the schema or running a read-only catalog query; a missing or disabled index is the single most common root cause.
4. 04Check the application's uniqueness-check code path to see whether the check is a SELECT before the INSERT, and whether both calls run inside the same transaction.
5. 05Capture the database isolation level and lock-wait behavior during the window the duplicates were created, to rule out isolation-driven non-repeatable reads.

Evidence to collect

• •The exact rows in the affected table, including created_at or inserted_at timestamps with subsecond precision and the values in any natural-key, idempotency-key, or business-uniqueness column.
• •Database catalog metadata showing the defined indexes, unique constraints, and exclusion constraints on the table and any related tables in the create flow.
• •Access log or HTTP server log entries for both requests, with method, path, status code, request id, user id, and any Idempotency-Key or X-Request-Id header value.
• •Application log lines emitted from the dedupe or uniqueness probe, showing whether the SELECT returned zero rows for each request and the ordering relative to the INSERT.
• •Database session statistics such as pg_stat_activity or the platform equivalent, captured during the incident window, to identify concurrent transactions against the same table.
• •Transaction isolation level setting for the application connection pool, plus any explicit SET TRANSACTION or SET SESSION CHARACTERISTICS statements executed by the code.

Where to look

• •The boundary between the application's uniqueness probe and the database write, where a SELECT-then-INSERT pattern straddles two transactions and loses atomicity.
• •The unique index definition on the candidate natural key, including whether it is partial, deferred, or filtered in a way that excludes the rows actually being inserted.
• •The transaction isolation boundary for the write path, including any explicit SET TRANSACTION ISOLATION LEVEL statements and the connection pool default.
• •The idempotency-key storage boundary, whether it is the database, a shared cache, or an in-process map that does not coordinate across instances or workers.
• •The retry and replay boundary, where the client, message queue, or background worker can re-issue the same payload before the first request has committed.

Diagnostic steps

1. 01List all unique and exclusion constraints on the affected table from the database catalog; if the natural key duplicated by the incident is not covered, that is the root cause.
2. 02If the unique constraint exists, check whether it is deferred or partial; deferred constraints allow the violating rows to be visible inside the transaction until COMMIT, and partial constraints exclude rows that do not match the predicate.
3. 03Trace the code path from request entry to the INSERT statement and classify the uniqueness check as one of: database-enforced index, application SELECT, cache lookup, or none.
4. 04If the check is an application SELECT, confirm whether the SELECT and the INSERT run inside the same transaction; if not, two concurrent transactions can both observe zero rows.
5. 05Inspect the connection pool default isolation level and any explicit overrides on the write path; compare against the level required for the dedupe strategy to be sound.
6. 06Reproduce the race under controlled concurrency by issuing two requests with identical payloads within a few milliseconds and observing whether both INSERTs succeed or one fails with a unique-violation error.
7. 07Examine the idempotency-key storage for write visibility: if it is an in-process map or a non-durable cache, two application instances or a restart between the two requests can bypass dedupe.
8. 08Correlate database session statistics with the two request timestamps to confirm that both transactions were indeed in-flight at the same time and competing for the same row slot.

Common mistakes

• •Adding application-level locking or a singleton mutex without first confirming the database already has the correct unique index; this masks the symptom without preventing duplicates under crash or restart.
• •Assuming a SELECT COUNT or SELECT EXISTS inside the write transaction is sufficient without verifying the transaction is the default REPEATABLE READ or SERIALIZABLE for that engine.
• •Catching the unique-violation error in code and retrying with the same payload, which converts a successfully prevented duplicate into a second committed row if the retry uses a fresh key.
• •Keying dedupe on a value that is regenerated per attempt, such as a process-local UUID or a timestamp, so each retry appears unique to the dedupe layer.
• •Treating two 5xx responses during the incident as unrelated load shedding when they are actually the database rejecting the second write; failing to log the underlying constraint name hides the cause.
• •Adding a unique index on the wrong column, such as a surrogate id or a soft-delete flag column, while the true natural key remains unenforced.

Safe fixes

• •If the unique index is missing on the natural key, add a UNIQUE INDEX on that key as a separate online operation and wrap the application's write in a retry loop that converts unique-violation errors into idempotent replays for the same idempotency key.
• •If the index exists but the application uses SELECT-then-INSERT outside a transaction, collapse the check into a single INSERT and rely on the database to reject the second writer, surfacing the error rather than swallowing it.
• •If the transaction isolation level allows the race, change the write path to use the engine's strongest default that still supports the application's concurrency, and verify with concurrent test traffic before rollout.
• •If the idempotency-key store is in-process, move it to the database table that holds the resource, with the key as an additional unique column, so dedupe survives restarts and is consistent across instances.
• •If the retry layer regenerates the key on each attempt, propagate the original idempotency key from the client or upstream queue so a retry reuses it and converges on a single resource.
• •For background or queued retries, add a server-side lease or row claim so only one worker can hold a given dedupe key at a time, releasing the lease only after the INSERT has committed.

Prove the fix

1. 01Run a controlled concurrency test that issues N identical create requests for the same natural key within a short window, and assert that exactly one row exists in the table afterward.
2. 02Inspect database statistics or constraint counters to confirm that the unique-violation event count increased by N-1 for the test, proving the database rejected the duplicates rather than the application.
3. 03Replay the original incident payloads from logs against the fixed code path and verify that the second request returns the existing resource rather than creating a new row, and that both responses reference the same primary key.
4. 04Verify that an application restart between the two requests no longer produces duplicates, proving the dedupe key is durable rather than in-process.
5. 05Confirm that an integrity check query selecting rows grouped by the natural key and having count greater than one returns zero rows in production, and add this as a recurring regression check.

Prevention and next steps

• •Treat natural keys and idempotency keys as schema-level concerns: require a unique index or constraint on any column that semantically identifies a resource, and add it to schema review checklists.
• •Forbid SELECT-then-INSERT dedupe patterns in code review unless the SELECT and INSERT are demonstrably in the same transaction and at an isolation level that prevents the race.
• •Propagate idempotency keys from clients, gateways, and message queues end to end so retries and double-clicks converge on the same server-side record.
• •Instrument the write path to log the database constraint name on any uniqueness error, and alert on sustained rates of such errors on create endpoints as a leading indicator of contention.
• •Add a periodic data-integrity job that groups by natural keys and reports any count greater than one, with paging on regression, so silent duplicates cannot accumulate unnoticed.

Safe commands and checks

psql -h <host> -p <port> -U <user> -d <database> -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename = '<table>';"
psql -h <host> -p <port> -U <user> -d <database> -c "SELECT conname, contype, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = '<table>'::regclass;"
psql -h <host> -p <port> -U <user> -d <database> -c "SELECT pid, state, query_start, wait_event_type, wait_event FROM pg_stat_activity WHERE datname = '<database>' ORDER BY query_start;"
psql -h <host> -p <port> -U <user> -d <database> -c "SHOW transaction_isolation;"
psql -h <host> -p <port> -U <user> -d <database> -c "SELECT <natural_key>, COUNT(*) FROM <table> GROUP BY <natural_key> HAVING COUNT(*) > 1;"
psql -h <host> -p <port> -U <user> -d <database> -c "SELECT relname, n_tup_ins, seq_scan, idx_scan FROM pg_stat_user_tables WHERE relname = '<table>';"