Isolation Levels and Concurrency

Key features

• Dirty read: seeing another transaction's uncommitted change — PostgreSQL never allows this, at any isolation level
• Non-repeatable read: the same row read twice in one transaction returns different values, because another transaction committed a change to it in between
• Phantom read: the same query re-run in one transaction returns a different SET of rows, because another transaction inserted/deleted matching rows in between
• Serialization anomaly: the combined outcome of concurrent, individually-fine transactions is inconsistent with every possible serial ordering of them
• PostgreSQL never permits dirty reads at any level; Repeatable Read additionally prevents non-repeatable reads AND phantom reads (stronger than the SQL standard requires); only Serializable prevents serialization anomalies too

Command

-- 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;

Key features

• PostgreSQL has 3 internally distinct isolation levels, not 4 — Read Uncommitted is accepted as valid syntax but behaves identically to Read Committed
• Dirty reads never happen in PostgreSQL, at any level, including nominal "Read Uncommitted" — this is stronger than the SQL standard requires at that level
• PostgreSQL's Repeatable Read also prevents phantom reads, which the SQL standard only requires starting at Serializable — this is a real, documented deviation worth knowing explicitly
• Only Repeatable Read and Serializable can fail a transaction with a serialization error requiring retry — Read Committed never does
• Serializable in PostgreSQL is implemented via Serializable Snapshot Isolation (SSI), detecting conflicts via predicate locking rather than literally executing transactions one at a time

Command

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;

Key features

• Pessimistic: SELECT ... FOR UPDATE locks the row immediately; other transactions attempting to lock or modify it wait (or fail, with NOWAIT)
• Optimistic: no lock is taken on read; the write is conditioned on the row being unchanged since it was read, typically via a version/updated_at column
• Pessimistic pays a locking cost on every read-then-write, even when no conflict would have occurred — a cost independent of actual contention
• Optimistic pays a retry cost only when a conflict genuinely occurred — cheap under low contention, expensive (frequent retries) under high contention
• Optimistic concurrency requires the application to handle the "0 rows updated, someone else changed it" case explicitly — it is not automatic the way a lock wait is

Command

-- 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

Key features

• SELECT ... FOR UPDATE locks every row it returns until the current transaction ends (COMMIT or ROLLBACK)
• Other transactions can still plain-SELECT the locked rows — FOR UPDATE only blocks other lockers/writers, not readers
• The lock is acquired the moment the SELECT runs, and held for the rest of the transaction — using it outside an explicit BEGIN...COMMIT is nearly always a mistake, since autocommit would release it immediately
• FOR UPDATE should target exactly the rows about to be modified — locking an unnecessarily broad set increases contention for no benefit
• A transaction using FOR UPDATE must still keep the surrounding transaction short — the lock, like any lock, is held for the transaction's whole duration

Command

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;

Key features

• FOR UPDATE: strongest — conflicts with all four modes, including another FOR UPDATE or any concurrent FOR KEY SHARE
• FOR NO KEY UPDATE: conflicts with FOR SHARE, FOR NO KEY UPDATE and FOR UPDATE, but NOT with FOR KEY SHARE — this is what a plain UPDATE acquires automatically when it does not touch a primary key or unique column
• FOR SHARE: conflicts with FOR NO KEY UPDATE and FOR UPDATE, but allows concurrent FOR SHARE and FOR KEY SHARE
• FOR KEY SHARE: weakest — only conflicts with FOR UPDATE; specifically exists to let a foreign key check block a DELETE without blocking ordinary concurrent UPDATEs
• Choosing a weaker lock than necessary risks a race the lock was meant to prevent; choosing a stronger lock than necessary needlessly increases contention

Command

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

Key features

• NOWAIT: fails immediately with an error (rather than waiting) if a selected row cannot be locked right away
• SKIP LOCKED: silently omits already-locked rows from the result set instead of waiting or erroring — returns only rows it could actually lock
• SKIP LOCKED gives an inconsistent view of the data (it is not "all matching rows," only "all matching rows that happened to be free") — the docs explicitly warn it is unsuitable for general-purpose queries
• The canonical SKIP LOCKED use case is a job/task queue: many workers each run SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1 to atomically claim one row nobody else has already claimed
• Both options only affect the row-level lock; the required table-level ROW SHARE lock is still acquired normally either way

Command

-- 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;

Key features

• Inventory: SELECT quantity, then check > 0, then UPDATE — two concurrent buyers can both pass the check on the same pre-decrement quantity
• Payments: checking "has this payment already been processed" then processing it has the same gap — a double-submitted request can be processed twice
• Counters: SELECT count, compute count + 1, UPDATE with the computed value — classic lost-update pattern, same shape as max(id)+1
• Job claiming: multiple workers SELECT the same "next" unclaimed job before any of them has marked it claimed
• Uniqueness checks: "check no row with this email exists, then INSERT" has a gap two concurrent signups can both pass

Command

-- 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