PostgreSQL

PostgreSQL — ERROR: could not serialize access due to concurrent update

Written and reviewed by Sahil Srivastav

PostgreSQLIsolationConcurrency
ERROR:  could not serialize access due to concurrent update
CONTEXT:  while updating tuple (0,7) in relation "inventory"

What this error actually means

Your transaction read a row under `REPEATABLE READ` or `SERIALIZABLE`, another transaction committed a change to that row, and yours then tried to update it. PostgreSQL cannot let the update proceed without breaking the isolation guarantee it promised you — your transaction’s snapshot no longer reflects committed reality — so it aborts yours instead.

This is not corruption or a bug in the database. It is the guarantee working. Under these isolation levels PostgreSQL uses optimistic concurrency: transactions proceed without blocking, and conflicts are detected at commit or update time rather than prevented by locks up front. The cost of not blocking is that some transactions must be retried.

Crucially, the victim is always safe to replay. Nothing was partially applied — the transaction rolled back atomically. So unlike most errors, the correct handling here is usually a retry loop rather than a fix, and the presence of this error means your isolation level is protecting you from a lost update that `READ COMMITTED` would have allowed silently.

Causes, most common first

  1. 1Read-modify-write on a contended row. The canonical case: read the current stock or balance, compute a new value in application code, write it back. Two transactions overlap on the same row and one must lose. Under `READ COMMITTED` this same code silently loses an update instead.
  2. 2Long transactions widening the conflict window. The longer a transaction holds its snapshot, the more likely another commit lands on a row it read. Transactions that include network calls or heavy processing collide constantly.
  3. 3SERIALIZABLE predicate conflicts. Under full serialisable isolation, conflicts can arise between transactions that never touch the same row — a read of a range that another transaction inserts into. The error may name a relation your transaction never wrote.
  4. 4Batch jobs competing with request traffic. A bulk update sweeping many rows conflicts with whatever interactive transactions touched them. The batch is usually the victim, having read the most.
  5. 5No retry logic at all. Not a cause of the conflict but the reason it becomes a user-visible error. Serialisation failures are an expected outcome of optimistic concurrency; code that does not retry is incomplete rather than unlucky.

When you see it

  • A small percentage of transactions fail under concurrency and succeed immediately on retry
  • Rate scales with contention on specific hot rows — a stock count, a balance, a shared counter
  • Appears only after raising isolation above `READ COMMITTED`, often during a correctness fix
  • Under `SERIALIZABLE`, it can also fire for read-only transactions from phantom conflicts
  • Retry storms if every client retries immediately with no jitter

How to diagnose it

Step 1

Confirm the isolation level in effect

Frameworks and connection settings can raise isolation without anyone noticing. Check what the session actually uses rather than what you believe it uses.

SHOW transaction_isolation;
SELECT current_setting('default_transaction_isolation');

Step 2

Identify the contended rows

The `CONTEXT` line names the relation and tuple. Aggregating these across occurrences shows whether contention is spread or concentrated on a handful of hot rows — which determines whether retry is sufficient or the data model needs changing.

Step 3

Measure the rollback rate

A conflict rate of a fraction of a percent is normal and healthy. Double digits means contention is a throughput problem and optimistic concurrency is the wrong strategy for that row.

SELECT xact_commit, xact_rollback,
       round(100.0 * xact_rollback / (xact_commit + xact_rollback), 2) AS rollback_pct
FROM pg_stat_database WHERE datname = current_database();

Step 4

Measure transaction duration for the failing path

Conflict probability rises with the window between first read and commit. If the failing transactions are seconds long, shortening them will reduce the rate more than any tuning.

The fix

Implement a bounded retry with exponential backoff and jitter, and treat it as the primary handling rather than a workaround. Retry the whole transaction — reopening it re-reads the now-committed state, which is exactly what makes the replay correct. Retrying only the failed statement inside the same transaction cannot work, since the snapshot is still stale.

Shorten the transaction to shrink the conflict window. Move reads that do not need to be in the snapshot outside it, and keep external calls out entirely. This reduces the conflict rate more reliably than any other change.

For genuinely hot rows where the conflict rate is high, switch that path from optimistic to pessimistic: `SELECT ... FOR UPDATE` serialises access up front, trading concurrency for predictability. Retry storms on a hot row are worse than a short queue.

Better still, remove the read-modify-write. Let the database do the arithmetic atomically — `UPDATE inventory SET qty = qty - $1 WHERE id = $2 AND qty >= $1` needs no snapshot and no retry, and the affected-row count tells you whether it succeeded. Most of these conflicts exist only because the computation happened in application code.

For `SERIALIZABLE` predicate conflicts, narrow what each transaction reads. Reading an unnecessarily wide range creates conflicts with inserts that have nothing to do with your logic.

-- Read-modify-write: needs a retry loop and can conflict
SELECT qty FROM inventory WHERE id = $1;      -- app computes qty - n
UPDATE inventory SET qty = $2 WHERE id = $1;

-- Atomic and conditional: no snapshot dependency, no conflict, no retry
UPDATE inventory
   SET qty = qty - $1
 WHERE id = $2
   AND qty >= $1;
-- 0 rows affected => insufficient stock, handled as a business outcome

How to stop it coming back

  • Wrap every transaction at these isolation levels in a retry policy as standard infrastructure, not per-call code
  • Prefer atomic conditional updates over read-then-write; most serialisation conflicts are avoidable arithmetic
  • Keep transactions short and free of network calls — window length is the dominant factor in conflict rate
  • Alarm on rollback percentage, so contention shows up as a metric rather than as user-visible errors
  • Choose isolation deliberately per workload rather than globally; `READ COMMITTED` with explicit locking is right for some paths

Practise this failure in a real repository

Gronex ships the read-modify-write failure as a runnable repository: inventory that oversells under concurrent purchases. The tests hammer it with parallel buyers and assert stock never goes negative, so a retry loop around the same logic does not pass.

FAQ

Is retrying the right fix, or a hack?

It is the intended design. Optimistic concurrency trades blocking for occasional aborts, and the aborted transaction is atomically rolled back, so replay is always safe. Code that uses these isolation levels without a retry policy is incomplete.

Should I just use READ COMMITTED to make it stop?

That removes the error and reintroduces the bug it was preventing. Under `READ COMMITTED` the same read-modify-write silently loses an update — no error, wrong balance. If you drop the isolation level, you must add explicit locking or an atomic conditional update in its place.

Why does a read-only transaction get this error?

Under `SERIALIZABLE`, PostgreSQL tracks predicate dependencies, so a read-only transaction can be part of an unserialisable cycle with two writers. Marking it `READ ONLY DEFERRABLE` lets PostgreSQL wait for a safe snapshot instead of aborting.

How is this different from "deadlock detected"?

Different mechanism entirely. A deadlock is a cycle in lock waits, detected after `deadlock_timeout` of actual waiting. This is a snapshot conflict detected without anyone waiting at all. Deadlocks point at lock ordering; serialisation failures point at contention and transaction length.

Related

PostgreSQL: deadlock detectedInventory overselling under concurrencyDatabase engineering interview questions

Other errors engineers hit next to this one

Full error and symptom index →