Transactions, Isolation Levels & Concurrency Control
Deep dive into ACID guarantees, MVCC, isolation levels, savepoints, and advisory locks.
🎯 What You'll Learn
- The four isolation levels and which anomalies each prevents
- Dirty reads, non-repeatable reads, and phantom reads explained
- How MVCC allows readers and writers to coexist without blocking
- Savepoints for partial rollback within transactions
- Advisory locks for application-level coordination
🔒 Isolation Levels — Choosing Your Trade-Off
Isolation levels control the trade-off between correctness and performance. Higher isolation means fewer data anomalies but more locking, retries, and overhead. Think of it like noise-cancelling headphones — maximum isolation blocks everything but slows you down.
Isolation Levels
READ UNCOMMITTED through SERIALIZABLE
-- Transaction Isolation Levels
-- Controls what other transactions' changes you can see
-- READ UNCOMMITTED (weakest — "dirty reads" allowed)
-- You can see data that another transaction hasn't committed yet
-- Rarely used — if that transaction rolls back, you read phantom data
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- READ COMMITTED (PostgreSQL default)
-- You only see committed data, but re-reading may show new commits
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- sees $1
...👻 The Three Anomalies
Each isolation level prevents specific data anomalies. Understanding these helps you choose the minimum isolation level that's safe for your use case.
⚠️ Common Mistake
Using READ UNCOMMITTED "for performance." The speed gain is negligible, but you risk reading data that never existed (rolled-back transactions). READ COMMITTED is almost always the right starting point.
Transaction Anomalies
Dirty reads, non-repeatable reads, and phantom reads
-- The three anomalies isolation levels prevent:
-- 1. DIRTY READ: reading uncommitted data
-- Session A:
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- (not committed yet)
-- Session B (READ UNCOMMITTED):
SELECT balance FROM accounts WHERE id = 1;
-- Sees 0 — but Session A might ROLLBACK!
-- 2. NON-REPEATABLE READ: same query, different results
-- Session A:
BEGIN;
SELECT salary FROM employees WHERE id = 5; -- $80,000
-- Session B commits: UPDATE employees SET salary = 90000 WHERE
...📸 MVCC — Snapshots Instead of Locks
PostgreSQL (and InnoDB) use Multi-Version Concurrency Control: instead of locking rows, they keep old versions. Each transaction sees a consistent snapshot. Readers never block writers, and writers never block readers.
💡 Pro Tip — VACUUM Is Essential
MVCC creates dead tuples (old row versions). Without regular VACUUM, your table bloats and queries slow down. PostgreSQL's autovacuum handles this automatically, but high-write tables may need tuning.
MVCC Internals
How PostgreSQL manages concurrent access with snapshots
-- MVCC: Multi-Version Concurrency Control
-- How PostgreSQL avoids locks for readers
-- PostgreSQL keeps old row versions (tuples)
-- Each row has: xmin (created by txn), xmax (deleted by txn)
-- Transaction 100 inserts a row:
-- tuple: (data, xmin=100, xmax=null)
-- Transaction 200 updates the same row:
-- old tuple: (old_data, xmin=100, xmax=200) ← marked "dead"
-- new tuple: (new_data, xmin=200, xmax=null) ← current
-- Transaction 150 (started before 200) still sees the OLD tuple
-- beca
...💾 Savepoints — Partial Rollback
Savepoints let you undo part of a transaction without losing everything — like an "undo" button within a larger operation. Essential for multi-step workflows where one step might fail but others should persist.
Savepoints in Action
Partial rollback for multi-step transactions
-- Savepoints: partial rollback within a transaction
BEGIN;
-- Step 1: Create the order
INSERT INTO orders (customer_id, total, status)
VALUES (42, 299.99, 'processing')
RETURNING id; -- assume returns id = 1001
-- Step 2: Try to apply a discount coupon
SAVEPOINT before_coupon;
UPDATE coupons SET used = TRUE WHERE code = 'SAVE20';
-- If coupon doesn't exist or is already used:
-- ROLLBACK TO before_coupon;
-- If coupon is valid, continue:
UPDATE orders SET total = total * 0.80 WHERE id = 1
...🔑 Advisory Locks
Advisory locks are application-defined locks managed by the database but not tied to any table. They coordinate work between sessions — like a "busy" flag that prevents duplicate processing.
Advisory Locks
Application-level coordination without table locks
-- Advisory Locks: application-level coordination
-- Use when you need to serialize a specific business operation
-- Example: prevent double-processing of a job
-- Lock key = job_id
-- Try to acquire lock (non-blocking)
SELECT pg_try_advisory_lock(12345) AS got_lock;
-- Returns TRUE if acquired, FALSE if another session holds it
-- If got_lock = TRUE, process the job:
UPDATE jobs SET status = 'processing' WHERE id = 12345;
-- ... do work ...
UPDATE jobs SET status = 'completed' WHERE id = 123
...📋 Quick Reference
| Concept | Key Point |
|---|---|
| READ COMMITTED | Default — sees only committed data |
| REPEATABLE READ | Snapshot frozen at txn start |
| SERIALIZABLE | Strongest — detects all conflicts |
| SAVEPOINT | Partial rollback within a txn |
| VACUUM | Reclaims dead MVCC tuples |
🎉 Lesson Complete!
You now deeply understand transaction isolation, MVCC, and concurrency tools. Next, learn about locking and deadlock prevention!
Sign up for free to track which lessons you've completed and get learning reminders.