Recipe

Pessimistic Locking Patterns

When two workers race for the same row, optimism loses money. Pessimistic locking takes the contended row out of circulation for the duration of the critical section, trading throughput for correctness. This recipe covers the three patterns Meridian uses in production: row locks, advisory locks, and lease-based locks.

1. SELECT FOR UPDATE on hot rows

The simplest pessimistic primitive: open a transaction, lock the row, mutate, commit. The lock is released when the transaction ends. Use this when the critical section is short (under 100ms) and you can tolerate readers blocking on the lock.

BEGIN;
SELECT balance
  FROM accounts
 WHERE id = $1
   FOR UPDATE;

UPDATE accounts
   SET balance = balance - $2
 WHERE id = $1;
COMMIT;

2. Advisory locks for cross-row coordination

When the resource you are protecting is not a single row (a queue partition, a cron singleton, a tenant migration), Postgres advisory locks give you a named mutex scoped to either the session or the transaction. They never block readers and survive across statements within the same connection.

Hash your logical key into a bigint and call pg_advisory_xact_lock(key). The lock auto-releases on commit or rollback.

3. Lease locks for long-running work

For jobs that span minutes or hours (large exports, model fine-tunes, file ingestion), holding a database transaction open is hostile to your connection pool. Instead, write a row with a leased_until timestamp, renew it from the worker every 30 seconds, and let stale leases expire so crashed workers do not deadlock the queue forever. This is the pattern Meridian uses for its async batch endpoints.