MVCC, Isolation Levels & Locking

MVCC

PostgreSQL never updates a row in place. An UPDATE writes a new version of the row and marks the old one as dead; a DELETE just marks it dead. Each transaction sees the versions that were committed when its snapshot was taken.

Consequences worth internalising:

• Readers never block writers, and writers never block readers.
• Dead row versions accumulate and must be cleaned up by autovacuum.
• A long-running transaction holds back that cleanup across the whole database.
• count(*) must scan, because visibility is per transaction.

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

VACUUM ANALYZE students;        -- reclaim space and refresh statistics
VACUUM FULL students;           -- rewrites the table, takes an exclusive lock

Use VACUUM FULL only in a maintenance window; for online compaction use the pg_repack extension.

Concurrency problems

Problem	What happens
Dirty read	Reading uncommitted data
Non-repeatable read	The same row gives different values within one transaction
Phantom read	The same range query returns new rows
Serialization anomaly	The result is impossible under any serial ordering

The three isolation levels

Postgres accepts all four SQL standard names but implements three distinct behaviours — READ UNCOMMITTED behaves as READ COMMITTED, so dirty reads are impossible in Postgres at any level.

Level	Dirty	Non-repeatable	Phantom	Serialization anomaly
READ COMMITTED (default)	No	Possible	Possible	Possible
REPEATABLE READ	No	No	No	Possible
SERIALIZABLE	No	No	No	No

SHOW transaction_isolation;
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET default_transaction_isolation = 'read committed';

READ COMMITTED takes a fresh snapshot per statement; REPEATABLE READ takes one snapshot for the whole transaction.

SERIALIZABLE in Postgres uses predicate locking (SSI) and guarantees the outcome matches some serial order. It does not block more, but it can abort a transaction with a serialization failure (SQLSTATE 40001). Any application using it must retry on that error.

Row locks

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;        -- exclusive
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker, allows FK references
SELECT * FROM accounts WHERE id = 1 FOR SHARE;         -- shared
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED;
SELECT * FROM jobs WHERE id = 1 FOR UPDATE NOWAIT;

The safe read-modify-write pattern:

BEGIN;
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;

FOR UPDATE SKIP LOCKED is the standard way to build a job queue in Postgres — each worker grabs a different row instead of queueing behind the others.

Table locks and deadlocks

LOCK TABLE students IN SHARE MODE;     -- rarely needed; prefer row locks
SET lock_timeout = '5s';
SET statement_timeout = '30s';

A deadlock happens when two transactions each hold a lock the other wants. Postgres detects it after deadlock_timeout (default 1s), aborts the cheaper transaction and returns SQLSTATE 40P01. Your application should catch it and retry.

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity WHERE wait_event_type = 'Lock';

SELECT * FROM pg_locks WHERE NOT granted;
SELECT pg_cancel_backend(pid);     -- cancel the query
SELECT pg_terminate_backend(pid);  -- kill the connection

Reducing contention

1. Keep transactions short and commit promptly.
2. Touch rows in a consistent order everywhere in the codebase — this alone removes most deadlocks.
3. Index the columns in your WHERE clauses; unindexed updates lock more than you intend.
4. Never hold a lock while waiting on an external service.
5. Use SKIP LOCKED for queues, and retry on 40001 and 40P01 as a matter of course.