DEV Community: Ismail Iusifli

Postgres race conditions live in a window of a few milliseconds

Ismail Iusifli — Tue, 10 Nov 2026 08:48:26 +0000

I wrote a lost-update test, ran it, and found something I had not expected. The race was not hiding. It had a narrow window, and my tests either landed inside it or missed it completely depending on a few milliseconds of timing.

Here is the experiment. Two transactions read the same balance, subtract 10, and write the result back. No locks, so the correct final balance is 80 and a lost update leaves 90. The second transaction starts a fixed number of milliseconds after the first. I ran each setting 100 times against a local PostgreSQL 16 with Node 22, on two machines: a Windows 11 laptop and a Linux box.

Start offset of tx 2	Windows: plain run	Windows: fuzzer	Linux: plain run	Linux: fuzzer
0 ms	100 / 100	96–98 / 100	95 / 100	88 / 100
1 ms	96 / 100	92–96 / 100	81 / 100	88 / 100
2 ms	72 / 100	92–95 / 100	21 / 100	87–89 / 100
5 ms	5 / 100	93–94 / 100	0 / 100	85–86 / 100
15 ms	1 / 100	91 / 100	0 / 100	78–90 / 100

Lost updates out of 100 runs. The fuzzer columns show the range over two seeds (42 and 7) with a maximum injected delay of 30 ms.

If both transactions start together, as they do in a Promise.all test, the plain run finds the bug nearly every time on both machines. A few milliseconds later the picture changes. At a 5 ms offset the plain run found it 5 times out of 100 on one machine and never on the other, and at 15 ms it found it once or never. The width of the window depends on the machine: at a 2 ms offset the plain run found the bug 72 times on the Windows laptop and 21 times on the Linux box. On the Linux box a query through the proxy takes about 0.2 ms, so a four-statement transaction is over in roughly a millisecond.

Production code does not obviously start its transactions together. A connection pool hands out a client, a request handler does some work, a function awaits something before BEGIN. Gaps of a few milliseconds are plausible, though I have not measured them in a real service, and they put the race outside the window your test can see. The fuzzer columns are the tool I will describe below. It adds random delays before queries, so the two transactions overlap even when they start far apart. At 0 ms it does slightly worse than the plain run, because its random delays sometimes pull two transactions apart. It is built for the offsets where the plain run is blind.

This is a toy scenario, one row and no locking, so almost any widening of the window finds the bug. The point is not that the number is impressive. The point is that a green test does not tell you the race is absent, only that the test did not reach it.

What I built

pg-interleave is a proxy that speaks the PostgreSQL wire protocol (v3.0). It sits between your code and Postgres, and a test controls it over a small HTTP API. The server is written in Rust on tokio and the client is a TypeScript SDK. It is alpha software, MIT licensed, currently v0.7.1.

test code ──HTTP :9090──▶ control plane ─┐
                                          ▼
pg / any client ──:5433──▶ proxy ──────▶ PostgreSQL :5432

Because it understands the protocol, it can hold one specific statement while other connections keep running. Nothing in the application changes: you point the database URL at port 5433 instead of 5432.

Here is the same lost update, but deterministic. Client A reads 100 and plans to write 80. We freeze A's UPDATE inside the proxy, let B run a full transaction, then release A.

import { Client } from "pg";
import { PgInterleaveClient } from "pg-interleave";

const url = "postgres://postgres:postgres@127.0.0.1:5433/postgres";
const chaos = new PgInterleaveClient("http://127.0.0.1:9090");
const clientA = new Client({ connectionString: url });
const clientB = new Client({ connectionString: url });
await clientA.connect();
await clientB.connect();

// Hold A's UPDATE in the proxy. Postgres has not seen it yet.
const barrier = await chaos.intercept({
  client: clientA,
  onQueryMatch: /UPDATE accounts SET balance = \$1/i,
  action: "pause_before_query",
  times: 1,
});

await clientA.query("BEGIN");
const readA = await clientA.query("SELECT balance FROM accounts WHERE id = 1");
const balanceA = readA.rows[0].balance - 20; // 80

const txA = (async () => {
  await clientA.query("UPDATE accounts SET balance = $1 WHERE id = 1", [balanceA]);
  await clientA.query("COMMIT");
})();

await barrier.wait(); // A's UPDATE is now frozen in the proxy

// B runs a complete transaction while A is frozen
await clientB.query("BEGIN");
const readB = await clientB.query("SELECT balance FROM accounts WHERE id = 1");
await clientB.query("UPDATE accounts SET balance = $1 WHERE id = 1", [readB.rows[0].balance - 30]); // 70
await clientB.query("COMMIT");

await barrier.resume(); // A's stale write lands on top of B's commit
await txA;

const res = await clientB.query("SELECT balance FROM accounts WHERE id = 1");
console.log(res.rows[0].balance); // 80, but the correct total is 50

No sleep, no timing luck: the order is the same on every run. The repository also has the SELECT ... FOR UPDATE version of this scenario (packages/sdk-ts/src/lost-update.test.ts), where B waits on A's row lock and the result is correct.

Torn commits and retry loops

Reordering statements is half of it. The other half is what happens when a connection dies at the worst moment: after Postgres has committed, before the client hears about it.

The proxy can do exactly that. With drop_connection_on_commit it lets the commit finish on the server and then closes the client's socket. The client sees a network error for a transaction that actually succeeded.

Here is a function that inserts a $50 order, wrapped in a generic retry helper:

import pRetry from "p-retry";
import { Client } from "pg";
import { PgInterleaveClient } from "pg-interleave";

const url = "postgres://postgres:postgres@127.0.0.1:5433/postgres";
const chaos = new PgInterleaveClient("http://127.0.0.1:9090");
let attempts = 0;

const createOrder = async () => {
  attempts++;
  const client = new Client({ connectionString: url });
  client.on("error", () => {}); // the dropped socket emits an 'error' event
  await client.connect();

  if (attempts === 1) {
    await chaos.intercept({
      client,
      onQueryMatch: /COMMIT/i,
      action: "drop_connection_on_commit",
      times: 1,
    });
  }

  try {
    await client.query("BEGIN");
    await client.query("INSERT INTO billing_orders (account_id, amount) VALUES (101, 50)");
    await client.query("COMMIT");
  } finally {
    await client.end().catch(() => {});
  }
};

await pRetry(createOrder, { retries: 2, minTimeout: 50 });
// SELECT * FROM billing_orders WHERE account_id = 101  ->  2 rows, $100 charged

The first attempt commits and the socket is cut. p-retry sees an error, assumes nothing happened, and tries again. The table now has two rows.

The retry helper did what it was told. The transaction was not safe to repeat, because it had no idempotency key. The usual fix is a unique constraint plus INSERT ... ON CONFLICT (idempotency_key) DO NOTHING. A mock cannot show you this, because a mocked connection drop does not leave a real committed row behind.

The schedule fuzzer

Barriers are for interleavings you already thought of. The fuzzer is for the ones you did not. Start the proxy with --fuzz and it delays queries (including BEGIN and COMMIT) by a random amount up to --fuzz-max-delay-ms, so concurrent transactions reach the database in different orders from run to run.

pg-interleave --fuzz --seed 42 --fuzz-max-delay-ms 30

If you omit --seed, the proxy picks one and logs it. Each connection gets its own random stream derived from the seed and the connection id (SplitMix64), so the same seed gives every connection the same sequence of delays. Client-side timing still varies by a few milliseconds, so rerunning with a seed gives a close result rather than an identical one. That is visible in the Linux column above: seeds 42 and 7 differed by up to 12 runs out of 100 at the 15 ms offset.

Fuzzing samples schedules. A green run means those schedules passed, not that the code is free of races.

The 44 ms trap

My first benchmark was wrong, and the way it was wrong is worth knowing about. An early version of my README claimed that the fuzzer uncovers 100% of lost updates. That number was an artifact. The proxy was adding about 44 ms to every query, which is consistent with Nagle's algorithm interacting with delayed ACKs on the proxy's sockets. With queries that slow, each transaction lasted long enough that the two overlapped whether or not the fuzzer was on, so the comparison meant nothing.

Setting TCP_NODELAY on both the client-facing and the upstream socket dropped the added latency to about 0.2 ms per query, and the plain baseline at a 5 ms offset fell to between 0 and 5 out of 100, depending on the machine. If you build a proxy, measure what the proxy itself costs before you trust any timing-dependent result.

Testing it on pg-boss, and the bug in my own tool

To see how it behaves against software I did not write, I built a test suite for pg-boss v12.38.1, a job queue on top of Postgres. It has eight tests, and none of them found a bug in pg-boss:

A synthetic 40P01 deadlock injected into the fetch statement: the rule fired once, the worker kept polling and processed the job.
A torn commit on job completion: the job ended up completed and the handler ran exactly once.
The completion statement held in the proxy: the job stays active in the database while it is held and becomes completed after release.
A handler that sleeps past the expiry timeout: the job is picked up again (retry_count 1) and runs twice, which is the documented at-least-once behavior.
Ten concurrent send() calls with the same singleton key: one row.
Twenty and fifty concurrent jobs: each ran exactly once.

The more interesting result was about my own tool. My first version of the deadlock test passed, and it should not have. The deadlock was never injected.

pg-boss writes its fetch statement across several lines. Abridged from its source:

UPDATE pgboss.job_common j SET
  state = 'active',
  started_on = ...

My pattern was a single-line regex, UPDATE ... SET state = 'active', and in a regex . does not cross a newline. The rule never matched, the proxy forwarded the query untouched, the worker behaved normally, and the test went green. A rule that silently does not fire looks exactly like a system that handled the fault. I only learned about it when a reviewer ran my pattern against the real multi-line statement. A second problem sat next to it: the SDK passed RegExp.source to the proxy, which dropped flags such as /i.

Two changes came out of that:

The SDK now compiles a JavaScript RegExp with the dotAll flag and keeps i and m, so .* crosses newlines.
Every rule handle has assertFired(n), which throws if the rule did not fire exactly n times.

const deadlock = await chaos.injectDeadlock({
  onQueryMatch: /UPDATE pgboss\.job_common.*SET.*state\s*=\s*'active'/i,
  times: 1,
});

// ... run the scenario ...

await deadlock.assertFired(1); // this test fails if the fault never happened

Now an ORM that reformats its SQL breaks the test loudly instead of making it pass for the wrong reason.

Limitations

Plaintext only. The proxy answers N to SSLRequest, so sslmode=require does not work. disable and prefer do.
Flush pipelines. Drivers that rely on explicit Flush frames before sending Sync can stall the proxy.
Data-modifying CTEs. The AST selector looks at the top-level statement, so WITH u AS (UPDATE ...) SELECT ... is classified as SELECT. Use a regex for those.
PgBouncer in transaction mode. Backend PIDs change between transactions, so route rules with SQL comment tags instead.
Shared state. Rules and barriers live in the proxy process. Run test files serially and call reset() between tests.
No authentication on the control plane. Keep it on 127.0.0.1.
One toy benchmark, results vary by machine. The table above is a single scenario, and the race window was a different width on my two machines. I have not measured how the fuzzer behaves on a large real application.
Alpha. CI runs on PostgreSQL 14, 15 and 16. APIs may change.

When not to use it

If you can write your scenario as plain sequential awaits on two clients, you do not need a proxy. PostgreSQL's own isolationtester explores step permutations across sessions and is a better fit when you are testing the database engine itself. Toxiproxy works at the TCP level, which is the right layer for latency and dropped connections but cannot tell a SELECT from a COMMIT. pg-interleave is for the case in between: application code you do not want to modify, where you want to stop one specific statement.

Try it

npm install -D pg-interleave

Run the proxy, either from a release binary:

./pg-interleave --listen-addr 127.0.0.1:5433 --target-addr 127.0.0.1:5432

or with Docker (the arguments replace the image defaults, so pass all three):

docker run -d --name pg-interleave \
  --add-host=host.docker.internal:host-gateway \
  -p 127.0.0.1:5433:5433 -p 127.0.0.1:9090:9090 \
  ghcr.io/alt-f6/pg-interleave:latest \
  --listen-addr 0.0.0.0:5433 \
  --control-addr 0.0.0.0:9090 \
  --target-addr host.docker.internal:5432

Point your test database URL at port 5433. To reproduce the table:

cd examples && npm install
# plain run
OFFSETS=0,1,2,5,15 npx tsx lost_update_benchmark.ts
# with the fuzzer (start the proxy with: --fuzz --seed 42 --fuzz-max-delay-ms 30)
OFFSETS=0,1,2,5,15 npx tsx lost_update_benchmark.ts

The code is at github.com/alt-f6/pg-interleave. If you try it and something breaks, please open an issue. I would especially like to hear about drivers or ORMs where the proxy misbehaves.

Every step was correct. The student still got refunded twice.

Ismail Iusifli — Fri, 23 Oct 2026 11:32:32 +0000

How a randomized invariant test found a billing bug none of my example tests could, and why the fix needed a lock.

An admin cancels a lesson, so the student's charge is reversed. Later a teacher tidies up the attendance journal and clears the mark, and the charge is reversed again. The student ends up credited for a lesson they never paid for.

Each of those three operations was correct on its own, and every example test I'd written passed. A test that threw random operations at a real database found the bug on its first run. Here's how.

The setup

I'm the sole engineer of the platform behind an online school with 300+ active students a month. Teachers and admins mark attendance, and every billable mark charges the student for the lesson. Lessons get re-marked, cleared and cancelled, often by different people at the same time.

I pulled the money-handling core out into a public repo, edtech-billing-core, rewritten on plain PostgreSQL so every lock and constraint is visible. Two design rules matter for this story.

The ledger is append-only. A database trigger rejects UPDATE and DELETE on ledger rows. You never edit a charge: you append a reversal, then a new charge if needed. For each student and lesson there's a net, the sum of its rows, and it should always be either 0 or minus the lesson's price.

Writes can't form a lock cycle. Marks and clears lock the student row (FOR NO KEY UPDATE), then the lesson row (FOR SHARE). A cancellation locks only the lesson (FOR UPDATE). When it writes reversals, the foreign-key checks take KEY SHARE on each student, and NO KEY UPDATE doesn't block that, so there's no cycle and no deadlock. In practice: two writes for the same student run one after the other, marks on the same lesson coexist, and a cancellation waits for marks already in flight.

With those two rules in place, I had example tests for every operation: mark, re-mark, clear, cancel. They all passed. Then I wrote a different kind of test.

A test that doesn't know the answer

Example tests check outcomes you already thought of. The bug I was worried about was the one I hadn't thought of, so I wrote a test that throws random operations at the ledger and checks rules that must hold no matter what happened.

The rules are invariants, each written as a SQL query that returns the rows breaking it. An empty result means the rule holds:

-- "a cancelled lesson nets to zero for every student"
SELECT t.student_id
FROM ledger_transaction t
JOIN class_session s ON s.id = t.class_session_id
WHERE s.is_cancelled
GROUP BY t.student_id, t.class_session_id
HAVING SUM(t.amount) <> 0;

There are eight of them, for example: a lesson's reversals never exceed its charges, a free lesson never nets to a charge, each provider payment is credited at most once.

The test itself:

creates 4 students and 10 lessons with random prices, some of them free, and puts one student on a billing freeze;
runs 200 random operations for each of 8 seeds: marks, clears, cancellations, payments, and sometimes a redelivered payment to exercise idempotency;
runs them five at a time in parallel, against a real PostgreSQL;
checks all eight invariants after every batch, and that the ledger never shrinks.

The randomness is seeded, so a failure replays exactly:

function prng(seed: number) {
  let s = seed >>> 0;
  return () => {
    s = (s * 1664525 + 1013904223) >>> 0;
    return s / 2 ** 32;
  };
}

One more rule keeps the test honest: the only error it accepts is touching a lesson that's already cancelled. A lost race or a deadlock fails the test, and a guard at the end checks that at least 100 operations really succeeded, so the test can't pass by refusing everything.

It failed on the first run

The invariant that broke was "a cancelled lesson nets to zero for every student". Replaying the seed gave this sequence. Here it is as ledger rows for one student and one lesson priced at 500:

Step	Row appended	Net
Student marked present	`LESSON_CHARGE` −500	−500
Lesson cancelled	`CHARGE_REVERSAL` +500	0
Attendance cleared	`CHARGE_REVERSAL` +500	+500

The net should be 0. Instead the student was credited 500 for a lesson they were never charged for.

Each step on its own was correct. Marking charges. Cancelling reverses outstanding charges. Clearing reverses the lesson's charge. The bug only exists in that one order.

Here's the part that surprised me: this isn't a race. The three steps reproduce one call at a time, in order. What found it was the randomness, not the parallelism: random sequences hit orders I'd never write by hand. The parallel part of the test is there for the other class of bug, the real races, and as the next section shows, the fix for this one created exactly such a race.

The fix, and why it needs a lock

The obvious fix is a rule: clearing refuses a cancelled lesson, because the cancellation already reversed the charge.

But the rule alone opens a real race. If the clear checks is_cancelled, then a cancellation commits, then the clear writes its reversal, the double refund is back. So the check has to run under the lesson's share lock, the lock a cancellation must wait for:

export async function clearAttendance(
  db: Db,
  classSessionId: string,
  studentId: string,
) {
  await inTransaction(db, async (tx) => {
    // FOR NO KEY UPDATE on the student
    await lockStudent(tx, studentId);
    // FOR SHARE on the lesson
    const session = await lockSessionShared(tx, classSessionId);
    if (session.is_cancelled) {
      throw new BillingRuleError(
        "Cannot clear attendance for a cancelled lesson",
      );
    }
    // ... reverse the charge
  });
}

While the clear holds FOR SHARE on the lesson, a cancellation, which needs FOR UPDATE, waits. When the clear commits, the cancellation reads the nets after its lock wait, finds nothing outstanding, and writes nothing. If the cancellation gets there first, the clear sees is_cancelled and refuses. Either way, the net stays at zero.

Both cases are now permanent tests: the plain sequence, and the race. The race test fails if the share lock is removed.

Why not just SERIALIZABLE?

It's the first question people ask, so two answers.

It wouldn't have caught this bug. The double refund happens with the steps run one after another, which is a perfectly serializable history. Isolation levels protect you from bad interleavings, not from wrong rules.

I measured it. My production code wraps billing in SERIALIZABLE and also takes FOR UPDATE on the student, and the first version of this extraction copied that. A concurrency probe during code review showed roughly 9 in 10 concurrent writes aborting with serialization failures, and FOR UPDATE deadlocking against a cancellation's foreign-key checks. You can retry serialization failures, but when most writes fail under ordinary contention, retries become the workload. With one explicit lock order under READ COMMITTED, every concurrent call in the burst tests succeeds. That one deserves its own post.

What I took from it

Example tests check what you thought of. I had tests for clearing and tests for cancelling. I didn't have one for clearing after cancelling, because I hadn't thought of it. A randomized test doesn't need you to.

Write invariants, not expected values. "The balance should be 500" only holds for a case you designed. "A cancelled lesson nets to zero" holds after any sequence, which is what makes it useful against sequences you didn't design.

Test on the real database. The guarantees here live in locks, constraints and a trigger. A mocked database would have passed this test forever.

Make failures replayable. A random test that fails once and never again is noise. With a seed, the failure became a three-step story I could read, fix, and turn into two permanent tests.

Check that your tests can fail. The repo has a mutation script that removes each defence in turn (either lock, the cancelled-lesson guard, the idempotency key and more) and reruns the suite. All 12 mutants are killed, so I know the tests aren't passing by accident.

The full code, the invariant test and the mutation script are in edtech-billing-core. If you build billing, or anything else where a number has to be right, I'd like to hear how you test it.

I'm Ismail, a backend engineer focused on billing, payments and integrations. iusifli.dev · LinkedIn