Skip to content
Tyrone Software
Database

SKIP LOCKED: a job queue in your database, and its limits

Abstract illustration for the article SKIP LOCKED: a job queue in your database, and its limits

A background job table looks trivial until two workers grab the same row. The next thing people reach for is a broker. For many workloads, a single clause in the database is enough.

The problem it solves

With plain SELECT ... FOR UPDATE, the second worker blocks until the first one commits, and then it usually gets the same job's neighbour anyway. Workers spend their time waiting for each other on the first row of the queue. SKIP LOCKED tells the query to ignore rows that another transaction has locked and move on to the next one. PostgreSQL supports it since 9.5, and MySQL since 8.0.

The pattern (PostgreSQL syntax)

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

The worker runs this, processes the job, and updates the status to done or failed. Because the claim and the status change happen in one statement, a crash between them cannot leave a job half-claimed without a trace. Add a partial index on (id) WHERE status = 'queued' so the lookup stays cheap however many finished jobs the table accumulates.

Four steps showing two workers claiming different jobs with SKIP LOCKED and a sweeper for crashed workers
Without SKIP LOCKED, worker B would wait for worker A on the same row.

Details that need decisions

  • Crashed workers. A job marked running by a worker that died stays running forever. Store started_at (or a heartbeat) and have a sweeper put stale jobs back to queued after a timeout, which means jobs must be safe to run twice.
  • Ordering is best effort. Workers skip locked rows, so jobs do not finish in strict order. If order matters within one customer, you need something more, such as one queue per key.
  • Retries and backoff. Keep an attempts count and a run_at timestamp, and select only rows where run_at <= now(), so failed jobs wait before returning.
  • Keep transactions short. If the worker holds the transaction open while doing the slow work, the row stays locked and a connection is used up. Claim, commit, do the work outside the transaction, and record the result in a second short transaction.
  • Cleanup. Delete or archive completed jobs regularly, or the table becomes the dead-row problem from another post.

Where it stops working

A table used as a queue produces constant inserts, updates and deletes, and that load competes with your real data for I/O and vacuum. Very high job rates, fan-out to many consumers, delayed messages at scale, or the need for streaming and replay are the signs to move to a broker such as RabbitMQ, Redis Streams or Kafka. Below that threshold, having jobs in the same transaction as your data (insert the order and its email job atomically) is a real advantage a separate broker does not give you.

Waking workers instead of polling for them

The pattern above still needs something to decide when a worker checks the table again, and the simplest answer, a short sleep in a loop, either wastes cycles polling an empty queue or adds latency equal to the sleep interval before a new job is even noticed. PostgreSQL's LISTEN and NOTIFY remove that trade-off for a SKIP LOCKED queue specifically: a trigger on insert sends a lightweight notification, and idle workers block on LISTEN instead of polling, waking immediately when a job arrives and falling back to a longer poll interval only as a safety net in case a notification is ever missed (connection drops between the notify and a worker's listen do not queue the message for it).

CREATE OR REPLACE FUNCTION notify_new_job() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('new_job', NEW.id::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER jobs_notify AFTER INSERT ON jobs
FOR EACH ROW EXECUTE FUNCTION notify_new_job();

Because LISTEN needs its own persistent connection per listening process, and PgBouncer's transaction pooling mode does not support it (covered in the pooling article), a queue that combines SKIP LOCKED with LISTEN/NOTIFY needs a direct connection for the listening side, separate from the pooled connections the workers use to actually claim and run jobs.

Keep reading

Technologies

  • Python
  • PHP
  • Laravel
  • Django
  • PostgreSQL
  • MySQL
  • HTML5
  • CSS3
  • Bootstrap