PostgreSQL

PostgreSQL Advisory Locks vs SELECT FOR UPDATE for Job Queues

5 min read by DebuggedIt

Quick answer

For a job queue in PostgreSQL, SELECT FOR UPDATE SKIP LOCKED inside a transaction is usually the simplest and safest way to let many workers claim jobs without...

For a job queue in PostgreSQL, SELECT FOR UPDATE SKIP LOCKED inside a transaction is usually the simplest and safest way to let many workers claim jobs without blocking each other. Advisory locks are better for coordinating work that is not tied to a single row, such as ensuring only one instance runs a scheduled task. The lifetime of the lock and what happens on a crash are the main differences.

How Does SELECT FOR UPDATE SKIP LOCKED Work?

A normal SELECT FOR UPDATE locks the rows it returns until the transaction ends, and other transactions that touch those rows wait. Adding SKIP LOCKED tells PostgreSQL to ignore rows that are already locked by someone else. Each worker therefore receives a different job, and none of them waits.

BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- process the job, then:
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

The lock is tied to the transaction. If the worker crashes, the connection closes, the transaction rolls back, and the row becomes available again automatically. That crash safety is a major advantage, and it means you rarely need a separate cleanup process for abandoned locks.

What Are the Drawbacks of Holding a Transaction Open?

If the job takes a long time, the transaction stays open for the whole duration. Long transactions hold back vacuum cleanup, keep a connection busy, and can cause table bloat. For slow jobs, this approach does not scale well.

A common alternative is the claim and release pattern. In a short transaction, update the row to a running state with a timestamp or worker identifier, commit immediately, process the job outside the transaction, and then mark it done. This frees the database connection during the work, but you must handle crashes yourself, for example by reclaiming jobs that have been running longer than a timeout.

  • Use heartbeats or timeouts to detect abandoned running jobs.
  • Make job processing idempotent, since a reclaimed job may run twice.
  • Add retry counts and a dead-letter state for jobs that keep failing.
  • Index the columns used to find pending jobs so the claim query stays fast.

How Do Advisory Locks Differ?

Advisory locks are application-defined locks identified by a number. PostgreSQL does not tie them to any table or row. It simply tracks that someone holds lock number N, and its meaning is entirely up to your code.

They come in two flavors. Session-level locks last until released or until the session ends. Transaction-level locks release automatically at commit or rollback. Both are fast and do not create row versions or table bloat.

-- try to take a lock without waiting
SELECT pg_try_advisory_lock(42);

-- transaction-scoped variant
SELECT pg_try_advisory_xact_lock(42);

Session-level locks require care with connection pooling. In transaction pooling mode, your next statement may run on a different backend, so a session lock can be left behind or released from the wrong place. Prefer transaction-level locks in pooled environments, and verify the behavior of your pooler.

Which One Should You Choose?

Use row locks with SKIP LOCKED when each unit of work corresponds to a row, because the lock, the data, and the job state live together and recover together. Use advisory locks when you need mutual exclusion that is not a row, such as a singleton cron-style task, a migration guard, or serialization of work for one customer.

  • Queue with many workers and short jobs: FOR UPDATE SKIP LOCKED in a transaction.
  • Queue with long jobs: claim and release with a status column and timeouts.
  • Only one instance should run a task: advisory lock, preferably transaction-scoped.
  • Serialize operations per entity: advisory lock keyed by a hash of the entity id.

Advisory lock keys share a single numeric space, so collisions between unrelated features are possible. Use a consistent key scheme and document it. Also consider whether a dedicated message queue fits better when throughput or features outgrow what a database table can reasonably provide.

Frequently Asked Questions

Does SKIP LOCKED guarantee ordering?

No. Rows skipped by one worker are processed by others, so strict ordering is not preserved across concurrent workers.

What happens to advisory locks when a connection drops?

Session-level locks are released when the session ends. Transaction-level locks are released at the end of the transaction.

Can advisory locks cause deadlocks?

Yes, if several locks are taken in inconsistent orders. Use a try variant or a fixed ordering to avoid them.

Is a database table a good job queue?

For moderate volumes, yes. At very high throughput, consider a dedicated queue system.

Summary

Row locks with SKIP LOCKED suit queues where jobs are rows, while advisory locks coordinate work that is not row-shaped. Keep transactions short, prefer transaction-scoped locks with pooling, and read the PostgreSQL documentation for lock semantics in your version.

You might also like