skip to content

Lock Modes & Advisory Locks

PostgreSQL has eight table lock modes, four row lock strengths and application-defined advisory locks. Interviewers probe job queues with SKIP LOCKED, foreign-key locking and deadlock handling.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL, how should workers of a transactional-email queue claim rows with SKIP LOCKED, and how does that differ from NOWAIT?

level: middleimportance: must knowfreq 66%

answer

  1. row locks, never table locks
  2. skip versus raise an error
  3. lock inside the CTE, LIMIT the batch
  4. SQLSTATE 55P03
  5. a lease for crashed workers

basics

~20 s

Each worker runs a SELECT ... FOR NO KEY UPDATE SKIP LOCKED LIMIT n that locks only rows no other transaction holds, so workers get disjoint batches without waiting. NOWAIT instead raises SQLSTATE 55P03 the moment a selected row is locked.

solid answer

~50 s

Each worker runs a short transaction that selects a batch of due rows with `FOR NO KEY UPDATE SKIP LOCKED` and a `LIMIT`, marks them `sending` with a lease timestamp, and commits: a CTE that locks the ids plus an `UPDATE ... FROM` with `RETURNING`. `SKIP LOCKED` makes PostgreSQL pass over any row another transaction has row-locked, so ten workers get ten disjoint batches instead of queueing behind the same oldest rows. `NOWAIT` instead raises SQLSTATE `55P03` (`lock_not_available`) as soon as one selected row is locked, which in a queue just means constant errors. Both options govern only row locks; the `ROW SHARE` table lock is still waited for in the ordinary way. Because the claim commits before the email is sent, a `locked_until` lease lets a reaper return rows a crashed worker left in `sending`. PostgreSQL 18's `log_lock_failures` can log `NOWAIT` failures.

code

sql · 15 lines
sql
CREATE TABLE email_outbox (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  recipient    text        NOT NULL,
  template     text        NOT NULL,
  status       text        NOT NULL DEFAULT 'pending',
  run_after    timestamptz NOT NULL DEFAULT now(),
  locked_until timestamptz
);
CREATE INDEX email_outbox_due ON email_outbox (run_after)
  WHERE status = 'pending';

-- reaper: hand back rows whose worker died mid-send
UPDATE email_outbox
SET status = 'pending', locked_until = NULL
WHERE status = 'sending' AND locked_until < now();

go deeper

for a junior

Recall that SKIP LOCKED passes over rows other transactions have locked, while NOWAIT raises an error instead of waiting for them.

for a middle

Explain the CTE-plus-UPDATE claim, why ORDER BY, LIMIT and the locking clause sit inside the CTE, and why the ROW SHARE table lock is still waited for.

for a senior

Show the crash story: claims committed before the send need a lease and a reaper, and FOR NO KEY UPDATE avoids skipping rows pinned by foreign-key checks.

for a principal

Weigh a table-backed queue against a dedicated broker: enqueueing in the same transaction as the business write versus dead tuples, vacuum load and throughput ceilings.

## The problem: many workers, one table A **transactional-email queue** keeps pending messages in a table such as `email_outbox`. Several worker processes repeatedly take a batch of due rows, hand them to the mail provider and mark them sent. Two things must hold: no email is claimed by two workers, and workers must not queue up behind each other. A plain `SELECT ... FOR UPDATE ... LIMIT 20` fails the second requirement: every worker scans the same oldest rows, the first one locks them, and the rest **wait** on those row locks. PostgreSQL's locking clause takes two modifiers that change what happens when a selected row is already locked. ## The claim statement ```sql WITH claim AS ( SELECT id FROM email_outbox WHERE status = 'pending' AND run_after <= now() ORDER BY run_after LIMIT 20 FOR NO KEY UPDATE SKIP LOCKED ) UPDATE email_outbox e SET status = 'sending', locked_until = now() + interval '5 minutes' FROM claim WHERE e.id = claim.id RETURNING e.id, e.recipient, e.template; ``` The locking clause sits **inside** the CTE: a locking clause on the main query does not reach into a `WITH` query, so the CTE must carry its own. With `LIMIT`, locking stops once enough rows have been returned, so each worker locks only its own batch. ## What SKIP LOCKED and NOWAIT do 1. The executor finds candidate rows in `run_after` order. 2. For each one it tries to take the requested **row lock**, here `FOR NO KEY UPDATE`. 3. If another transaction holds a conflicting row lock: - with no modifier, the statement **waits** for that transaction to end, then, under `READ COMMITTED`, re-checks the row; - with `NOWAIT`, the statement **fails** with SQLSTATE `55P03` (`lock_not_available`) and the message "could not obtain lock on row in relation"; - with `SKIP LOCKED`, the row is **left out** and the scan moves on. 4. The `UPDATE` marks the claimed rows and the statement commits, releasing the row locks. Both modifiers apply **only to row locks**. The statement still takes the `ROW SHARE` table lock in the ordinary way, so it waits behind anyone holding `EXCLUSIVE` or `ACCESS EXCLUSIVE` on `email_outbox`. `LOCK TABLE ... NOWAIT` is the table-level tool if that wait is unacceptable. ## Waiting, NOWAIT and SKIP LOCKED compared | Clause | When a selected row is locked | Fits | |---|---|---| | `FOR NO KEY UPDATE` | waits for the holder, then re-checks | updating one known row | | `FOR NO KEY UPDATE NOWAIT` | raises `55P03`; the transaction is aborted | interactive paths that must not hang | | `FOR NO KEY UPDATE SKIP LOCKED` | leaves the row out of the result | queue claims with many consumers | The manual's own warning applies: skipping locked rows gives an **inconsistent view** of the data, so `SKIP LOCKED` is for queue-like tables, never for reports or backlog counts. ## Choosing the lock strength `FOR UPDATE` conflicts with `FOR KEY SHARE`, the lock PostgreSQL's foreign-key checks take on a referenced row. If a webhook is inserting `email_events` rows that reference an outbox row, a `FOR UPDATE SKIP LOCKED` claim skips that row for as long as the inserting transaction is open, although nobody is sending it. `FOR NO KEY UPDATE`, the strength an `UPDATE` of non-key columns takes anyway, does not conflict with `FOR KEY SHARE`, so it is the better default for a claim that only changes `status` and `locked_until`. ## Crash safety and other pitfalls - **Lock-holding claims** keep the transaction open while sending. If the worker dies, PostgreSQL notices the dropped connection, aborts the transaction and the rows become claimable again. The price is a transaction held open across a call to the mail provider, a cost the long-transactions leaf covers. - **Lease claims**, like the statement above, commit at once, so after commit **no lock remains**. A worker that crashes mid-send leaves rows in `sending`; a reaper must reset rows whose `locked_until` has passed, and sends must be idempotent because the provider may already have accepted one. - **Index the hot predicate**: a partial index on `run_after` with `WHERE status = 'pending'` keeps each claim from scanning sent history. - **Every claim is an UPDATE**, so a busy queue table produces dead tuples steadily and depends on autovacuum keeping up. - **Logging NOWAIT failures**: PostgreSQL 18 adds `log_lock_failures`, off by default and settable by superusers, which writes a detailed log message when a `SELECT ... NOWAIT` cannot get its lock. Rows passed over by `SKIP LOCKED` are not failures and are not reported. - **Waking workers** instead of polling is `LISTEN`/`NOTIFY`, another leaf's subject.

  • Why might a FOR UPDATE SKIP LOCKED claim skip email rows that no worker is sending?
    `FOR UPDATE` conflicts with `FOR KEY SHARE`, which PostgreSQL's foreign-key checks take on the referenced row. While a transaction inserting an `email_events` row that references outbox row 101 is open, row 101 counts as locked, and the claim silently skips it. `FOR NO KEY UPDATE` does not conflict with `FOR KEY SHARE`, so a claim that only changes non-key columns should use it.
  • Does SKIP LOCKED guarantee that a PostgreSQL queue worker never blocks?
    No. It only changes how row locks are handled. The statement still takes `ROW SHARE` on `email_outbox` in the ordinary way, so while a migration holds `ACCESS EXCLUSIVE` on the table the claim waits like any other query. `LOCK TABLE ... NOWAIT` handles the table level; bounding waits behind DDL with `lock_timeout` is the schema-changes leaf's subject.
  • How can you see NOWAIT lock failures in the PostgreSQL 18 server log?
    Turn on `log_lock_failures`, a boolean added in PostgreSQL 18, off by default and settable only by superusers or roles granted `SET` on it. It writes a detailed log message whenever a lock acquisition fails; currently only `SELECT ... NOWAIT` failures are covered. Rows skipped by `SKIP LOCKED` never fail, so they produce no message.

  • SKIP LOCKED also skips tables another session has locked
  • NOWAIT returns zero rows instead of failing when a row is locked
  • A plain FOR UPDATE with LIMIT spreads concurrent workers across different rows
  • A row committed as sending is released automatically if its worker crashes
  • SKIP LOCKED gives a consistent view that is fine for counting the backlog

Assumes PostgreSQL 18; log_lock_failures, which logs SELECT ... NOWAIT lock failures, is new in 18.

open as a page

In PostgreSQL, which table-level lock do SELECT, SELECT ... FOR UPDATE and UPDATE each take, and which mode alone blocks a plain SELECT?

level: juniorimportance: should knowfreq 52%

basics

~20 s

A plain SELECT takes ACCESS SHARE, SELECT ... FOR UPDATE takes ROW SHARE and UPDATE takes ROW EXCLUSIVE, and none of the three conflict. Only ACCESS EXCLUSIVE, taken by DROP TABLE, TRUNCATE and many ALTER TABLE forms, blocks a plain SELECT.

open as a page

In PostgreSQL, what are the four row-lock strengths, and why do foreign-key checks take FOR KEY SHARE rather than FOR SHARE?

level: middleimportance: should knowfreq 44%

basics

~20 s

PostgreSQL's row locks, strongest first, are FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE. A foreign-key check only needs the referenced key to survive, so FOR KEY SHARE lets non-key updates of the parent row proceed.

open as a page

In PostgreSQL, how do session-level and transaction-level advisory locks differ, and why does a session-level one leak behind PgBouncer's transaction pooling?

level: seniorimportance: should knowfreq 42%

basics

~20 s

A session-level advisory lock is held until pg_advisory_unlock or disconnect, even across ROLLBACK; a transaction-level one ends with its transaction. Behind transaction pooling the unlock can reach another server connection, so the lock stays held.

open as a page

A PostgreSQL email worker fails with SQLSTATE 40P01 after a one-second stall; what did deadlock_timeout do, and how do the error and pg_locks reveal the cycle?

level: seniorimportance: should knowfreq 38%

basics

~20 s

A lock waiter sleeps for deadlock_timeout (default 1s) before searching for a wait cycle; on finding one, PostgreSQL aborts one transaction with 40P01. Its DETAIL lists who waits on which transaction, and pg_locks shows row waits as ungranted transactionid ShareLocks.

open as a page