liveBook · Manning

FOR UPDATE clause

As explained in Just Use Postgres!

A FOR UPDATE clause locks selected rows so that concurrent transactions cannot independently modify or process them until the lock-holding transaction ends.

FOR UPDATE places row-level locks on selected queue messages, allowing only one concurrent consumer to work with each returned row. Locks remain until the transaction commits or rolls back. section 11.2

Concurrent queue consumers

A dequeue function selects new messages in creation order, locks them, and updates their status: WITH new_messages AS (SELECT id FROM mq.queue WHERE status = 'new' ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT messages_cnt) UPDATE mq.queue SET status = 'processing' FROM new_messages WHERE mq.queue.id = new_messages.id. SKIP LOCKED lets other consumers bypass rows already locked, so processing continues in parallel. section 11.2

Within BEGIN...COMMIT, locks survive alongside business logic and release at COMMIT or rollback. Without an explicit transaction, the function’s implicit transaction releases them when execution finishes. section 11.2

How Just Use Postgres! covers it

How deep it goes
Explains row-level locks, transaction boundaries, SKIP LOCKED behavior, and a complete dequeue implementation.
Examples
Uses a PostgreSQL queue table, a dequeue function, concurrent consumers, and explicit transaction blocks.
What it assumes
Assumes familiarity with PostgreSQL functions, common table expressions, and explicit transactions.
What you can do after
Operate concurrent queue consumers that reserve messages without blocking on rows already being processed.
How it connects
Ties row-level locks to transactions, common table expressions, UPDATE statements, SKIP LOCKED, and message queues.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Also explained in

Compare how 2 books explain FOR UPDATE clause →

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.