beginnerintermediateadvanced

PostgreSQL · Section 16

Isolation Levels and Concurrency

Level: intermediate
Read: 36 min
Concepts: 7

The four concurrency phenomena in escalating order of subtlety, the two places PostgreSQL's actual behavior deliberately exceeds what the SQL standard requires, the optimistic-vs-pessimistic framing for choosing a concurrency-control strategy, SELECT ... FOR UPDATE and its three sibling lock strengths for expressing exactly how strong a claim on a row is really needed, NOWAIT and SKIP LOCKED for fail-fast and job-queue use cases respectively, and — tying it together — the single read-then-write race pattern that reappears, disguised, across inventory, payments, counters, job claiming and uniqueness workflows.

This section

Transaction Isolation Levels With PostgreSQL as an examplemkdev

What is true here

Dirty read (never in PostgreSQL) < non-repeatable read < phantom read < serialization anomaly — Repeatable Read prevents the first three, only Serializable prevents all four.
PostgreSQL's Repeatable Read exceeds the SQL standard by also blocking phantom reads — a real, documented deviation worth knowing explicitly.
Pessimistic locking (FOR UPDATE) pays a cost upfront; optimistic locking (a version column) pays a cost only on an actual conflict — the right choice tracks real contention.
FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE form a strength ladder — pick the weakest one that expresses the real requirement.
Inventory, payments, counters, job claiming and uniqueness races are the same read-then-write gap, fixed by the same small set of tools.

What you will be able to do

Name which concurrency phenomenon a given bug report describes, and which isolation level would have prevented it
Explain the specific places PostgreSQL's isolation levels exceed the SQL standard's minimum guarantees
Choose between optimistic and pessimistic concurrency control based on expected contention
Select the correct row-lock strength (FOR UPDATE/FOR NO KEY UPDATE/FOR SHARE/FOR KEY SHARE) and use NOWAIT/SKIP LOCKED appropriately
Recognize a read-then-write race in a new workflow and apply the correct fix

From naming the phenomenon to the tool that prevents it

Name the phenomenon

dirty/non-repeatable/phantom/anomaly

Pick a strategy

isolation level, or explicit locking

Apply it to the real workflow

inventory, payments, counters, jobs, uniqueness

Naming the phenomena

The four concurrency phenomena, and exactly where PostgreSQL's real behavior stands relative to the SQL standard.

Dirty Reads, Non-Repeatable Reads, Phantom Reads and Serialization Anomalies

coreintermediate

A dirty read sees another transaction's uncommitted change. A non-repeatable read sees the same row return different values on a second SELECT within one transaction, because another transaction committed a change to it in between. A phantom read sees a repeated query return a different set of rows (not just different values), because another transaction inserted or deleted matching rows in between. A serialization anomaly is the most subtle: the combined result of several committed, concurrent transactions could not have happened under ANY serial (one-at-a-time) ordering of them.

Think of it as

These four phenomena form an escalating ladder of "how much can concurrent activity disturb what I observe." A dirty read is the most obviously dangerous — building on data that might not even exist a moment later. A non-repeatable read is subtler: every value you read was genuinely committed, just not stable across your own transaction. A phantom read is the same instability applied to a set of rows rather than one row's values. A serialization anomaly is the subtlest of all — every individual read was fine, but the transactions collectively produced an outcome that is provably impossible under any actual one-at-a-time execution, which is exactly the class of bug that isolation levels beyond Read Committed exist to prevent.

sql

-- Read Committed (default): permits non-repeatable and phantom reads
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 1000
-- (another transaction commits a change here)
SELECT balance FROM accounts WHERE id = 1;  -- may now read 900
COMMIT;

What we're doing: Demonstrate a phantom read under Read Committed: the same WHERE condition matches a different set of rows on a second SELECT, because another transaction inserted a new matching row in between.

phantom_read.sqlsql

-- Session A:
BEGIN;
SELECT count(*) FROM orders WHERE status = 'pending';  -- returns 5

-- Session B, meanwhile:
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- Session A, same transaction, same query:
SELECT count(*) FROM orders WHERE status = 'pending';  -- returns 6 -- a phantom row appeared

3: First read of the matching set — 5 rows, inside Session A's still-open transaction.
5–6: Session B inserts and commits a new row that matches the same WHERE condition, entirely independently of Session A.
8: The identical query, run again in the SAME transaction, now sees a different set of matching rows — the defining feature of a phantom read.

Output

 count 
-------
     5

 count 
-------
     6

Why this works: Under Read Committed, each SELECT takes its own fresh snapshot, so a newly-committed row that matches the WHERE clause is legitimately visible to the second query — this is expected, documented behavior at this level, not a bug, and it is exactly the instability Repeatable Read exists to eliminate for transactions that need their own view of the matching set to stay fixed.

Counting on a repeated aggregate query to return the same answer within one Read Committed transaction

Wrong

sql

BEGIN;  -- Read Committed, the default
SELECT count(*) FROM orders WHERE status = 'pending';  -- 5, used to allocate 5 worker slots
-- ... some other work happens, another session inserts a new pending order ...
SELECT * FROM orders WHERE status = 'pending' LIMIT 5;  -- may now miss the true 6th row,
                                                          -- or double-book worker slot logic
                                                          -- built on the earlier count of 5

Better

sql

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders WHERE status = 'pending';  -- 5
-- the snapshot is now frozen for this whole transaction
SELECT * FROM orders WHERE status = 'pending';  -- guaranteed to still match exactly 5 rows
COMMIT;

What you see: Logic that reads a count early in a transaction and later assumes the same set of rows still matches produces subtly wrong results under concurrent write load — off-by-one allocations, a worker that never gets assigned the "phantom" row, or double-counted totals.

Why: Read Committed intentionally gives every statement the freshest possible snapshot, which means a count taken early in a transaction is not a promise about what a later query in the SAME transaction will see — any logic that needs the matching row set to stay fixed for the whole transaction needs Repeatable Read (or Serializable) to actually get that guarantee.

A non-repeatable read

Transaction A

accounts table

Transaction B

1. SELECT balance → 1000
2. UPDATE balance = 900; COMMIT
3. SELECT balance → 900 (different!)

The four phenomena

The four phenomena
Phenomenon	What changes	Prevented starting at
Dirty read	seeing uncommitted data	never allowed by PostgreSQL, at any level
Non-repeatable read	a row's values, between two reads	Repeatable Read
Phantom read	which rows match a query, between two reads	Repeatable Read (PostgreSQL exceeds the SQL standard here)
Serialization anomaly	the combined outcome vs any serial ordering	Serializable only

Remember: Dirty read (uncommitted data — never happens in PostgreSQL), non-repeatable read (a row's values change between two reads), phantom read (the matching row SET changes between two reads), serialization anomaly (the combined outcome is impossible under any serial ordering). Repeatable Read prevents the first three; only Serializable prevents all four.

PostgreSQL's Behavior Under Its Isolation Levels

coreintermediate

PostgreSQL implements only three distinct isolation levels internally, not four — requesting Read Uncommitted silently gives you Read Committed behavior instead, since PostgreSQL never allows dirty reads regardless of what you ask for. And PostgreSQL's Repeatable Read is stronger than the SQL standard requires: it also blocks phantom reads, which the standard only mandates starting at Serializable.

Think of it as

The SQL standard defines what each isolation level must, at minimum, prevent — but a database is always free to provide a stronger guarantee than the standard requires, and PostgreSQL does exactly that in two places. Treating the standard's isolation-level table as a literal description of PostgreSQL's behavior will make you expect weaker guarantees than you actually get, which is a relatively safe mistake — the real risk is the opposite: assuming Read Committed is unsafe against a dirty read because "read uncommitted might be involved," or assuming any level below Serializable can fail with a serialization error when only Repeatable Read and Serializable actually can.

sql

SHOW transaction_isolation;  -- 'read committed' by default

BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;  -- still reports 'read uncommitted' as requested,
                              -- but behaves exactly like Read Committed underneath
COMMIT;

What we're doing: Confirm that requesting Read Uncommitted still fully blocks a dirty read, proving PostgreSQL does not actually implement a weaker level even though it accepts the name.

read_uncommitted_is_read_committed.sqlsql

-- Session A:
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
UPDATE accounts SET balance = 999999 WHERE id = 1;  -- NOT committed yet

-- Session B, also "Read Uncommitted":
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- returns the ORIGINAL committed value -- Session A's uncommitted 999999
-- is never visible, despite both sessions requesting "Read Uncommitted"

3: Session A's change is deliberately left uncommitted.
8–9: Even under nominal Read Uncommitted, Session B cannot see it — proving PostgreSQL genuinely never permits a dirty read, regardless of the requested level name.

Output

 balance 
---------
    1000

Why this works: This is a deliberate design choice, not an oversight — PostgreSQL's MVCC architecture makes dirty reads structurally impossible to implement cheaply, so rather than build a weaker, riskier mode just to match every SQL standard level literally, PostgreSQL accepts the Read Uncommitted name for portability but silently maps it onto Read Committed's actual behavior.

Assuming PostgreSQL's Repeatable Read only prevents what the SQL standard's Repeatable Read minimally requires

Wrong

sql

-- reasoning based on the SQL standard's table alone:
-- "Repeatable Read still allows phantom reads, per the standard,
--  so I need Serializable to prevent this phantom-read scenario"
BEGIN ISOLATION LEVEL SERIALIZABLE;  -- reaching for the strongest,
                                       -- most expensive level unnecessarily

Better

sql

-- PostgreSQL's Repeatable Read already prevents phantom reads --
-- confirmed directly against postgresql.org/docs, not assumed from
-- the generic SQL-standard table
BEGIN ISOLATION LEVEL REPEATABLE READ;  -- sufficient, and cheaper than Serializable

What you see: A team defaults to Serializable everywhere "to be safe against phantom reads," paying Serializable's higher overhead and retry rate for a guarantee Repeatable Read already provides on PostgreSQL specifically.

Why: Documentation and tutorials that describe the SQL standard's isolation levels in the abstract are correct about the standard, but PostgreSQL's actual, documented behavior is stronger at Repeatable Read than the standard requires — checking PostgreSQL's own docs rather than a generic SQL-isolation reference avoids over-provisioning to Serializable when Repeatable Read already suffices.

PostgreSQL vs the SQL standard, per isolation level

more guaranteesfewer guarantees

matches the standardstronger than the standard

Read Uncommitted: behaves exactly like Read Committed — no dirty reads, though the standard would allow them
Read Committed: matches the standard exactly
Repeatable Read: also blocks phantom reads — the standard does not require this until Serializable
Serializable: matches the standard, via SSI predicate locking

SQL standard requirement vs PostgreSQL's actual behavior

SQL standard requirement vs PostgreSQL's actual behavior
Level	Standard requires	PostgreSQL actually provides
Read Uncommitted	may allow dirty reads	behaves exactly like Read Committed — no dirty reads
Read Committed	no dirty reads	no dirty reads (matches standard)
Repeatable Read	no dirty/non-repeatable reads; phantoms allowed	ALSO prevents phantom reads — stronger than required
Serializable	prevents all four phenomena	prevents all four (matches standard), via SSI

Remember: PostgreSQL implements 3 distinct isolation levels, not 4 — 'Read Uncommitted' is accepted syntax but behaves exactly like Read Committed. PostgreSQL's Repeatable Read is stronger than the SQL standard requires: it also blocks phantom reads. Check PostgreSQL's own docs, not a generic standard table, when reasoning about exactly what a level guarantees here.

Concurrency control strategy

Optimistic vs pessimistic control, and the FOR UPDATE lock family for expressing exactly how strong a claim is needed.

Optimistic vs Pessimistic Concurrency Control

coreintermediate

Pessimistic concurrency control locks a row before touching it (SELECT ... FOR UPDATE), assuming a conflict is likely enough to prevent upfront — other transactions simply wait. Optimistic concurrency control does not lock anything upfront; it reads a row, does its work, and only at write time checks (usually via a version column or WHERE clause matching the originally-read value) whether the row changed since it was read — assuming conflicts are rare enough to detect and retry instead of preventing.

Think of it as

The choice is a bet about how often two transactions will actually collide. Pessimistic control pays a locking cost on every read that might later write, whether or not a conflict would have actually happened — cheap under heavy contention, wasteful under light contention. Optimistic control pays nothing upfront and instead pays a retry cost, but only exactly when a real conflict occurred — cheap under light contention, potentially wasteful (lots of retries) under heavy contention. Neither is universally better; the right choice tracks how contended the actual workload is.

sql

-- pessimistic: lock the row up front
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- optimistic: no lock, check the version at write time
UPDATE accounts SET balance = balance - 100, version = version + 1
 WHERE id = 1 AND version = 7;  -- 0 rows updated means someone else changed it first

What we're doing: Show the same balance update implemented both ways, and what each does when a real conflict occurs.

pessimistic_vs_optimistic.sqlsql

-- PESSIMISTIC: Session A locks the row; Session B must wait
-- Session A:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- row is now locked
-- Session B, concurrently, tries the same:
-- SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- BLOCKS until A commits/rolls back

-- OPTIMISTIC: no lock; the conflict surfaces at write time instead
-- Session A reads version = 7, computes new balance, then:
UPDATE accounts SET balance = 900, version = 8 WHERE id = 1 AND version = 7;
-- if Session B already updated the row (version is now 8, not 7), this affects 0 rows --
-- the application must detect that and decide whether to retry

4: FOR UPDATE takes the lock immediately, before any conflict is even known to exist.
10: The WHERE version = 7 clause is the entire conflict check — if it matches 0 rows, someone else got there first, and no lock was ever held.

Output

-- pessimistic: Session B waits (or errors with NOWAIT)
-- optimistic: UPDATE 0  -- the application must check this and retry

Why this works: The two approaches trade the same underlying risk (two transactions racing to update the same row) for different costs: pessimistic control pays a locking cost unconditionally, in exchange for never needing retry logic; optimistic control pays nothing until a conflict genuinely happens, in exchange for the application having to detect and handle "0 rows updated" as a real, expected case rather than an error.

Using optimistic concurrency without actually checking the affected row count

Wrong

sql

-- application code:
UPDATE accounts SET balance = 900, version = 8 WHERE id = 1 AND version = 7;
-- application does not check how many rows this affected --
-- assumes it succeeded and proceeds as if balance is now 900

Better

sql

UPDATE accounts SET balance = 900, version = 8 WHERE id = 1 AND version = 7;
-- application checks the affected row count:
--   0 rows  -> someone else updated it first; re-read and retry (or surface a conflict to the user)
--   1 row   -> the update genuinely succeeded

What you see: Under concurrent access, a user's update appears to succeed (no database error was raised) but silently had no effect, because another transaction updated the same row first and the WHERE version = ... clause matched zero rows — the application never checked.

Why: Optimistic concurrency's entire conflict-detection mechanism IS the affected row count — there is no separate error or exception the database raises for a version mismatch, since as far as PostgreSQL is concerned the UPDATE simply matched no rows, which is not an error. Skipping that check throws away the one signal optimistic concurrency actually provides.

Two bets about how often transactions collide

Pessimistic — SELECT ... FOR UPDATE

+Locks the row immediately, on read
+Other transactions simply wait
+Pays a locking cost even when no conflict happens
+Best under high contention

Optimistic — version column

−No lock taken on read
−Write is conditioned on WHERE version = ?
−Pays a retry cost, only when a conflict actually occurs
−Best under low contention

Pessimistic vs optimistic concurrency

Pessimistic vs optimistic concurrency
Property	Pessimistic (FOR UPDATE)	Optimistic (version check)
Cost paid on read	lock acquisition, always	none
Cost paid on conflict	other transaction waits	the losing transaction must retry
Best under	high contention	low contention
Requires	holding a transaction/lock open	a version column or equivalent, and explicit retry logic

Remember: Pessimistic (SELECT ... FOR UPDATE) locks upfront and makes other transactions wait — good under high contention. Optimistic (version column + WHERE clause) takes no lock and detects conflicts only at write time via the affected row count — good under low contention, but requires the application to explicitly check for and handle a 0-row update as a real case.

Using SELECT ... FOR UPDATE Appropriately

coreintermediate

SELECT ... FOR UPDATE locks the returned rows against concurrent modification for the rest of the current transaction, forcing other transactions that want to lock or change the same rows to wait until this one commits or rolls back. It is the pessimistic tool for read-then-write sequences that must not race — read a row, decide something based on it, then update it, with a guarantee nobody else changed it in between.

Think of it as

FOR UPDATE exists specifically to close the gap between reading a value and writing based on it — without it, another transaction can commit a change in that gap, and your later write silently overwrites information it never saw. The lock does not prevent other transactions from reading the row (a plain SELECT still works), only from locking or writing it — so FOR UPDATE is surgical: it protects exactly the read-then-write sequence, not all access to the row.

sql

BEGIN;
SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE;
-- row is now locked -- no other transaction can lock or update it until COMMIT/ROLLBACK
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;

What we're doing: Show FOR UPDATE preventing the classic read-then-write inventory race: two transactions both check stock and both attempt to decrement it.

inventory_race_prevented.sqlsql

-- Session A:
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE;  -- reads 1, locks the row

-- Session B, concurrently:
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE;
-- BLOCKS -- must wait for Session A to commit or roll back

-- Session A finishes:
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;  -- 1 -> 0
COMMIT;

-- Session B, now unblocked, sees the up-to-date quantity:
-- SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE;  -- reads 0, NOT the stale 1

3: Session A's read is now protected -- nobody else can lock or modify this row until it finishes.
7: Session B genuinely waits here — it cannot proceed with a stale read of quantity=1.
10–11: Session A safely decrements, knowing no concurrent transaction could have changed the value underneath it.

Output

-- Session B unblocks only after Session A commits, and then correctly reads quantity = 0

Why this works: Without FOR UPDATE, both sessions could read quantity = 1 before either updates, both decrement based on that stale read, and the inventory could go negative or oversell the same last unit — FOR UPDATE closes exactly that gap by making the second reader wait until the first read-then-write sequence is fully resolved.

Using SELECT ... FOR UPDATE outside an explicit transaction

Wrong

sql

-- no BEGIN -- autocommit means this statement is its own complete transaction
SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE;
-- the lock is acquired and released in the SAME instant -- it never
-- protects anything, since the transaction that held it is already over
-- by the time the application runs its next statement

Better

sql

BEGIN;
SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE;
-- lock genuinely held here, across both statements
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;

What you see: FOR UPDATE appears in the code, giving a false sense of safety, but the same race condition it was meant to prevent still occurs under concurrent load — because the lock was released before the application even issued its next statement.

Why: A FOR UPDATE lock only protects a gap that exists WITHIN a transaction — without an explicit BEGIN wrapping both the SELECT and the later write, autocommit closes the transaction (and releases the lock) the instant the SELECT itself finishes, defeating the entire purpose of using FOR UPDATE in the first place.

FOR UPDATE closes the read-then-write gap

Session A

Session B

inventory row

1. SELECT ... FOR UPDATEreads 1, locks the row
2. SELECT ... FOR UPDATE
3. blocks — waits
4. UPDATE quantity = quantity - 1
5. COMMIT
6. unblocked — reads the up-to-date value (0, not stale 1)

FOR UPDATE at a glance

FOR UPDATE at a glance
Property	Behavior
Blocks other FOR UPDATE/writes on the same rows?	yes
Blocks plain SELECT on the same rows?	no
Lock released	at COMMIT or ROLLBACK
Typical use	read-then-write sequences that must not race (inventory, balances, job claiming)

Remember: SELECT ... FOR UPDATE locks the returned rows against other lockers/writers for the rest of the current transaction — it protects exactly a read-then-write sequence, blocking other FOR UPDATE/writes but not plain SELECTs. It only works inside an explicit transaction; used outside one, the lock is released before it can protect anything.

coreadvanced

These four row-locking clauses form a strength ladder from strongest to weakest: FOR UPDATE (exclusive — blocks everything else), FOR NO KEY UPDATE (blocks writes but not a FOR KEY SHARE lock, used automatically by UPDATEs that do not touch key/unique columns), FOR SHARE (shared — blocks writes but allows other FOR SHARE/FOR KEY SHARE readers), and FOR KEY SHARE (weakest — only blocks a DELETE or an UPDATE that actually changes a key column).

Think of it as

The four modes exist because "I plan to modify this row" is not one single kind of intent — locking for a full update is a stronger claim than locking just to prevent the row from being deleted while a foreign key still references it. Choosing the weakest lock mode that is actually sufficient minimizes what it blocks: a foreign-key-checking query only needs FOR KEY SHARE (block deletes, allow everything else), while an UPDATE that will change the row's data uses FOR NO KEY UPDATE automatically, reserving full FOR UPDATE for cases that specifically need to exclude even other FOR KEY SHARE lockers.

sql

SELECT * FROM orders WHERE id = 1 FOR UPDATE;        -- strongest: full exclusive intent
SELECT * FROM orders WHERE id = 1 FOR NO KEY UPDATE;  -- updating non-key columns
SELECT * FROM orders WHERE id = 1 FOR SHARE;          -- read, but block concurrent writers
SELECT * FROM orders WHERE id = 1 FOR KEY SHARE;      -- weakest: just block deletion

What we're doing: Show FOR KEY SHARE allowing a concurrent, unrelated UPDATE to the same row to proceed, while still blocking a DELETE — the exact selective behavior FOR KEY SHARE exists to provide.

key_share_selective_block.sqlsql

-- Session A: a foreign key check locks the referenced row
BEGIN;
SELECT * FROM products WHERE id = 42 FOR KEY SHARE;

-- Session B: an ordinary UPDATE that does not touch the key column
BEGIN;
UPDATE products SET description = 'Updated text' WHERE id = 42;
-- SUCCEEDS immediately -- FOR KEY SHARE does not conflict with FOR NO KEY UPDATE

-- Session C: attempts to DELETE the same row
BEGIN;
DELETE FROM products WHERE id = 42;
-- BLOCKS -- FOR KEY SHARE specifically conflicts with a delete's implicit FOR UPDATE-equivalent

3: The weakest lock — just enough to prevent the row from disappearing out from under a foreign key reference.
6–8: An unrelated UPDATE proceeds without waiting -- FOR KEY SHARE was deliberately chosen to not block this.
11–13: A DELETE, which really would break the foreign key reference, correctly still blocks.

Output

-- Session B's UPDATE: succeeds immediately
-- Session C's DELETE: blocks until Session A commits/rolls back

Why this works: This is precisely why FOR KEY SHARE exists as its own, weaker mode rather than everyone defaulting to FOR UPDATE or FOR SHARE — a foreign key reference genuinely only cares that the referenced row's key keeps existing, not that nothing about the row ever changes, so blocking ordinary UPDATEs would be needless contention for no real safety benefit.

Defaulting to FOR UPDATE everywhere out of caution

Wrong

sql

-- a read-only check that just needs to ensure the row is not deleted
-- while a related operation is in progress:
SELECT * FROM products WHERE id = 42 FOR UPDATE;  -- unnecessarily strong --
-- blocks EVERY concurrent locker, including ordinary UPDATEs that have
-- nothing to do with the concern this lock was meant to address

Better

sql

SELECT * FROM products WHERE id = 42 FOR KEY SHARE;
-- blocks only a DELETE -- exactly the concern, nothing more

What you see: A workload with many concurrent readers doing foreign-key-style checks experiences far more lock contention and blocking than the actual business logic requires, because every check reaches for the strongest available lock mode by habit.

Why: Each of the four modes exists to express a specific, narrower intent than "I might do anything to this row" — using FOR UPDATE when FOR KEY SHARE would fully express the actual requirement blocks concurrent operations (like ordinary UPDATEs) that were never actually in conflict with what the lock was protecting.

The four row-lock modes, strongest to weakest

stronger intentweaker intent

blocks fewer operationsblocks more operations

FOR KEY SHARE: only blocks DELETE / key-changing UPDATE
FOR SHARE: blocks writes, allows other readers
FOR NO KEY UPDATE: what a plain UPDATE takes automatically
FOR UPDATE: blocks everything, including other FOR KEY SHARE

Conflict matrix (does row A's lock make row B's request wait?)

Conflict matrix (does row A's lock make row B's request wait?)
Held lock	Conflicts with
FOR UPDATE	FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE
FOR NO KEY UPDATE	FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE
FOR SHARE	FOR UPDATE, FOR NO KEY UPDATE
FOR KEY SHARE	FOR UPDATE

Remember: FOR UPDATE (strongest, blocks everything) > FOR NO KEY UPDATE (blocks writes, not FOR KEY SHARE — what a normal UPDATE takes automatically) > FOR SHARE (blocks writes, allows other readers) > FOR KEY SHARE (weakest, only blocks DELETE and key-changing UPDATEs). Choose the weakest mode that actually expresses your real requirement to minimize needless contention.

NOWAIT and SKIP LOCKED Use Cases

coreintermediate

By default, SELECT ... FOR UPDATE waits for a locked row to become free. NOWAIT instead fails immediately with an error if any selected row is already locked. SKIP LOCKED instead silently excludes any already-locked rows from the result and returns whatever is left unlocked — the standard mechanism for building a job queue where multiple workers each need to claim a different, unclaimed row without waiting on each other.

Think of it as

Default FOR UPDATE assumes waiting for a lock is the right behavior — usually true for a genuine read-then-write on a specific row. NOWAIT is for situations where waiting is pointless and failing fast is better (a UI action that should immediately tell the user "someone else is editing this"). SKIP LOCKED is for a different shape of problem entirely: multiple interchangeable workers pulling from a shared pool of work, where any unclaimed row will do — waiting for a SPECIFIC row that's already claimed makes no sense when any other unclaimed row would serve equally well.

sql

-- job queue: each worker claims one unclaimed row, no waiting
SELECT id FROM jobs
 WHERE status = 'pending'
 ORDER BY created_at
 LIMIT 1
   FOR UPDATE SKIP LOCKED;

-- fail fast instead of waiting: e.g. a UI edit-lock check
SELECT * FROM documents WHERE id = 5 FOR UPDATE NOWAIT;

What we're doing: Simulate two workers concurrently claiming jobs from a shared queue with SKIP LOCKED, confirming they each get a different row with no waiting.

job_queue_skip_locked.sqlsql

-- Worker A:
BEGIN;
SELECT id FROM jobs WHERE status = 'pending'
 ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
-- returns job id 1, and locks it

-- Worker B, concurrently, running the SAME query:
BEGIN;
SELECT id FROM jobs WHERE status = 'pending'
 ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
-- does NOT wait for job 1, and does NOT return job 1 (it is locked) --
-- instead skips it and returns job id 2

-- both workers now process a different job, with zero contention
-- between them and no explicit coordination needed

3–5: Worker A claims job 1 and holds its lock for the rest of its transaction.
8–12: Worker B runs the identical query but SKIP LOCKED means job 1 is simply invisible to it — it gets job 2 instead of waiting.

Output

-- Worker A: id = 1
-- Worker B: id = 2 (no wait)

Why this works: SKIP LOCKED is exactly what turns SELECT ... FOR UPDATE ... LIMIT 1 into a safe, efficient work-claiming primitive for multiple concurrent workers — without it, every worker after the first would queue up waiting for the SAME already-locked row, even though any other unclaimed row would do the job equally well.

Using plain FOR UPDATE (no SKIP LOCKED) for a job-queue claim pattern

Wrong

sql

SELECT id FROM jobs WHERE status = 'pending'
 ORDER BY id LIMIT 1 FOR UPDATE;
-- Worker B, running concurrently with Worker A, BLOCKS waiting for
-- the exact same row Worker A already claimed -- even though other
-- unclaimed jobs exist and would serve Worker B just as well

Better

sql

SELECT id FROM jobs WHERE status = 'pending'
 ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
-- Worker B is not blocked at all -- it simply gets a different,
-- genuinely unclaimed row

What you see: A worker pool meant to process jobs in parallel instead serializes almost entirely — each worker waits for the previous one to finish before it can even see which job it will work on, defeating the purpose of having multiple workers.

Why: Without SKIP LOCKED, every worker's ORDER BY id LIMIT 1 FOR UPDATE targets the exact same top row, and the lock on that specific row forces every subsequent worker to queue up behind it one at a time — SKIP LOCKED is the only way to make "any of the unclaimed rows" actually mean any of them, rather than accidentally meaning "the same one, in sequence."

Two workers claiming jobs from the same queue

Plain FOR UPDATE

+Worker B targets the same top row as Worker A
+Worker B blocks, waiting for that exact row
+Workers serialize even though other jobs are free

FOR UPDATE SKIP LOCKED

−Worker B silently skips the row Worker A already locked
−Worker B claims a different, genuinely unclaimed row
−No waiting — workers run in true parallel

Default vs NOWAIT vs SKIP LOCKED

Default vs NOWAIT vs SKIP LOCKED
Option	Behavior when a row is already locked
(default)	waits until the lock is released
NOWAIT	fails immediately with an error
SKIP LOCKED	silently excludes that row, returns the rest

Remember: NOWAIT fails immediately instead of waiting for a locked row — for fail-fast UI/API checks. SKIP LOCKED silently excludes locked rows and returns the rest — the standard building block for a multi-worker job queue, where any unclaimed row will do. Neither is a general-purpose SELECT option; SKIP LOCKED specifically gives an inconsistent view unsuitable outside queue-like patterns.

Applying it

The one race-condition pattern behind five common real-world workflows, and the small set of known fixes.

Race Conditions in Inventory, Payments, Counters, Jobs and Uniqueness Workflows

coreintermediate

Five common workflow shapes share the same underlying race: read a value, decide something based on it, then write — with a gap in between where another transaction can do the exact same thing using the same stale read. Inventory (oversell the last unit), payments (double-charge or double-refund), counters (lost updates), job claiming (two workers processing the same job), and uniqueness checks (two "new" rows that were each checked against the same pre-insert state) are all this one pattern wearing different clothes.

Think of it as

Once you see the read-then-write gap as the actual bug, all five "different" scenarios collapse into one problem with a small number of known fixes: a single atomic SQL statement that reads and writes together (no gap at all), a row lock (FOR UPDATE) that closes the gap by making a second reader wait, a unique constraint that turns the race into a visible, safe error instead of silent corruption, or SKIP LOCKED for the specific case of interchangeable workers claiming from a shared pool. Naming the pattern is what lets you recognize it in a sixth scenario nobody has described yet.

sql

-- the general fix shape: collapse read-then-write into one atomic statement
UPDATE inventory SET quantity = quantity - 1
 WHERE product_id = 42 AND quantity > 0
RETURNING quantity;
-- 0 rows returned means "no stock" -- no separate SELECT, no gap

What we're doing: Fix the inventory oversell race by replacing SELECT-then-UPDATE with one atomic UPDATE that both checks and decrements in a single statement.

inventory_atomic_fix.sqlsql

-- RACY: two concurrent buyers, both pass the same stale check
-- SELECT quantity FROM inventory WHERE product_id = 42;  -- both read 1
-- (application checks quantity > 0 in code -- both pass)
-- UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;  -- both succeed, quantity -> -1

-- FIXED: the check and the write are the same atomic statement
UPDATE inventory SET quantity = quantity - 1
 WHERE product_id = 42 AND quantity > 0
RETURNING quantity;
-- first buyer: 1 row returned, quantity now 0
-- second buyer, concurrently: 0 rows returned -- WHERE quantity > 0 no longer matches

2–4: The racy version has a real gap between reading quantity and writing based on it.
9–11: The fixed version has no gap at all — PostgreSQL evaluates the WHERE clause and performs the write as one indivisible operation per row.

Output

-- first UPDATE: quantity = 0 (success)
-- second, concurrent UPDATE: 0 rows affected (correctly rejected, no oversell)

Why this works: A single UPDATE ... WHERE quantity > 0 statement has no window for another transaction to interleave between the check and the write, because PostgreSQL performs both as one atomic operation on each row — this is the same principle behind why nextval() is safe and max(id)+1 is not, generalized to any read-then-write workflow, not just id generation.

Treating each of the five workflows as a separate, novel problem instead of recognizing the shared pattern

Wrong

sql

-- payments: reaching for a NEW, bespoke locking scheme
-- counters: reaching for a DIFFERENT bespoke locking scheme
-- job claiming: reaching for yet another ad-hoc coordination mechanism
-- each "solved" independently, inconsistently, by different developers
-- at different times -- some race-free, some not

Better

sql

-- recognize the shared shape (read-then-write gap) and apply one of
-- the same few known fixes consistently:
--   1. one atomic statement (UPDATE ... WHERE ... RETURNING)
--   2. FOR UPDATE to close the gap explicitly
--   3. a real unique constraint to make the race a safe, visible error
--   4. FOR UPDATE SKIP LOCKED for interchangeable-worker claiming

What you see: A codebase ends up with five different, inconsistent approaches to what is structurally the same bug — some workflows race-free by luck, others not, and no shared vocabulary for a reviewer to recognize the pattern in a sixth, new workflow before it ships.

Why: Recognizing "this is a read-then-write race" as a single reusable pattern — the same one behind the earlier max(id)+1 concept — means every future workflow with the same shape gets evaluated against the same small, known set of fixes instead of being solved from scratch each time, which is both faster and more consistently correct.

One pattern, five disguises

Inventory

oversell the last unit

Payments

double-charge/refund

Counters

a lost update

Job claiming

two workers, one job

Uniqueness

two "new" rows, same key

The pattern and its standard fix, per workflow

The pattern and its standard fix, per workflow
Workflow	The gap	Standard fix
Inventory	read quantity, then decrement	single UPDATE ... WHERE quantity > 0, or FOR UPDATE
Payments	check processed, then process	unique constraint on an idempotency key
Counters	read count, compute, write	SET n = n + 1 in one statement, or FOR UPDATE
Job claiming	read next job, then claim it	FOR UPDATE SKIP LOCKED
Uniqueness	check exists, then insert	a real UNIQUE constraint, not an app-side check

Remember: Inventory oversell, payment double-processing, lost counter updates, duplicate job claims, and race-y uniqueness checks are the same read-then-write gap wearing different clothes. Fix with one atomic statement (UPDATE ... WHERE ... RETURNING), FOR UPDATE to close the gap explicitly, a real UNIQUE constraint to make it a safe error, or FOR UPDATE SKIP LOCKED for interchangeable-worker claiming.