Isolation and Locking

Introduction

When many clients access the database at once, isolation levels define what one transaction can see of another's work. Locks enforce those rules when conflicts arise. This chapter explains dirty reads, non-repeatable reads, and phantoms, compares PostgreSQL's default READ COMMITTED with MySQL InnoDB's default REPEATABLE READ, and covers FOR UPDATE, FOR SHARE, deadlocks, and long-transaction bloat. Compare with MySQL isolation and locks.

Prerequisites

• Transactions Basics
• Two psql sessions (two terminals) helpful for demos
• accounts table from the previous chapter

Phenomena

Four Isolation Levels in PostgreSQL

*PostgreSQL treats READ UNCOMMITTED as READ COMMITTED—no true dirty reads.

**PostgreSQL REPEATABLE READ also blocks many phantom cases via snapshot isolation—stricter than the SQL standard minimum.

Check current level:

SHOW transaction_isolation;
-- read committed

Set Isolation Level

For the session default:

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

For the next transaction only:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE name = 'Alice';
COMMIT;

Inside an already started transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;

Demo: Non-Repeatable Read (READ COMMITTED)

Session A:

BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice';

Session B:

UPDATE accounts SET balance = balance + 5 WHERE name = 'Alice';
COMMIT;

Session A again:

SELECT balance FROM accounts WHERE name = 'Alice';
COMMIT;

Second read can differ—non-repeatable read allowed at READ COMMITTED.

At REPEATABLE READ, the first snapshot repeats for the whole transaction.

REPEATABLE READ Snapshot

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE name = 'Alice';
-- other session updates and commits
SELECT * FROM accounts WHERE name = 'Alice';
-- same result as first select in this transaction
COMMIT;

PostgreSQL REPEATABLE READ uses snapshot isolation—no non-repeatable reads of committed data within one transaction.

SERIALIZABLE

Strictest—serializable snapshot isolation detects conflicts and may raise:

ERROR: could not serialize access due to concurrent update

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts;
COMMIT;

Retry the transaction on serialization failure in apps. Use sparingly—contention increases.

PostgreSQL vs MySQL Defaults

ORMs and drivers may assume different defaults—set explicitly when behavior matters.

Row-Level Locks

Explicit exclusive lock:

BEGIN;
 
SELECT id, balance FROM accounts WHERE name = 'Alice' FOR UPDATE;
 
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
 
COMMIT;

Shared lock (multiple readers, block writers):

BEGIN;
SELECT * FROM accounts WHERE name = 'Bob' FOR SHARE;
COMMIT;

Code explanation:

• Locks apply to rows returned by the query—index use matters
• NOWAIT fails immediately if lock unavailable; SKIP LOCKED skips locked rows—useful for job queues

SELECT id FROM jobs WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;

Deadlocks

Two transactions waiting on each other's locks—PostgreSQL detects and aborts one:

ERROR: deadlock detected
DETAIL: Process 123 waits for ShareLock on transaction 456; blocked by process 789...

PostgreSQL logs deadlock details—check server logs in production.

Inspect current locks (concept):

SELECT pid, mode, locktype, relation::regclass, granted
FROM pg_locks
WHERE relation IS NOT NULL
LIMIT 20;

Prevention habits:

• Lock rows in a consistent order (always id ascending)
• Keep transactions short
• Avoid user interaction inside transactions

Long Transactions and Bloat

Open transactions hold back VACUUM from reclaiming old row versions—tables grow (bloat), queries slow.

Check long runners:

SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start NULLS LAST;

Fix: COMMIT or ROLLBACK idle transactions—details in Performance, VACUUM, and Monitoring.

Advisory Locks (Optional)

Application-level locks without touching table rows:

SELECT pg_advisory_lock(42);
-- critical section work
SELECT pg_advisory_unlock(42);

Use for migrations or single-flight jobs across app instances.

FAQ

Which isolation level should apps use?

READ COMMITTED is PostgreSQL's default and works for most web apps. Use REPEATABLE READ or SERIALIZABLE when you need stronger guarantees and handle retries.

FOR UPDATE vs regular SELECT?

Regular SELECT takes no row lock in PostgreSQL (MVCC snapshot). FOR UPDATE blocks concurrent writers on those rows—use for "read balance, then debit" patterns.

Does PostgreSQL have gap locks like MySQL?

Not in the InnoDB next-key sense. SERIALIZABLE and predicate locking behave differently—test concurrent scenarios.

What is SKIP LOCKED?

Workers grab the next unlocked job row without waiting—great for parallel queue consumers.

How do I debug locks?

pg_locks, pg_stat_activity, and log_lock_waits = on in postgresql.conf (dev/staging).

Deadlock in production?

Retry the whole transaction once or twice; fix lock ordering if deadlocks repeat.