patterntypescriptTip
Deterministic "during" concurrency tests in Postgres: stall the operation on a row lock, poll pg_stat_activity, then race it
Viewed 0 times
pg_stat_activity wait_event_type Locksql.reserveFOR UPDATESKIP LOCKEDrace condition testintegration test
Problem
Integration tests for a background worker (queue drain, full recompute under a row lock) need to prove behaviour when a second operation arrives during the first: two drains in parallel, or an edit that lands while a recompute holds the parent row. Plain Promise.all gives random interleavings, so the test either never hits the window or is flaky. Injecting sleeps or test hooks into production code is ugly.
Solution
Use a reserved connection to hold a lock that the operation under test must take at a known point, so it stalls there deterministically. Example:
const r = await sql.reserve(); await r\begin\; await r\select id from records where id = ${x} for update\` makes a later for key share inside the worker's apply step wait while the worker already holds its parent row lock. Start the worker without awaiting it, then poll select count(*) from pg_stat_activity where wait_event_type = 'Lock' and query like $1 with a distinctive fragment of the worker's SQL until it is blocked. Fire the concurrent operation (it blocks on the parent row) and poll again until it is blocked too. Then rollback or commit the reserved transaction and await both promises. Assert only the final state (rows, history, invariants), never which worker did the work. Always rollback and release() the reserved connection in finally`.Why
The lock wait is observable from outside through pg_stat_activity, so the test knows each party reached its expected point without timing guesses. Because the stall uses real locks, it also exercises the actual lock order (FOR NO KEY UPDATE vs FOR KEY SHARE, SKIP LOCKED) instead of a simulation.
Gotchas
- The poll query passes the fragment as a parameter, so it does not match its own text in pg_stat_activity.query.
- Count the pool: reserved connection + each blocked operation + the poller must stay under the pool max, or the test deadlocks waiting for a connection.
- Do concurrent writes sequentially while others are blocked to avoid exhausting the pool.
- Lock the rows the worker reads with FOR KEY SHARE, not ones it reads with a plain SELECT: plain reads never block.
Revisions (0)
No revisions yet.