SELECT FOR UPDATE and Row-Locking Patterns in PostgreSQL

SELECT ... FOR UPDATE is the workhorse of correct concurrent writes in PostgreSQL. It locks the rows you read so nobody else can change them until you commit, which is exactly what read-modify-write logic needs. It also has variants and options that most developers never learn, and those variants are what let you build job queues, avoid blocking, and control what happens when a row is already locked. This article covers the whole family.

This is part 22 of the PostgreSQL Masterclass, following optimistic vs pessimistic locking.

The basic pattern

The core use is protecting a read-modify-write from the race we've seen throughout this module. Lock the row on read, so the value you read can't change before you write it:

BEGIN;
  SELECT stock FROM products WHERE id = 1 FOR UPDATE;  -- lock the row
  -- app checks stock > 0, decides to sell
  UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;  -- lock released

Between the locking SELECT and the COMMIT, any other transaction that does FOR UPDATE on the same row waits. This serializes access to that row, so two concurrent buyers can't both read "1 in stock" and both sell. It's the standard fix for oversell at the default Read Committed level.

The four lock strengths

Postgres offers four row-lock strengths, from strongest to weakest:

• FOR UPDATE: full lock. Blocks other FOR UPDATE, FOR SHARE, and any update or delete of the row. Use it when you intend to modify the row.
• FOR NO KEY UPDATE: like FOR UPDATE but slightly weaker, allowing concurrent FOR KEY SHARE. This is what a normal UPDATE of non-key columns takes internally.
• FOR SHARE: shared lock. Others can also FOR SHARE the row (read-lock it), but nobody can update or delete it. Use it when you need the row to stay unchanged but don't intend to modify it yourself.
• FOR KEY SHARE: weakest. Blocks only changes to the row's key. This is what a foreign key insert takes on the parent row.

For most application code, FOR UPDATE (intend to change it) and FOR SHARE (need it stable) are the two you'll use. The weaker two mostly matter for understanding what Postgres does internally with updates and foreign keys.

NOWAIT and SKIP LOCKED: don't just wait

By default, FOR UPDATE waits if the row is already locked. Two options change that, and they're genuinely useful.

NOWAIT fails immediately instead of waiting, raising an error if the row is locked. Use it when waiting is worse than failing, and you'd rather tell the user "someone else is editing this, try again" than hang:

SELECT * FROM tickets WHERE id = 42 FOR UPDATE NOWAIT;

SKIP LOCKED is the interesting one. It silently skips rows that are already locked, returning only the ones it could lock. This is exactly what you need to build a job queue where many workers pull tasks concurrently without stepping on each other.

Building a job queue with SKIP LOCKED

A common need is a queue table that multiple workers drain in parallel. Without SKIP LOCKED, workers contend on the same next row and serialize. With it, each worker grabs a different available job:

BEGIN;
  SELECT id, payload
  FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED;   -- take the first job no one else has locked

  -- mark it taken so it isn't picked again
  UPDATE jobs SET status = 'processing' WHERE id = :id;
COMMIT;

Ten workers running this loop each pick a distinct job, because SKIP LOCKED makes each skip the rows the others have locked. This pattern turns a plain table into a concurrent work queue without any external queue system, and it's how several lightweight Postgres-based queue libraries work under the hood. It's reliable, transactional, and needs no extra infrastructure.

Locking across joins

When your SELECT FOR UPDATE joins multiple tables, you can control which tables get locked. By default FOR UPDATE locks all rows returned. Name the table to lock just that one:

SELECT o.*, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = 100
FOR UPDATE OF o;   -- lock the order row, not the customer row

This avoids locking rows you're only reading for context, like the customer name here, which reduces contention.

Keeping it safe

Two habits keep row locking from causing trouble, both echoing earlier articles. Acquire locks in a consistent order (by primary key) to avoid the deadlocks from the previous articles, especially when locking multiple rows. And keep the transaction short, because the lock is held until commit, so a slow operation between the lock and the commit blocks other writers the whole time.

SELECT FOR UPDATE and its variants are the precise tools for concurrent correctness. Plain FOR UPDATE protects a read-modify-write, FOR SHARE pins a row you need stable, NOWAIT refuses to wait, and SKIP LOCKED turns a table into a parallel queue. Knowing the whole set means you rarely need heavier machinery to get concurrency right.

Next, we finish the concurrency module by going under everything we've discussed to the mechanism that makes it all work: MVCC, dead tuples, and how VACUUM keeps the whole thing running.