In PostgreSQL, how should workers of a transactional-email queue claim rows with SKIP LOCKED, and how does that differ from NOWAIT?
answer
- row locks, never table locks
- skip versus raise an error
- lock inside the CTE, LIMIT the batch
- SQLSTATE 55P03
- a lease for crashed workers
basics
~20 sEach 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 sEach 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 linesCREATE 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
Recall that SKIP LOCKED passes over rows other transactions have locked, while NOWAIT raises an error instead of waiting for them.
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.
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.
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.