40001 is not a query error

Mohamed Aboelmagd — Tue, 25 Aug 2026 18:15:52 +0000

The PostgreSQL manual is unusually direct about this:

When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning.

"The whole transaction" is doing a lot of work in that sentence, and it is the part that gets dropped.

TypeORM issue #9806 — "Auto Retry options on error in transactions (e.g. Deadlock)" — has been open since February 2023. Thirty 👍, six comments, no implementation. Meanwhile typeorm-transactional, at 188,000 downloads a week, ships @Transactional() with isolation levels and seven propagation modes and no retry at all.

So the ecosystem's actual answer to "how do I use SERIALIZABLE in Node" is: don't. Use READ COMMITTED, don't think about write skew, and hope.

I spent a while building the thing that issue asks for. The short version of what I found: the feature as literally requested cannot be built correctly, and the reason is more interesting than the feature.

The implementation everyone reaches for first

Wrap the query. It's the obvious move — the error came from a query, so retry the query:

async function withRetry<T>(fn: () => Promise<T>, attempts = 3): Promise<T> {
  for (let i = 1; ; i++) {
    try {
      return await fn();
    } catch (e) {
      if (i >= attempts || !isSerializationFailure(e)) throw e;
      await sleep(50 * i);
    }
  }
}

await dataSource.transaction('SERIALIZABLE', async (em) => {
  const from = await em.findOneOrFail(Account, { where: { id: fromId } });
  const to   = await em.findOneOrFail(Account, { where: { id: toId } });

  await withRetry(() => em.decrement(Account, { id: fromId }, 'balance', amt));  // ← here
  await withRetry(() => em.increment(Account, { id: toId },   'balance', amt));  // ← and here
});

This does nothing. Worse than nothing — it turns one clear error into a confusing one.

When PostgreSQL raises 40001, it does not fail that statement. It aborts the entire transaction. The connection is now in a failed transaction state, and every subsequent statement on it — including the retry you just issued — comes back as:

25P02  current transaction is aborted, commands ignored until end of transaction block

So withRetry burns its three attempts on a statement that is guaranteed to fail three times, then throws 25P02 instead of 40001. You've replaced the actionable error with a meaningless one and added 150ms of sleeping to do it.

The same is true of deadlocks. A 40P01 victim's transaction is dead, not its last statement.

By the time you have an error to react to, there is no query left to re-issue.

It's worse than that: you don't know where it will fire

I wrote a test asserting that under SERIALIZABLE, the failure surfaces at COMMIT — because that's how I understood SSI to work. Serializable Snapshot Isolation tracks read/write dependencies and looks for a dangerous structure; I assumed the check happened at commit time.

The test passed on PostgreSQL 14. Passed on 16. Passed on 17.

Failed on 15.

My first instinct was that 15 had changed something. It hadn't — and the real answer is worse for anyone writing this code.

I ran the write-skew reproduction 50 times against each version, no ORM in the path, recording which statement raised the error:

Version	Reported at `UPDATE`	Reported at `COMMIT`
14.23	0	50
15.19	3	47
16.15	1	49
17.10	1	49

15 is not an outlier — it sits between its neighbours, and the spread is noise. An earlier 25-round pass against 14.23 reported at the UPDATE twice; the 50-round pass above reported it zero times. Same version, same machine, same script. So the version cannot be the explanatory variable. It's a race, and how often you lose it tracks how busy the box is.

My test wasn't catching a quirk of PostgreSQL 15. It was flaky on every version I ran it against, and 15 was just where the coin first landed tails.

PostgreSQL raises 40001 as soon as its machinery notices the dangerous structure. Usually that's at COMMIT, after every statement in your transaction has already returned successfully. A few percent of the time it's the conflicting statement itself. It is not something you can predict, and it is not something you should write code against.

The test that replaced it asserts the only thing that's actually stable:

/**
 * Where the failure surfaces is **not fixed**, and that is the point.
 * [...] Either way the *entire* transaction is aborted, so there is no single
 * statement a caller could usefully re-issue. That is what makes
 * whole-transaction retry the only sound design, and per-statement retry —
 * what TypeORM issue #9806 literally asked for — unimplementable.
 */
it('surfaces at COMMIT or at the conflicting statement, never predictably', async () => {
  const [a, b] = await produceWriteSkew(dataSource);
  const failure = reasonOf(a) ?? reasonOf(b);
  expect(failure.query).toMatch(/COMMIT|UPDATE doctor/i);
});

This is the general lesson, and it's the one I'd keep even if you never touch SERIALIZABLE: assertions about database behaviour that you derived by reasoning are hypotheses. Run them against the versions you actually support. I had a clean mental model of SSI and it was wrong in a way that only a version matrix could show me.

Which pushes retry up to the transaction boundary

If you can't retry the statement, retry the thing that owns the transaction. Whatever called dataSource.transaction() has to roll back, open a fresh connection and transaction, and re-run the entire callback from the top.

That's a small change in where the loop goes and an enormous change in what the API means, because now your callback runs more than once.

// ✗ BROKEN — sends two emails and charges the card twice when it retries once
@Transactional({ isolation: 'SERIALIZABLE', retry: { maxAttempts: 5 } })
async transfer(from: string, to: string, amount: number) {
  await this.accounts.decrement({ id: from }, 'balance', amount);
  await this.accounts.increment({ id: to }, 'balance', amount);

  await this.mailer.send(to, 'You received a payment');
  await this.stripe.charges.create({ amount });
  await this.kafka.publish('transfer.completed', { from, to });
}

ROLLBACK undoes the two database writes. It does not un-send the email, un-charge the card, or un-publish the message.

The fix is to defer everything non-transactional until after the transaction is durable:

// ✓ FIXED
@Transactional({ isolation: 'SERIALIZABLE', retry: { maxAttempts: 5 } })
async transfer(from: string, to: string, amount: number) {
  await this.accounts.decrement({ id: from }, 'balance', amount);
  await this.accounts.increment({ id: to }, 'balance', amount);

  runOnCommit(async () => {
    await this.mailer.send(to, 'You received a payment');
    await this.kafka.publish('transfer.completed', { from, to });
  });
}

Rule of thumb: if undoing it needs more than ROLLBACK, it belongs in a commit hook.

I suspect this constraint is a large part of why #9806 has stayed open for three and a half years. Adding a retry: 3 option is an afternoon. Adding a retry: 3 option that doesn't silently double-charge people requires a commit-hook mechanism, a per-attempt reset of that registry, documentation of the hazard, and a decision about what to do with in-memory state that mutated on attempt one. It's a feature that drags a design in behind it.

Jitter is not a nice-to-have

Two transactions that just deadlocked are synchronised by construction. PostgreSQL killed one of them at the exact instant it let the other proceed. They are phase-locked.

Back both off by the same base · 2ⁿ and they wake up together and deadlock again. And again. Exponential backoff without jitter, applied to deadlock partners, is a machine for reproducing the deadlock you just recovered from.

Full jitter — random(0, min(cap, base · 2ⁿ)) — is what breaks the lock. It's the AWS architecture blog's recommendation and it's the right default here for a reason specific to this problem, not just as general good hygiene.

What it actually costs

Here is the part that most "add retry to your ORM" posts leave out. I benchmarked it: 600 contended read-then-write transfers per configuration, containerised PostgreSQL 17, three strategies, four concurrency levels, both a pathological and a realistic contention profile.

100 concurrent workers over 1,000 accounts:

	failure rate	throughput	p99
`SERIALIZABLE`, no retry	87%	109 ops/s	168 ms
`SERIALIZABLE` + retry	0%	109.8 ops/s	3,794 ms
`READ COMMITTED` + ordered `FOR UPDATE`	0%	382 ops/s	619 ms

Three things worth saying plainly.

Retry does what it claims. An 87% failure rate becomes zero. That is the entire difference between SERIALIZABLE being a thing you read about and a thing you can deploy.

That p99 is a trap. Unretried SERIALIZABLE looks twenty times better on p99 — 168ms against 3,794ms. It isn't. It's fast because failing is fast: 87% of those transactions did no useful work and returned quickly. You are measuring the latency of giving up. Whenever a config looks dramatically better on latency, check what fraction of its requests succeeded.

Retry does not make anything fast. It makes correctness available. Where I could enumerate the rows a transaction touches, READ COMMITTED with consistently-ordered FOR UPDATE beat SERIALIZABLE + retry at every single concurrency level I measured — 3.5× the throughput at this one. If you can order your locks, order your locks. Retry is for the case where you can't know in advance which rows you'll touch.

Under pathological contention (10 accounts, 100 workers) retry's throughput falls as workers are added — 77.6 ops/s at concurrency 1 down to 6.6 — because every conflict throws away a whole transaction's work. A high retry rate means retry is treating a symptom. Fix the contention.

And one thing I got wrong in public

I shipped 0.1.0 with observability callbacks typed like this:

export type RetryCallback = (info: RetryInfo) => void;

Every call site was wrapped in try/catch, because an exception from someone's metrics code must never break the transaction it's measuring. I thought that was covered.

It wasn't. TypeScript permits an async function anywhere a void-returning one is expected — this compiles clean under --strict:

onRetry: async (info) => { await metrics.push(info); }   // no error. none.

try/catch never sees that rejection. It becomes an unhandled rejection, and Node 15+ terminates the process on those. Mid-retry, with a transaction open. A flaky metrics backend could take down the service measuring it.

That's 0.1.1. Then I swept for the pattern instead of waiting for someone to hit it again — and found the diagnostic handler had the same shape, which was worse, because the diagnostic handler is the channel every other failure gets reported through. That's 0.1.2.

If you write callback APIs in TypeScript: tsc will not catch this. Type-aware ESLint's @typescript-eslint/no-misused-promises will, at the caller's site — but you can't rely on your users having it enabled. Guard the call.

The library

typeorm-resilient-transactional — @Transactional() for NestJS + TypeORM with SQLSTATE-classified retry, commit/rollback hooks, ordered-locking helpers, zero runtime dependencies, published with provenance.

It's API-compatible with typeorm-transactional, so migrating is one import line:

- import { Transactional, runOnTransactionCommit } from 'typeorm-transactional';
+ import { Transactional, runOnTransactionCommit } from 'typeorm-resilient-transactional';

npm i typeorm-resilient-transactional

The benchmarks above are reproducible with pnpm bench — the results file and the chart in it are both generated from the same run, so they can't drift from each other. If a number is in the README, it was measured.

I'd genuinely like to contribute the classifier and the backoff strategies upstream to TypeORM if there's appetite; I've said so on #9806. Until then, this exists.

DEV Community: Mohamed Aboelmagd