DEV Community

Cover image for Database Race Conditions Your Tests Miss
Arseniy Potapov
Arseniy Potapov

Posted on

Database Race Conditions Your Tests Miss

#postgres #mongodb #database #testing

I once spent three days on a production race that had passed tests, lint and type checks, and I've written it up twice: as the transactions that interlocked and as the counter that lost updates. Some of that code was AI-assisted. That's the pattern I rank P1 when I review agent-written diffs: correct single-threaded logic in a world that isn't single-threaded. The tests were green and the race still shipped, because none of them forced two workers onto the same row at the same moment.

That's the question I now ask of every green check on agent-written code: which wrong version would it reject? For shared database state, you answer it by controlling when two workers read and write the same records. Pick the ordering that breaks the rule your data must keep, such as "both increments to a counter survive." That rule gives you a final value to assert, plus any error the caller has to handle.

Below, I'll reproduce two different failures on PostgreSQL 17 and MongoDB 8. A deadlock aborts a transaction loudly. A stale write loses an update silently. Then I'll compare four guards: atomic updates, consistent lock ordering, cooperative advisory locks and version checks. The examples are constructed, small enough to paste into two terminals, and every one ran on scratch servers. Each ends as a regression test you can keep.

Two Workers Are Enough to Expose the Gap

You don't need production traffic to make two operations overlap. Two workers touching one record are enough, and a small schedule lets you follow each step.

Say worker A processes records 41 and 42, while worker B processes 42 and 43. The jobs have different IDs, but both touch record 42. If a test runs A to completion before starting B, B sees A's finished changes. That test never asks what happens when B changes 42 between A's read and its dependent write.

Sequence diagram: Worker A reads record 42 and pauses. Worker B changes the value and gets a write acknowledgement. A resumes and writes from the old value.

Time runs downward. The diagram shows operation order, not measured durations.

At A's final arrow, its calculation depends on a value B has already changed. Whether the database accepts that write depends on the operation and its guards. Keep that read/write boundary in mind for the MongoDB section.

So check both the record IDs and the operation order. A test that gives every worker separate fixtures avoids the overlap entirely. A test with shared fixtures can still miss it if A finishes before B reaches the record. Starting two jobs at once leaves the interleaving to the scheduler, so neither setup guarantees the sequence in the diagram.

The timeline narrows the investigation to one question: can another worker change the state this operation still relies on? PostgreSQL adds a second failure to tell apart, where two transactions each hold a lock the other needs.

PostgreSQL: Follow the Lock Cycle

A deadlock starts with each transaction holding something the other needs. Here, two workers each increment two counters, but visit the rows in opposite order. Both operations must finish for each counter to reach 2. The SQL uses database-side increments, so there's no stale application calculation to blame.

Open two independent psql connections to the same disposable database. Run this setup once, outside a transaction, before either worker starts. I ran everything here on PostgreSQL 17.11 at READ COMMITTED.

CREATE TABLE race_counters (id integer PRIMARY KEY, n integer NOT NULL);
INSERT INTO race_counters VALUES (1, 0), (2, 0);
Enter fullscreen mode Exit fullscreen mode

Run the numbered groups in order, switching terminals where indicated. Submit A's second update, then switch to B while A waits. Each transaction holds its first row lock until it ends.

-- 1. Session A
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE race_counters SET n = n + 1 WHERE id = 1;

-- 2. Session B
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE race_counters SET n = n + 1 WHERE id = 2;

-- 3. Session A: waits for B
UPDATE race_counters SET n = n + 1 WHERE id = 2;

-- 4. Session B: closes the cycle
UPDATE race_counters SET n = n + 1 WHERE id = 1;
Enter fullscreen mode Exit fullscreen mode

A now needs row 2, held by B, and B needs row 1, held by A. Neither can finish without the other releasing a lock. After deadlock_timeout (one second by default), PostgreSQL detects the cycle and aborts one transaction. Don't depend on which one it picks. PostgreSQL's deadlock documentation describes this and recommends a consistent acquisition order.

In my run, A was the victim:

ERROR:  deadlock detected
DETAIL:  Process 872 waits for ShareLock on transaction 741; blocked by process 873.
Process 873 waits for ShareLock on transaction 740; blocked by process 872.
Enter fullscreen mode Exit fullscreen mode

Run ROLLBACK; in the terminal reporting deadlock detected, and COMMIT; in the survivor. SELECT * FROM race_counters ORDER BY id; then shows 1 in both counters. The victim's first increment disappeared with its transaction. That's an explicit failure the caller must recover from, unlike two successful writes silently replacing each other's result.

Now change the acquisition order. With both transactions closed, reset with UPDATE race_counters SET n = 0;. Both workers use this same sequence:

BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE race_counters SET n = n + 1 WHERE id = 1;
UPDATE race_counters SET n = n + 1 WHERE id = 2;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Pause A after its first update and start B. B waits on row 1 before it holds row 2, which leaves A free to finish. Complete A, then B, and both counters reach 2. That removes the cycle at the cost of waiting where the workers overlap. Apply the order across every write path that takes part, including any extra rows those paths touch.

Sorting IDs in application memory only helps if the database acquires the locks in that order. An UPDATE ... WHERE id IN (1, 2) doesn't promise to visit rows in the listed order.

Keep a bounded retry path for SQLSTATE 40P01 (deadlock_detected). Roll back and start a fresh transaction that reruns its reads, decisions and writes. The aborted transaction can't continue, and rerunning only its last statement in a new transaction drops the earlier work. PostgreSQL's retry guidance makes that whole-transaction boundary explicit and separates deadlocks from serialization failures (40001). Cap the attempts, add backoff with jitter and surface exhaustion to the caller. When I replayed the victim's whole transaction after the survivor committed, both counters reached 2. Repeated failures still mean you should look at the lock cycle.

Choose the Resource Your Lock Protects

A batch ID is a tempting advisory-lock key. But batches 101 and 102 can both modify tenant 42's records, and different keys let both workers proceed. Choose the resource that owns the invariant. If tenant 42 may hold at most two reservations, both workers must coordinate on tenant 42.

PostgreSQL's advisory locks are cooperative. Every writer that affects the invariant must take the agreed key before reading or changing the protected state, maintenance scripts included. An advisory lock doesn't lock a table, and it doesn't stop a writer that ignores the convention.

I use the key (7, 42): application namespace 7, tenant 42. The two-integer lock function lets you reserve a namespace for this operation. Run the setup once in a scratch database, then the transaction on one connection:

CREATE TABLE reservations (
    tenant_id integer NOT NULL,
    booking_id integer PRIMARY KEY
);
INSERT INTO reservations VALUES (42, 1);

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT pg_advisory_xact_lock(7, 42);
INSERT INTO reservations (tenant_id, booking_id)
SELECT 42, 2
WHERE (SELECT count(*) FROM reservations WHERE tenant_id = 42) < 2;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Keep BEGIN, the lock, the capacity check and the insert in the same transaction on the same connection. pg_advisory_xact_lock releases at commit or rollback. In autocommit, a standalone lock call ends its protection before the next statement. A session-level pg_advisory_lock lives longer and survives rollback, which gets messy with pooled connections. The explicit READ COMMITTED gives the insert a fresh snapshot after any lock wait.

Pause worker A before its commit. On a second connection, worker B runs the same transaction with booking ID 3. B waits on (7, 42). After A commits, B sees two reservations and inserts nothing. Commit B to finish this case.

With both transactions closed, reset with TRUNCATE reservations; INSERT INTO reservations VALUES (42, 1);. Repeat A's transaction and pause before commit. Give B the key (7, 43) and booking ID 3, while its insert still targets tenant 42. B no longer waits. Let both inserts finish, then commit both transactions, and the count reaches three.

Your operation Guard to consider Contract
Increment one counter Atomic SQL update Calculate from the stored value
Change several existing rows Row locks in consistent order All paths follow that order
Enforce a tenant-wide rule Shared advisory key Every relevant writer participates
Claim any available job FOR UPDATE SKIP LOCKED Busy jobs may wait for a later pass

A tenant-wide key serializes participating work for that tenant. Keep the protected transaction short. A global key would make unrelated tenants wait too.

Use SKIP LOCKED when claiming any other available queue item satisfies the request. PostgreSQL explicitly allows an inconsistent view there. If your batch must update every required record, silently skipping busy rows changes the result you promised. It doesn't complete that batch safely.

MongoDB: An Atomic Write Can Still Replace Stale Data

MongoDB makes each single-document write atomic. A separate read, calculation and write still leaves room for another worker to change the document between those steps. MongoDB's atomicity documentation describes exactly this overwrite when both updates filter only on _id.

Two workers each want to add one to a counter that starts at 10. Both read 10, both calculate 11, and both successfully store 11. The missing increment never produces a database error.

You need a disposable MongoDB server and the mongosh shell. I ran these on MongoDB 8.0.16 with mongosh 2.5.9. Open mongosh --nodb and paste the blocks in order into the same shell. Set uri to your scratch server's address (27118 is just my test port, not MongoDB's default). A and B use independent connections, and the script completes both reads before either write. The reset replaces only this demo's document in race_demo.counters.

const uri = "mongodb://127.0.0.1:27118"
const A = new Mongo(uri).getDB("race_demo").counters
const B = new Mongo(uri).getDB("race_demo").counters
const reset = () =>
  A.replaceOne(
    { _id: "demo" },
    { _id: "demo", count: 10, version: 0 },
    { upsert: true },
  )
reset()
let a = A.findOne({ _id: "demo" })
let b = B.findOne({ _id: "demo" })
A.updateOne({ _id: a._id }, { $set: { count: a.count + 1 } })
B.updateOne({ _id: b._id }, { $set: { count: b.count + 1 } })
A.findOne({ _id: "demo" }) // count: 11
Enter fullscreen mode Exit fullscreen mode

Both updates report matchedCount: 1, so nothing looks wrong. Each $set replaces a field with a value the application computed. Replacing the whole document from a stale snapshot can overwrite other fields too. For this counter, express the increment directly with $inc, so MongoDB applies it to the stored value:

reset()
A.updateOne({ _id: "demo" }, { $inc: { count: 1, version: 1 } })
B.updateOne({ _id: "demo" }, { $inc: { count: 1, version: 1 } })
A.findOne({ _id: "demo" }) // count: 12, version: 2
Enter fullscreen mode Exit fullscreen mode

When the calculation has to happen in your application from fields it read earlier, use a version check to reject stale input. Put the snapshot's version in the filter, then increment that version in the same write. I kept the counter arithmetic so the difference stays visible without extra business logic:

reset()
a = A.findOne({ _id: "demo" })
b = B.findOne({ _id: "demo" })
const save = (collection, snapshot) =>
  collection.updateOne(
    { _id: snapshot._id, version: snapshot.version },
    { $set: { count: snapshot.count + 1 }, $inc: { version: 1 } },
  )
save(A, a).matchedCount // 1
save(B, b).matchedCount // 0: stale version rejected
Enter fullscreen mode Exit fullscreen mode

The first write moves the version to 1. The second write matches nothing and changes nothing. Check matchedCount yourself, because a zero match doesn't throw a concurrency exception. With this filter, zero can also mean the document was deleted.

On a version conflict, reread the document and recompute from the fresh state. When I reread B's snapshot and saved again, it matched and the count reached 12 at version 2. For an automatic retry, cap the attempts (three is a reasonable start), then return a conflict if contention continues. An edit that needs a user's judgment should surface the conflict instead. Retrying the same stale value with a newer version would overwrite the change you just detected.

Every writer that changes fields used by the calculation must advance version, maintenance scripts included. Keep upsert disabled for the guarded write. A missing match is a conflict to handle.

The invariant here covers one document. A version check on this counter can't protect a calculation whose inputs also live in other documents.

Make the Interleaving a Regression Test

Automate the terminal sequences with two independent connections and a shared committed fixture. Record the server version and transaction settings. Signal completed reads and lock acquisitions before releasing the next operation. Confirm blocked PostgreSQL requests from an observer connection with pg_stat_activity or pg_blocking_pids. A sleep can't prove that wait.

These schedules make good test specifications:

  1. PostgreSQL deadlock. Reset both counters to 0. A locks row 1, B locks row 2, then dispatch both second-lock requests without awaiting either. Expect one transaction to report 40P01, the survivor to commit, and both counters at 1.
  2. Ordered locks. Reset to 0. A locks row 1, B requests row 1. Confirm B waits, then let A finish and commit. Expect B to complete afterward with both counters at 2.
  3. Advisory key. Reset to one reservation. A inserts under (7, 42), and B requests that key before checking capacity. Expect B to wait and the count to stay at 2. Repeat with B on (7, 43): both insert, and the count reaches 3.
  4. MongoDB stale write. Reset to count 10, version 0. Both read, A writes 11, then B writes its cached 11 after A's acknowledgement. Expect both writes to match and a final count of 11.
  5. Atomic increment. Reset to count 10, version 0. Each connection runs the $inc update. Expect count 12 and version 2.
  6. Version guard. Reset to count 10, version 0. Both read, A saves, B tries its stale snapshot. Expect match counts of 1 then 0, count 11 and version 1.

Add recovery assertions to the failure cases. For the deadlock, run the whole-transaction retry after the survivor commits. Both counters must reach 2 no matter which worker failed. For MongoDB, exercise reread and recompute if that's your contract. The result must be count 12 at version 2, and a swallowed conflict must fail the test.

Give every wait a deadline and clean up both connections on failure. With ordered locks, avoid a barrier that needs both workers to hold row 1, since the guard prevents exactly that. Keep the reproduced case next to your legacy-code regression tests and run it in CI against a disposable database. Passing it covers this schedule, not every possible interleaving.

Test One Write at a Time

Choose one write in your application: a counter increment, a balance change, or a document replaced from an earlier read. Give it a specific correctness rule before choosing a guard. For a counter, the rule might be "two accepted increments both appear in the final value."

Then turn that write into a regression test:

  • Inventory the writers. Name the request handlers, background jobs and maintenance scripts that can change the same records.
  • State the invariant. Write down the expected final state and which conflicts or aborts the caller must handle.
  • Force the overlap. Use two independent sessions and a controlled schedule that exposes the unguarded failure.
  • Verify the guard. Repeat the test after the change. Assert the final state and the expected errors, including the outcome after any required retry.

This covers PostgreSQL and MongoDB operations only. Redis, Elasticsearch, Celery delivery and external side effects bring their own interleavings, and these examples don't prove anything about them.

If an agent wrote the code, this test is the one its green suite most likely lacks. Keep the failing schedule next to the guard, so a future edit has a concrete behavior to preserve.

More on this on potapov.dev:

Top comments (0)