Skip to content
|By Muhammad Wilayat Shah|6 views • 6 unique IPs

How to Fix PostgreSQL Could Not Serialize Access

Fix PostgreSQL could not serialize access (SQLSTATE 40001): check the isolation level, retry the whole transaction, and keep serializable work short.

ShareLinkedInX
How to Fix PostgreSQL Could Not Serialize Access

A payment, stock update or booking request suddenly fails with ERROR: could not serialize access due to concurrent update, and retrying the same request a second later works fine. The postgresql could not serialize access error (SQLSTATE 40001, serialization_failure) is not a bug in PostgreSQL. It is the database protecting your data: two transactions running at REPEATABLE READ or SERIALIZABLE isolation touched the same data, and PostgreSQL cancelled one of them rather than let it commit a result that could never happen if they had run one after another. This guide explains why it happens, how to reproduce it, and how to fix it properly with transaction retries in SQL, Laravel, Node.js and Prisma.

Problem: PostgreSQL Could Not Serialize Access

PostgreSQL uses MVCC (multi-version concurrency control). At REPEATABLE READ and SERIALIZABLE, a transaction works from a snapshot taken at its first query. If another transaction changes and commits a row after that snapshot, and your transaction then tries to update or delete the same row, PostgreSQL cannot apply your change on top of data you never saw. Instead of silently overwriting it (a lost update), it aborts your transaction with a serialization failure.

At SERIALIZABLE, PostgreSQL goes further. Serializable Snapshot Isolation (SSI) also tracks what each transaction read, and cancels one transaction when a pattern of reads and writes could produce a result that no serial order would produce, even if no single row was written twice.

Symptoms

You will see one of these messages in application logs, the PostgreSQL log or psql:

Code
ERROR:  could not serialize access due to concurrent update

ERROR:  could not serialize access due to concurrent delete

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

Framework wrappers show the same thing with a different prefix:

  • Laravel: SQLSTATE[40001]: Serialization failure: 7 ERROR: could not serialize access due to concurrent update
  • node-postgres: an error object with code: '40001'
  • Prisma: error code P2034, "Transaction failed due to a write conflict or a deadlock. Please retry your transaction"

Typical patterns: the error appears only under load, during queue worker bursts or in parallel tests; it never happens when you run the request alone; and a manual retry succeeds.

postgresql could not serialize access: how two concurrent transactions cause SQLSTATE 40001 and how a retry loop fixes it
Two transactions touch the same row; PostgreSQL aborts one, and your code retries it.

Root Cause of the Serialization Failure

1. A higher isolation level than you realised

The default in PostgreSQL is READ COMMITTED, where this error does not occur for normal updates. If you see it, something raised the level: default_transaction_isolation in postgresql.conf, a per-role or per-database setting, an ORM option, or an explicit BEGIN ISOLATION LEVEL SERIALIZABLE in your code.

2. Concurrent writes to the same rows

The classic case at REPEATABLE READ: two requests read the same account, order or stock row, both decide to update it, and the second one to write loses. Hot rows such as counters, wallet balances and inventory totals hit this constantly.

3. Read/write dependencies at SERIALIZABLE

At SERIALIZABLE, a transaction that reads rows another transaction later changes (or the other way round) can be cancelled even when they wrote different rows. Queries that do sequential scans make this worse, because PostgreSQL then takes a predicate lock on the whole table instead of on individual index pages or rows.

4. Long transactions

The longer a transaction stays open (slow external API calls, waiting on user input, big batch loops), the more likely another transaction commits a conflicting change in between. Transactions left open are covered in PostgreSQL idle in transaction.

Step-by-Step Solution

Step 1: Confirm the isolation level in use

Code
-- server, database and role defaults
SHOW default_transaction_isolation;
SELECT datname, setconfig FROM pg_db_role_setting s
  LEFT JOIN pg_database d ON d.oid = s.setdatabase;

-- inside the failing transaction
SHOW transaction_isolation;

If you did not mean to run at REPEATABLE READ or SERIALIZABLE, removing that setting may be the simplest fix. Only do that if the code does not rely on the stronger guarantee.

Step 2: Reproduce it with two psql sessions

Seeing the failure on demand makes the fix easy to test:

Code
-- setup
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO accounts VALUES (1, 100);

-- session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- snapshot taken: 100

-- session B (autocommit)
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- session A
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
-- ERROR:  could not serialize access due to concurrent update
ROLLBACK;

Step 3: Add a PostgreSQL transaction retry

The official PostgreSQL guidance is that applications using REPEATABLE READ or SERIALIZABLE must be ready to retry transactions that fail with SQLSTATE 40001. The retry must re-run the whole transaction from BEGIN, including the reads, because the decisions you made were based on stale data. Retrying only the failed statement does not work: the transaction is already aborted.

Good retry rules: retry only on 40001 (and 40P01 deadlocks if you like), cap attempts at 3 to 5, wait a short randomised delay between attempts, and never perform side effects such as sending email or calling a payment API inside the retried block. Backoff with jitter is explained in exponential backoff for API retries.

Step 4: Keep transactions short and indexed

Move HTTP calls, file processing and anything slow outside the transaction. Make sure the WHERE clauses inside serializable transactions use indexes, so predicate locks stay narrow. Mark reporting transactions as READ ONLY; for long reports at SERIALIZABLE, SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE waits for a safe snapshot and then can never fail with a serialization error.

Step 5: Consider READ COMMITTED with explicit locking

Many hot-row problems do not need a stronger isolation level at all. An atomic update or a row lock at READ COMMITTED avoids the failure and the retry:

Code
-- atomic: no read-then-write race
UPDATE accounts SET balance = balance - 20
WHERE id = 1 AND balance >= 20
RETURNING balance;

-- or lock the row you are about to change
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
COMMIT;

FOR UPDATE makes the second transaction wait instead of fail. Pick this when the logic is about a few known rows; keep SERIALIZABLE plus retries for complex rules across many rows, where manual locking is easy to get wrong. More on this trade-off in how to prevent duplicate transactions.

Code and Commands Examples

Laravel: DB::transaction with attempts

Laravel retries a closure passed to DB::transaction when it detects a concurrency error such as SQLSTATE 40001, up to the number of attempts you pass as the second argument:

Code
use Illuminate\Support\Facades\DB;

DB::statement('SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE');

$order = DB::transaction(function () use ($id, $qty) {
    $item = DB::table('stock')->where('id', $id)->first();
    if ($item->quantity < $qty) {
        throw new RuntimeException('Out of stock');
    }
    DB::table('stock')->where('id', $id)->update(['quantity' => $item->quantity - $qty]);
    return DB::table('orders')->insertGetId(['stock_id' => $id, 'qty' => $qty]);
}, 5); // retry up to 5 times on serialization failure

// send emails and dispatch jobs AFTER the transaction returns

Laravel only retries the outermost transaction, so avoid wrapping this call inside another DB::transaction.

Node.js with node-postgres

Code
async function withSerializableRetry(pool, work, attempts = 5) {
  for (let i = 1; ; i++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await work(client);
      await client.query('COMMIT');
      return result;
    } catch (err) {
      await client.query('ROLLBACK').catch(() => {});
      if (err.code !== '40001' || i >= attempts) throw err;
      await new Promise(r => setTimeout(r, Math.random() * 50 * 2 ** i));
    } finally {
      client.release();
    }
  }
}

Prisma

Code
import { Prisma } from '@prisma/client';

for (let i = 1; i <= 5; i++) {
  try {
    await prisma.$transaction(async (tx) => {
      // reads and writes here
    }, { isolationLevel: Prisma.TransactionIsolationLevel.Serializable });
    break;
  } catch (e) {
    if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === 'P2034' && i < 5) continue;
    throw e;
  }
}

Verification

  1. Re-run the two-session psql test against your code path: the losing transaction should now succeed on its retry, and the final balance should equal the sum of both changes.
  2. Run a quick concurrency test (for example 50 parallel requests with xargs -P or a load tool) and check that no request returns a 500 with SQLSTATE 40001, and that totals add up.
  3. Log every retry with an attempt counter. A few retries under load are normal; retries hitting the cap mean the transaction is too long or too broad.
  4. While a serializable workload runs, SELECT mode, count(*) FROM pg_locks GROUP BY mode; shows SIReadLock predicate locks; very high counts point to sequential scans that need an index.

Prevention

  • Choose the isolation level per use case on purpose, and document it next to the code.
  • Wrap every REPEATABLE READ or SERIALIZABLE transaction in one shared retry helper rather than ad hoc try/catch blocks.
  • Keep transactions short: no network calls, no user waits, no large loops inside them.
  • Index the columns used in WHERE clauses inside serializable transactions.
  • Use atomic UPDATE ... WHERE statements or SELECT ... FOR UPDATE for single hot rows such as counters and balances.
  • Make retried work idempotent so a retry can never double-charge or double-insert. The wider topic is covered in race conditions in programming.

FAQs

What does SQLSTATE 40001 serialization failure mean?

It means PostgreSQL cancelled your transaction because committing it alongside a concurrent transaction would break the isolation guarantee you asked for. Nothing from the cancelled transaction is saved, so it is safe to run it again from the start.

Is it safe to just retry a PostgreSQL transaction?

Yes, as long as you retry the whole transaction, including its reads, and the retried block has no outside side effects. PostgreSQL's own documentation recommends retrying on this error.

Why does this error not happen in READ COMMITTED?

At READ COMMITTED, an UPDATE that finds a row changed by a committed transaction re-checks the new row version and continues instead of failing. That avoids the error but allows anomalies such as lost updates in read-then-write code, which is why locking or atomic updates matter at that level.

Should I use SERIALIZABLE or REPEATABLE READ?

SERIALIZABLE gives the strongest guarantee and protects complex business rules, at the cost of more serialization failures. REPEATABLE READ gives a stable snapshot and blocks lost updates but still allows write skew. Both need retry logic.

Is a deadlock the same as a serialization failure?

No. A deadlock (SQLSTATE 40P01, "deadlock detected") happens when transactions wait on each other's locks, at any isolation level. It is also safe to retry, and many teams handle both codes in the same retry helper.

ShareLinkedInX
Back to blog

Keep reading

Newsletter

Occasional notes on Laravel, React, and shipping production systems — no spam.

Comments

Join the discussion. Replies load on demand, up to 5 levels.

0 threads

Loading editor…
Reviewed before publishing

No comments yet

Be the first to start the conversation.