CodePark/ questions / Locking, concurrency & deadlocks
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Locking, concurrency & deadlocks

Explain how this query lets many workers pull jobs from one PostgreSQL table safely. Why SKIP LOCKED, and what else would a production queue need?

UPDATE jobs
SET status = 'running', started_at = now()
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING *;

Each worker locks and claims a different job, so no two workers take the same job.

The subquery picks the oldest queued job and locks its row with FOR UPDATE. SKIP LOCKED tells another worker to skip rows that someone else has already locked, instead of waiting for them. So each worker claims a different job without blocking. Without SKIP LOCKED, all workers would wait in line for the same row and process jobs one at a time. The outer UPDATE marks the job as running in the same statement, so claiming and marking happen together.

A production queue also needs:

  • An index that matches the lookup, such as a partial index on created_at WHERE status = 'queued'.
  • Recovery from crashed workers. Either keep the transaction open while working, so a crash releases the lock. Or record started_at or a heartbeat (a regular "I am alive" update), and requeue jobs that run too long.
  • Retry tracking. Count attempts, and move jobs that fail again and again to a separate place.
  • Autovacuum that keeps up. The table has many updates and deletes, which leave many dead rows.
  • LISTEN/NOTIFY to tell workers about new jobs, so they poll less often.
  • FOR UPDATE locks the row of the claimed job.
  • SKIP LOCKED lets workers take different rows without waiting for each other.
  • Claiming the job and marking it as running happen in one statement.
  • An index should match the lookup for queued jobs.
  • Recover jobs from crashed workers, and track retries.