Catching PostgreSQL Double-Spends in 60ms on the Wire Protocol (Without Touching App Code)

Imagine a classic wallet withdrawal endpoint in Go, Python, Node, or Rust. Everything is wrapped inside a database transaction:

tx, _ := pool.Begin(ctx)
defer tx.Rollback(ctx)

var balance int64
_ = tx.QueryRow(ctx, "SELECT balance FROM wallets WHERE id = $1", walletID).Scan(&balance)
if balance < amount {
    return ErrInsufficientFunds
}

_, _ = tx.Exec(ctx, "UPDATE wallets SET balance = balance - $1 WHERE id = $2", amount, walletID)
_ = tx.Commit(ctx)

Your unit tests pass. Your sequential integration tests pass. Code review looks clean.

Then in production, a mobile client retries on a flaky network or a webhook fires twice in the same millisecond. Wallet 1 starts with balance = 100. Both POST /withdraw requests for 100 return 200 OK. The database now says balance = -100.

Why? Because PostgreSQL defaults to READ COMMITTED, where each statement sees a snapshot of data committed before that statement began. Most critical concurrency bugs in backend systems aren't memory data races inside one process. They are interleavings of SQL transactions across the network:

• Check-then-act double spends
• Lost updates (SET balance = $computed overwriting a concurrent write)
• Write skew across multiple rows or aggregate checks (SELECT COUNT(*))
• Phantom inserts before uniqueness or quota checks
• Lock-order deadlocks when two endpoints touch rows in opposite order

Load tests (k6, wrk) only hit that microsecond race window by luck and give you no deterministic way to replay the failure. Thread model checkers like tokio-rs/loom only see threads and atomics inside a single Rust binary, stopping at the socket boundary.

So I built PgLoom, an open-source (Apache-2.0) deterministic race-condition model checker written in Rust that operates directly on the PostgreSQL v3 wire protocol.

Voskan / pgloom

Deterministic race-condition testing for PostgreSQL-backed services. A wire-protocol proxy explores how transactions interleave and finds double spends, lost updates and deadlocks, with exact replay. No code changes, any language.

PgLoom

Deterministic race-condition testing for anything that talks to PostgreSQL.

PgLoom is a model checker that sits between your service and PostgreSQL as a wire-protocol proxy. It fires concurrent requests at your service, holds every database round trip at a gate, and systematically explores how the transactions can interleave. When an interleaving breaks an invariant (a double spend, a lost update, a negative balance, a deadlock), it prints the exact schedule, explains the root cause, and writes a file that replays the bug deterministically in milliseconds.

No SDK, no code changes, no sleeps: point the service's DATABASE_URL at the proxy. It works the same for Go, Rust, Python, TypeScript, Java or anything else that speaks the PostgreSQL v3 protocol.

$ pgloom demo

Plain-text transcript

✗ RACE CONDITION DETECTED  [demo-wallet-withdraw]
  Explored 3/50 schedules in 66ms (1-preemption trace)
  Violated Invariants:
    • no_negative_balance: Wallet balance must never drop below zero
      Observed DB state:

…

View on GitHub

---

How PgLoom Works Without SDKs or Code Changes

Every database-backed service already funnels its state transitions through one narrow choke point: DATABASE_URL.

Instead of instrumenting your application code, PgLoom sits as a stateful TCP proxy between your unmodified service and a real PostgreSQL instance (tested across PostgreSQL 12 through 17):

1. Wire-Turn Gating (pgloom-wire): PgLoom speaks the PostgreSQL v3 frontend/backend protocol, parsing Simple Query, Extended Query pipelines (Parse, Bind, Describe, Execute, Flush, Sync), and COPY streams. Whenever a worker completes a protocol round trip, PgLoom holds that turn at a gate before forwarding it upstream. Because it works at the wire level, it doesn't care whether your app is written in Go (pgx), Python (psycopg / asyncpg), TypeScript (pg / Prisma), Java (JDBC / Hibernate), or Rust (sqlx / tokio-postgres).
2. Source-DPOR Schedule Exploration (pgloom-engine): Blindly permuting every query across workers blows up factorially. PgLoom extracts read/write footprints from SQL statements, enriches them with pg_catalog metadata (foreign keys and triggers), and uses Source-DPOR (dynamic partial-order reduction with sleep sets) to skip Mazurkiewicz-equivalent schedules that are guaranteed to produce the exact same database state.
3. Parking Row-Lock Waiters via pg_blocking_pids() (pgloom-pgctrl): What happens when Worker B executes UPDATE (or SELECT ... FOR NO KEY UPDATE) on a row already locked by Worker A? Postgres blocks Worker B inside the lock manager, so ReadyForQuery won't come back until Worker A finishes. A naive proxy would hang. PgLoom polls pg_blocking_pids(), recognizes that Worker B is blocked by Worker A, parks Worker B, hands control back to Worker A, and flags true lock cycles as deadlocks.
4. Real COMMITs + Sub-Millisecond Resets: A tempting shortcut in test harnesses is wrapping each run in BEGIN / SAVEPOINT / ROLLBACK. Unfortunately, faking commits with savepoints silently changes READ COMMITTED visibility semantics and hides real bugs. PgLoom executes genuine COMMITs against PostgreSQL on every schedule and restores dirty tables in <1ms from an UNLOGGED snapshot schema.

---

What a Scenario Looks Like

Pointing PgLoom at an existing service takes a single pgloom.yaml file. Here is a complete double-spend scenario against an HTTP wallet service:

version: 1
name: "wallet-withdraw-double-spend"

database:
  upstream: "postgres://postgres:postgres@127.0.0.1:5432/postgres"
  proxy_listen: "127.0.0.1:15432"
  setup_sql: |
    CREATE TABLE IF NOT EXISTS wallets (id BIGINT PRIMARY KEY, balance BIGINT NOT NULL);
    TRUNCATE wallets;
    INSERT INTO wallets (id, balance) VALUES (1, 100);

target:
  command: "go run ."
  env:
    DATABASE_URL: "postgres://postgres:postgres@127.0.0.1:15432/postgres?sslmode=disable"
    LISTEN_ADDR: "127.0.0.1:18080"
  ready_http: "http://127.0.0.1:18080/healthz"

workers:
  - id: "req_a"
    http:
      method: "POST"
      url: "http://127.0.0.1:18080/withdraw"
      headers: { Content-Type: "application/json" }
      body: '{"wallet_id": 1, "amount": 100}'
  - id: "req_b"
    http:
      method: "POST"
      url: "http://127.0.0.1:18080/withdraw"
      headers: { Content-Type: "application/json" }
      body: '{"wallet_id": 1, "amount": 100}'

invariants:
  sql:
    - name: "no_negative_balance"
      description: "Wallet balance must never drop below zero"
      assert_true: "SELECT COALESCE(MIN(balance), 0) >= 0 FROM wallets"
      diagnostic_query: "SELECT id, balance FROM wallets"
  responses:
    - name: "at_most_one_successful_withdrawal"
      max_2xx_count: 1

Running pgloom run explores the interleavings in ~60ms, prints the exact step-by-step execution trace, runs the built-in SQL Fix Advisor, and writes .pgloom-repro.json so anyone on your team (or CI) can replay that exact schedule deterministically via pgloom replay:

[FAIL] RACE CONDITION DETECTED  [wallet-withdraw-double-spend]
Explored 3/50 schedules in 66ms (1-preemption trace)
Observed DB state: id=1, balance=-100 (req_a=200, req_b=200)

+------+------------------------+------------------------+
| Step | Worker req_a (pid 205) | Worker req_b (pid 204) |
+------+------------------------+------------------------+
|  t3  | SELECT balance -> 100  |                        |
|  t4  | UPDATE balance - 100   |                        |
|  >>  |       --- context switch (preempt) ---->        |
|  t5  | [paused before COMMIT] | SELECT balance -> 100  |
|  t6  |                        | UPDATE balance - 100   |
|      |                        | [blocked by req_a]     |
|  t7  | COMMIT -> HTTP 200 OK  |                        |
|  t9  |                        | lock granted -> UPD 1  |
| t10  |                        | COMMIT -> HTTP 200 OK  |
+------+------------------------+------------------------+

Fix Option A (Pessimistic Lock):
  SELECT balance FROM wallets WHERE id = $1 FOR NO KEY UPDATE;
Fix Option B (Atomic Guarded Update):
  UPDATE wallets SET balance = balance - $1 WHERE id = $2 AND balance >= $1;

---

"Why Not Just Set SERIALIZABLE Everywhere?"

Whenever transaction anomalies come up, someone reasonably asks: why not turn on SERIALIZABLE (Postgres SSI) globally?

If you have low write contention and clean transaction-retry boundaries from day one, SSI is awesome. In practice, two things push most high-throughput production backends back toward READ COMMITTED with targeted locking or atomic predicates:

1. Hot-row 40001 retry storms: On hot merchant accounts, fee pools, or shared inventory counters, SERIALIZABLE aborts concurrent writers with SQLSTATE 40001 instead of queuing them cleanly in Postgres's lock manager. Under load bursts, that turns into retry loops and p99 tail-latency spikes, whereas FOR NO KEY UPDATE or a guarded UPDATE ... WHERE balance >= $1 serializes just that row in one pass.
2. Legacy codebases & side-effect hazards: Most existing services and ORMs aren't structured around pure, side-effect-free retry closures. Forgetting a 40001 retry handler leaks HTTP 500s under contention; retrying naively when the handler also touches Redis, logs an audit event, or calls a payment gateway risks duplicate side effects.

And if you do run SERIALIZABLE, PgLoom is complementary: it deterministically forces 40001 serialization conflicts in CI so you can prove your retry loop actually converges and preserves both DB and HTTP invariants.

---

Try It Locally in 10 Seconds

PgLoom ships with a self-contained pgloom demo command that spins up an in-process vulnerable wallet handler, catches the double-spend, patches the query with FOR NO KEY UPDATE, and re-verifies every interleaving (using your local Postgres or spinning up a throwaway postgres:17-alpine container):

# Via Cargo (Rust 1.89+):
cargo install pgloom --locked
pgloom demo

# Or grab a prebuilt static binary (Linux x86_64/arm64 & macOS Apple Silicon/Intel):
curl -sSfL https://github.com/Voskan/pgloom/releases/latest/download/pgloom-x86_64-unknown-linux-musl.tar.gz | tar -xz
./pgloom-x86_64-unknown-linux-musl/pgloom demo

Voskan / pgloom

Deterministic race-condition testing for PostgreSQL-backed services. A wire-protocol proxy explores how transactions interleave and finds double spends, lost updates and deadlocks, with exact replay. No code changes, any language.

PgLoom

Deterministic race-condition testing for anything that talks to PostgreSQL.

PgLoom is a model checker that sits between your service and PostgreSQL as a wire-protocol proxy. It fires concurrent requests at your service, holds every database round trip at a gate, and systematically explores how the transactions can interleave. When an interleaving breaks an invariant (a double spend, a lost update, a negative balance, a deadlock), it prints the exact schedule, explains the root cause, and writes a file that replays the bug deterministically in milliseconds.

No SDK, no code changes, no sleeps: point the service's DATABASE_URL at the proxy. It works the same for Go, Rust, Python, TypeScript, Java or anything else that speaks the PostgreSQL v3 protocol.

$ pgloom demo

Plain-text transcript

✗ RACE CONDITION DETECTED  [demo-wallet-withdraw]
  Explored 3/50 schedules in 66ms (1-preemption trace)
  Violated Invariants:
    • no_negative_balance: Wallet balance must never drop below zero
      Observed DB state:

…

View on GitHub

If you maintain wallets, billing, booking, inventory, or coupon endpoints on PostgreSQL, give PgLoom a spin against one of your mutating routes. I'd love to hear what anomalies it surfaces in your stack!