Row-level locks in PostgreSQL: FOR UPDATE, FOR NO KEY UPDATE, and preventing double booking Intermediate

Problem

A ticketing system has a seats table (column status: available or booked). Two requests book the same seat 42 almost simultaneously, each running: SELECT status, sees available, then UPDATE ... SET status = 'booked'.

Anti-pattern: read then write, seat 42 is sold twice

◀ Prev▶ PlayNext ▶↺ RestartStep 1/4

Request 1 reads the status and sees the seat is free. A plain SELECT holds no lock.

The database serializes the two UPDATEs, but the second one does not re-check the condition. A plain SELECT only observes; it holds no rights. There are two fixes:

• Put the condition into the write: UPDATE seats SET status = 'booked' WHERE id = 42 AND status = 'available' RETURNING id, then check that exactly one row came back. This is the default for simple conditions.
• Hold a lock from read to write with SELECT ... FOR UPDATE, when the decision takes several steps and the row must stay stable throughout them.

Fixed with FOR UPDATE: request 2 waits, then sees the seat is booked

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

Request 1 runs SELECT id FROM seats WHERE id = 42 AND status = available FOR UPDATE inside a transaction and holds the row 42 lock.

Core concepts

• A row lock is held until the end of the transaction (COMMIT or ROLLBACK). Running SELECT FOR UPDATE in autocommit releases the lock as soon as the statement ends and protects nothing that follows.
• A row lock only blocks writers and lockers of the same row; a plain SELECT can still read thanks to MVCC.
• A long-held lock means others wait long: do not make HTTP calls or heavy computations while holding a lock.
• Under Read Committed, after the wait ends PostgreSQL re-evaluates WHERE against the latest row version; under Repeatable Read and Serializable, if the row changed after the snapshot, it raises 40001 (see isolation levels).

Four modes and the conflict matrix

Mode	Use when
FOR UPDATE	About to DELETE, or UPDATE a key column
FOR NO KEY UPDATE	About to UPDATE without changing key columns. Prefer it over FOR UPDATE because it does not block foreign key checks
FOR SHARE	Reading and need the row not to be modified or deleted until the end of the transaction
FOR KEY SHARE	Only need the key columns not to change; foreign keys use this mode

Conflicts (X means they wait on each other, ok means both can hold it):

Held \ Requested	KEY SHARE	SHARE	NO KEY UPDATE	UPDATE
KEY SHARE	ok	ok	ok	X
SHARE	ok	ok	X	X
NO KEY UPDATE	ok	X	X	X
UPDATE	X	X	X	X

Which statement takes which lock automatically

Statement	Automatic row lock
Plain SELECT	None
DELETE, or an UPDATE changing a column of a unique index usable by a foreign key	FOR UPDATE
Any other UPDATE	FOR NO KEY UPDATE
INSERT/UPDATE of a child table with a foreign key	FOR KEY SHARE on the parent row

Practical consequence: modifying a regular column of the parent table does not block inserts into the child table (since PostgreSQL 9.3).

However, a DELETE of the parent row, or SELECT ... FOR UPDATE on it, waits for every running child insert and vice versa. That is the source of many “mysterious” lock waits and deadlocks involving foreign keys.

Two little-known facts

• Row locks are written to disk (into the row itself, via xmax). SELECT FOR UPDATE dirties pages and increases WAL, even when nothing is modified. When several transactions lock the same row with FOR SHARE, PostgreSQL uses a multixact, which also has an age that needs freezing (see wraparound).
• Joins: FOR UPDATE without a table list locks every participating table. Use FOR UPDATE OF orders to lock only orders.

How it works: where the waiter shows up in pg_locks

Suppose A has done FOR UPDATE on row 1 and not committed:

What session B does	Result
Plain SELECT	Reads the committed version, does not wait
SELECT ... FOR UPDATE	Waits for A to finish, then reads the new version per the isolation rules
UPDATE of the same row	Waits; then re-checks the WHERE condition

Run it with two psql connections (or examples/src/postgres-locks/row-lock-demo.ts) and look at pg_locks while B is waiting:

┌─────────┬──────────────────┬─────────────────┬──────────────────┬─────────┬──────────────────┐
│ (index) │ application_name │ locktype        │ mode             │ granted │ wait_event_type  │
├─────────┼──────────────────┼─────────────────┼──────────────────┼─────────┼──────────────────┤
│ 0       │ 'session_a'      │ 'relation'      │ 'RowShareLock'   │ true    │ 'Client'         │
│ 3       │ 'session_a'      │ 'transactionid' │ 'ExclusiveLock'  │ true    │ 'Client'         │
│ 6       │ 'session_b'      │ 'tuple'         │ 'AccessExclusiveLock' │ true │ 'Lock'          │
│ 8       │ 'session_b'      │ 'transactionid' │ 'ShareLock'      │ false   │ 'Lock'           │
└─────────┴──────────────────┴─────────────────┴──────────────────┴─────────┴──────────────────┘

How to read it:

1. Find the row with granted = false: that is the waiting session (session_b, ShareLock on transactionid).
2. That transaction ID is the lock holder’s transaction (A). B does not wait on the data row directly; it waits for A to finish.
3. The tuple row with granted = true is where B is queued; do not look only for locktype = 'tuple'.
4. The number and order of rows may differ, since pg_locks also shows internal locks. Set application_name per session to connect app logs with database state.

When A runs COMMIT, B is granted the lock immediately. Under Read Committed, B works with the new row version; if A deleted the row or changed a column in the filter condition, B may get 0 rows.

Choosing a tool: a lock or something else?

Situation	Use	Reason
Simple single-row condition	UPDATE ... WHERE condition RETURNING	No need to hold a lock for long
A multi-step decision on the same row	SELECT ... FOR NO KEY UPDATE in a short transaction	Keeps the row stable
Rare conflicts, users edit for a long time	Version column (optimistic)	No lock held while waiting on the user
No row to lock (a logical resource)	Advisory lock (pg_advisory_xact_lock)	The application defines its own lock convention
“Unique” / “no overlap” rules	UNIQUE / EXCLUDE	The database enforces it, no manual locking
Many workers taking jobs from a queue	FOR UPDATE SKIP LOCKED	See NOWAIT and SKIP LOCKED

Real-world example

Booking seats when ticket sales open

Sales open for 5,000 tickets, thousands of requests pour in within seconds, many targeting a few good rows of seats.

Use UPDATE ... WHERE status = 'available' RETURNING id: each request competes for one row, the losers get 0 rows and return “sold out”. The transaction holds a single statement, so the lock is held for a few milliseconds.

The wrong approach stretches the lock: SELECT FOR UPDATE, call the payment gateway for 3 seconds, then UPDATE. Every hot seat becomes a 3-second queue for all later requests. The numbers are an illustrative scenario.

Hold-and-pay like this should be two steps: a time-limited hold in the database, payment outside the transaction.

With SKIP LOCKED, results are incomplete by design: good for many workers competing for jobs, wrong for a screen that must list every pending job.

NOWAIT errors immediately instead of waiting. Use lock_timeout (default 0, i.e. wait forever) as a safety net for every statement that may wait on a lock.

🎮 Try it

• Scale Lab: concert ticketing: hot rows, lock hold time, and splitting inventory.
• Choose a lock/isolation level: which lock type fits which problem.

Production checklist

1. The business condition lives in the write statement, and the code checks the row count or RETURNING.
2. Lock and write in the same transaction on the same connection. With an ORM, pass the right transaction handle through every function in the flow.
3. Use FOR NO KEY UPDATE when not changing key columns; use FOR UPDATE OF <table> with joins.
4. Transactions that hold locks must be short: no external API calls, no waiting on users.
5. Set lock_timeout (and idle_in_transaction_session_timeout) for the app so lock waits do not hang forever.
6. Lock multiple rows in a fixed order (see deadlock).
7. Track lock wait time with pg_stat_activity (wait_event_type = 'Lock') and log_lock_waits.

Trade-offs

Pros

• Serializes conflicting operations on exactly the row that needs protection.
• Uses the existing transaction machinery and combines with SKIP LOCKED/NOWAIT.
• Allows re-checking the condition after acquiring the lock.

Cons

• The longer the lock is held, the longer others wait; it easily becomes a bottleneck if the transaction contains slow logic.
• Hard to spot in review: a FOR UPDATE in a shared function can lock more than expected.
• A heavily written hot row becomes a waiting point; when conflicts are rare, optimistic locking is cheaper.

Common mistakes

Common pitfall

Selecting the row in one statement and locking it in the next. SELECT id FROM seats WHERE status = 'available' LIMIT 1, then UPDATE ... WHERE id = <the id just read> are two separate steps: another request can pick the same id.

FOR UPDATE must be part of the selecting statement itself, or the select and write must be merged into one statement.

Common pitfall

FOR UPDATE and UPDATE on two different pooled connections. The two statements are not in the same transaction, so the first connection’s lock does not protect the write decision on the second.

Summary

Key takeaways

• A row lock is held until the end of the transaction; a plain SELECT is not blocked thanks to MVCC, writers and lockers are.
• An UPDATE that does not change the key takes FOR NO KEY UPDATE; DELETE or a key change takes FOR UPDATE; foreign keys take FOR KEY SHARE on the parent row.
• A lock waiter shows up as an ungranted ShareLock on the holder’s transactionid.
• Prefer UPDATE ... WHERE condition RETURNING; use FOR UPDATE when the row must stay stable across steps; set lock_timeout.
• Row locks are written to disk, so locking rows in bulk is not free.

Self-check:

1. Why can two requests that both see available still double-book even though PostgreSQL serialized the two UPDATEs?
2. Which lock mode does an UPDATE of a regular column take, and which does an UPDATE of the primary key take?
3. In pg_locks, which row tells you who is waiting for whom?

References

• Explicit Locking — PostgreSQL 17
• SELECT, The Locking Clause — PostgreSQL 17
• Transaction Isolation — PostgreSQL 17
• Release 9.3 — PostgreSQL