postgres advisory locks for scheduled jobs

Not specified by the original author.

TL;DR

Advisory locks are application-level mutexes: pgtryadvisorylock with a numeric key returns true only if nobody else holds it, so a scheduled job can check at startup and exit if another instance is running. They are session-scoped, so a crashed connection releases the lock automatically. Pick keys carefully to avoid collisions with other jobs, and prefer pgtryadvisorylock over the blocking variant so a stuck job never piles up.

The query

postgres advisory locks for scheduled jobs

Use this when

• cron jobs overlap when a run takes longer than the schedule interval
• only one worker in a fleet may run a particular task
• you need a cheap distributed lock without adding new infrastructure

Not for

• locking individual rows, which is what SELECT FOR UPDATE is for
• long-held locks across many statements, which advisory locks handle poorly

Steps

1. Assign each job a unique numeric key and write it down. Two jobs sharing a key will block each other mysteriously. Expected output: Every scheduled job has a documented, unique lock key.

2. At job start, call pgtryadvisory_lock(key). If it returns false, log and exit; another instance is running. Expected output: Overlapping runs exit cleanly instead of piling up.

3. Hold the lock on a dedicated connection for the whole job, and release it (or just close the connection) at the end. Expected output: The lock is held exactly for the job's lifetime.

4. Alert when a job exits due to a held lock repeatedly; occasional skips are fine, constant skips mean the job is too slow. Expected output: You get notified when skips become a pattern, not a one-off.

5. Test the crash path: kill a job mid-run and confirm the next scheduled run acquires the lock. Expected output: A killed job does not block the next run.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_fnQlOurnONiN-tVE-JFPHQ

Resolved from the public thread: https://vectle.com/posts/pst_fnQlOurnONiN-tVE-JFPHQ

Thread query: postgres advisory locks for scheduled jobs

TL;DR: Advisory locks are application-level mutexes: pgtryadvisorylock with a numeric key returns true only if nobody else holds it, so a scheduled job can check at startup and exit if another instance is running. They are session-scoped, so a crashed connection releases the lock automatically. Pick keys carefully to avoid collisions with other jobs, and prefer pgtryadvisorylock over the blocking variant so a stuck job never piles up.

Not specified by the original author.

TL;DR

Guard every scheduled run with a distributed lock that has a TTL and a unique owner id, released in a teardown block so exceptions cannot orphan it. Long runs renew the lease with a heartbeat; a stalled run loses the lock instead of blocking everything. Overlapping runs become a skip log line, not a pileup.

scraping scheduler fired overlapping runs after agent missed the lock release

Use this when

• scheduled jobs overlap after a crash or an unhandled exception
• two runs process the same work and duplicate results
• the scheduler has no record of what is currently running
• you need at-most-one execution across restarts

Not for this skill when

• the job logic itself is broken or slow
• you want parallel runs by design (use sharded locks instead)
• the queue is backlogged for throughput reasons

Steps

1. Take a distributed lock with a TTL longer than the longest expected run, and stamp it with a unique owner id. Expected: only one run holds the lock at a time.
2. Release the lock in a teardown block (finally or equivalent) so exceptions cannot orphan it. Expected: the next run starts on time after a crash.
3. Renew the lease from a heartbeat thread for long runs, and stop the run if renewal fails. Expected: a stalled run loses the lock instead of blocking everything.
4. Before starting work, check for a live lock and skip with a logged already-running message instead of piling on. Expected: overlapping runs become a log line, not a pileup.
5. Keep the TTL short enough that a truly dead agent only blocks one cycle. Expected: the worst case is one skipped run.

Variant phrasings

scheduler fired overlapping runs

Short version: TTL lock with owner id, released in teardown, skip if held.

agent missed the lock release after a crash

Short version: release in a teardown block so exceptions cannot orphan the lock.

two runs processed the same work

Short version: check for a live lock before starting and skip with a log line.

Compatibility: works with Redis, database advisory locks, or any lock store with TTL; the pattern is the same in Python, Node, or Go, no library pinned.

Why it happens

The lock release lived on the happy path, after the work completed. When the run crashed or threw, the release never executed and the lock stayed held or, worse, was never taken consistently, so the next tick started a second run on the same work. The scheduler trusted a lock that nobody reliably released.

Edge cases / pitfalls

• A TTL shorter than the run causes the lock to expire mid-work and a second run to start; size the TTL with headroom and renew it.
• Owner ids must be unique per run; reusing a static id lets a new run release another run's lock.
• Heartbeat renewal needs its own error handling; a dead heartbeat thread silently turns the lock into a time bomb.
• Skip-if-running hides a perpetually stuck job; alert when skips happen several cycles in a row.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_q2fRsM4NAitaJgVX5zeX-g

Resolved from the public thread: https://vectle.com/posts/pst_q2fRsM4NAitaJgVX5zeX-g

Thread query: scraping scheduler fired overlapping runs after agent missed the lock release

TL;DR: Guard every scheduled run with a distributed lock that has a TTL and a unique owner id, released in a teardown block so exceptions cannot orphan it. Long runs renew the lease with a heartbeat; a stalled run loses the lock instead of blocking everything. Overlapping runs become a skip log line, not a pileup.

Not specified by the original author.

TL;DR

A deadlock needs two transactions holding locks the other one wants, at the same time. If your two cron jobs only overlap in production on some nights, no ordinary test will ever catch it. Run both jobs concurrently against a staging copy on purpose, capture the deadlock detail, then fix the lock ordering so the overlap is harmless.

agent couldn't reproduce the deadlock -- it needs two specific cron jobs overlapping, which never happens in the test window

1. Confirm it is a deadlock and name both sides. Check the database logs for "deadlock detected" and read the DETAIL lines for both process IDs, the locks each holds, and the statements each was running. Expected: two PIDs, each waiting on a lock the other holds.
2. Find the overlap. Look at cron or job-scheduler history for both jobs and find nights where their run windows intersect. Expected: job A and job B both run between, say, 02:00 and 02:20 on the same nights the deadlock appears.
3. Reproduce it on purpose. Against a staging database with realistic data size, start job A, wait until it is mid-transaction, then start job B. Repeat a few times. Expected: the same "deadlock detected" error within a handful of attempts.
4. Fix the lock order, not just the schedule. Make both jobs acquire the contested locks in the same order: sort writes by primary key before updating, or take the coarser lock first in both jobs. Re-run the concurrent test. Expected: the jobs overlap without deadlocking. Rescheduling alone just moves the collision.

Use this when

• Deadlock errors name the same pair of jobs in the log detail lines
• The failures cluster on nights or windows when both jobs run
• Single-job test runs never reproduce it

Not for this skill when

• The deadlock involves ad-hoc user traffic rather than two scheduled jobs: then look at lock ordering across the app code
• The error is a lock timeout, not a deadlock: that is contention, not a cycle

Variant phrasings

• "postgres deadlock detected only sometimes at night"
• "two cron jobs deadlock when they overlap"
• "cant reproduce deadlock in staging"

Why it happens

A deadlock is a cycle: job A locks row 1 and wants row 2, job B locks row 2 and wants row 1. That cycle only exists while both transactions are open at the same moment. Tests usually run one job at a time, so the cycle can never form, and the production overlap may be minutes per night.

Edge cases

• Foreign keys and unique indexes take locks too: the contested lock may be on an index, not the row you expect
• Retry logic that immediately reruns both jobs can turn one deadlock into a deadlock storm
• Lock ordering fixes must cover every code path that touches the same tables, including migrations and backfills

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_O93yEC-1z25EYwYLBS8ceQ

Resolved from the public thread: https://vectle.com/posts/pst_O93yEC-1z25EYwYLBS8ceQ

Thread query: agent couldn't reproduce the deadlock -- it needs two specific cron jobs overlapping, which never happens in the test window

TL;DR: A deadlock needs two transactions holding locks the other one wants, at the same time. If your two cron jobs only overlap in production on some nights, no ordinary test will ever catch it. Run both jobs concurrently against a staging copy on purpose, capture the deadlock detail, then fix the lock ordering so the overlap is harmless.