Two sessions hit the same row at the same time and the wrong one wins. That's the entire problem this post is about. A single connection running one statement at a time is trivial - the database just does the work. The moment a second connection shows up wanting the same rows, someone has to decide what it's allowed to see and when it has to wait. Get that decision wrong and you don't get a crash, you get a number that's quietly off by a few hundred dollars three weeks later.
PostgreSQL solves this with two mechanisms working together: transaction isolation levels, which decide what a transaction is allowed to see, and locks, which decide what it's allowed to touch concurrently. They're often explained as competing approaches. In practice PostgreSQL uses both at once, and understanding where each one applies is what lets you pick the right isolation level instead of copying whatever the ORM defaults to.
The Three Read Phenomena, With Real Sessions
The SQL standard defines isolation levels by which anomalies they permit. Set up a table and watch them happen.
CREATE TABLE people (id INT PRIMARY KEY, name TEXT, salary INT);
INSERT INTO people VALUES (1, 'John', 150), (2, 'Jack', 200);
Dirty read - reading a value another transaction hasn't committed yet. This one's easy to demonstrate but you won't actually get it in PostgreSQL:
-- Session A
BEGIN;
UPDATE people SET salary = 180 WHERE id = 1;
-- not committed yet
-- Session B, same moment
BEGIN;
SELECT salary FROM people WHERE id = 1; -- returns 150 immediately, never sees the uncommitted 180
Under READ UNCOMMITTED in the SQL standard, session B would see 180 and then watch it vanish if A rolls back. PostgreSQL accepts READ UNCOMMITTED as a level name for compatibility but implements it identically to READ COMMITTED - MVCC never exposes an uncommitted row version to another transaction, so dirty reads are structurally impossible regardless of the level you request.
Non-repeatable read - the same row gives you two different answers in one transaction:
-- Session A
BEGIN;
SELECT salary FROM people WHERE id = 1; -- 150
-- Session B
UPDATE people SET salary = 180 WHERE id = 1;
COMMIT;
-- Session A, same transaction, same query
SELECT salary FROM people WHERE id = 1; -- 180 under READ COMMITTED
COMMIT;
Under the default READ COMMITTED, each statement gets its own fresh snapshot, so A's second SELECT sees B's committed change. That's often exactly what you want - a long-running report shouldn't have to hold every row frozen from minute one. It's a problem the moment your transaction logic assumes the two reads agree.
Phantom read - a repeated range query returns a different set of rows:
-- Session A
BEGIN;
SELECT * FROM people WHERE salary < 250; -- 2 rows
-- Session B
INSERT INTO people VALUES (3, 'Jacob', 120);
COMMIT;
-- Session A, same transaction
SELECT * FROM people WHERE salary < 250; -- 3 rows now, under READ COMMITTED
Full detail on how each PostgreSQL level maps to these phenomena, including the write-skew case snapshot isolation doesn't catch, is in transaction isolation levels.
Lost Updates: The Anomaly That Actually Costs Money
The read phenomena above are visible - a SELECT returns a value you can inspect. Lost updates are worse because nothing looks wrong; the transactions just silently overwrite each other's work.
-- Session A -- Session B
BEGIN; BEGIN;
SELECT salary FROM people WHERE id = 1; SELECT salary FROM people WHERE id = 1;
-- reads 150, app computes 150+30=180 -- reads 150, app computes 150+50=200
UPDATE people SET salary = 180
WHERE id = 1;
COMMIT;
UPDATE people SET salary = 200
WHERE id = 1;
COMMIT;
Both raises were legitimate. The final salary is 200 - session A's raise never happened, and nothing in either transaction's log says so. Under READ COMMITTED this runs clean with no error; the second UPDATE just wins.
Two fixes, both cheap:
-- Option 1: pessimistic - lock the row so B waits for A to finish
BEGIN;
SELECT salary FROM people WHERE id = 1 FOR UPDATE;
UPDATE people SET salary = salary + 30 WHERE id = 1;
COMMIT;
FOR UPDATE takes an exclusive row lock at the SELECT, so the second session blocks until the first commits and then works from the updated value instead of the stale one.
-- Option 2: don't read-then-write at all
UPDATE people SET salary = salary + 30 WHERE id = 1;
If the update is expressible as a delta rather than a read-compute-write round trip, skip the read entirely - PostgreSQL's UPDATE is atomic per row regardless of isolation level, so there's no window for another transaction to sneak in.
REPEATABLE READ catches this differently: the second UPDATE would raise a serialization error (could not serialize access due to concurrent update) instead of silently overwriting, forcing the application to retry. That's a real fix, but it means every write path touching contested rows needs retry logic. FOR UPDATE is usually the simpler tool when you already know which rows are contested.
Locks: What They Actually Protect
Isolation levels are about visibility. Locks are the mechanism that enforces write ordering underneath them, and PostgreSQL has more lock granularity than most people reach for.
Table-level locks range from ACCESS SHARE (a plain SELECT) up to ACCESS EXCLUSIVE (DROP TABLE, TRUNCATE, most ALTER TABLE), which conflicts with every other mode including a read. That's the one that bites in production: an ALTER TABLE that looks harmless queues behind whatever's currently running, then blocks every new query - including SELECTs - behind itself until it finishes.
Row-level locks are what FOR UPDATE above used, and PostgreSQL gives you four strengths - FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, FOR UPDATE - so a foreign-key check doesn't have to block a full row update it has no reason to conflict with. A queue-worker pattern worth knowing:
BEGIN;
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- process the job, then
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;
SKIP LOCKED lets multiple workers pull from the same queue table without piling up behind each other's row locks - each worker just grabs the next unlocked row instead of waiting.
PostgreSQL also distinguishes locks from latches, and mixing them up sends you chasing the wrong metric. Locks are logical, transaction-scoped, and visible in pg_locks. Latches (PostgreSQL calls them lightweight locks, LWLocks) guard internal structures like buffer headers for a handful of CPU instructions and never show up in pg_locks at all - they surface as wait_event_type = 'LWLock' in pg_stat_activity. A query stuck behind a row lock and a system stuck on LWLock contention look identical from "everything is slow" but need completely different fixes. The full mode table and how to query blocking chains is in database locks and latches.
Deadlocks
Locks that wait on each other in a cycle produce a deadlock, and it doesn't take an exotic schema to hit one - just two transactions touching the same two rows in opposite order:
-- Session A -- Session B
BEGIN; BEGIN;
UPDATE people SET salary = 180
WHERE id = 1;
UPDATE people SET salary = 130
WHERE id = 2;
UPDATE people SET salary = 230
WHERE id = 2; -- blocks, B holds this row
UPDATE people SET salary = 280
WHERE id = 1; -- blocks, A holds this row
Neither session can proceed. PostgreSQL doesn't wait forever - after deadlock_timeout (1 second by default), it builds a wait-for graph, finds the cycle, and aborts one transaction with SQLSTATE 40P01. The other proceeds normally. The aborted transaction's application code has to catch that and retry; PostgreSQL won't do it for you.
The fix is boring and reliable: acquire locks on multiple rows in a consistent order everywhere in the codebase. If every code path updates the lower id before the higher one, this particular cycle can't form. It's the kind of rule that's easy to state and easy to violate once the update lives in three different services that don't know about each other's lock order.
MVCC Is Why Reads Don't Block Writes
Everything above about isolation levels and row locks sits on top of Multiversion Concurrency Control. PostgreSQL never updates a row in place - an UPDATE writes a new row version tagged with the creating transaction ID and marks the old version's deletion ID, and a transaction's snapshot determines which versions it can see. That's why a SELECT never blocks behind a concurrent UPDATE: the reader just works from an older, still-valid version while the writer creates a new one. It's also why long-idle transactions are dangerous even when they're not touching contested rows - they pin an old snapshot, which keeps VACUUM from reclaiming the dead versions everything after it produced. A deep dive on the version bookkeeping, plus how optimistic and pessimistic strategies split the work, is in concurrency control in databases.
None of this is a reason to reach for SERIALIZABLE everywhere and call it solved - stronger isolation means more aborted transactions and more retry logic to write correctly, and most tables in most applications are read far more than they're contested. The useful move is matching the isolation level and lock strategy to the one or two tables where correctness actually depends on it, rather than picking one setting for the whole database and hoping. This is also the kind of drift that's hard to catch by reading code - a lock-order violation that was fine at low traffic starts producing real deadlocks once two services scale independently, and a lost-update pattern sits quiet until two write paths finally race. NeverBlink watches for exactly that: rising deadlock and serialization-failure rates, lock-wait chains, and long-held snapshots, and it surfaces them as a specific transaction and query to look at - a recommendation you act on, not a lock mode it silently changes underneath you.
