Day 12: Transactions & Concurrency - PostgreSQL in 15 Days

📋 Outline

1. ACID and transaction basics
2. Isolation levels in PostgreSQL
3. MVCC internals and snapshots
4. Locks: row, table, advisory
5. Deadlocks and how to avoid them
6. Long-running transactions, bloat, and autovacuum
7. Practical patterns (retry, idempotency, queues)
8. Challenge
9. Summary

---

🧱 ACID and Transaction Basics

• Atomicity, Consistency, Isolation, Durability
• BEGIN/COMMIT/ROLLBACK; savepoints for partial rollback

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;

---

🔒 Isolation Levels

PostgreSQL supports: READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

• READ COMMITTED: each statement sees committed data at start of statement
• REPEATABLE READ: snapshot for entire transaction; prevents non-repeatable reads, phantom reads by predicate locks
• SERIALIZABLE: guarantees serializability; may raise serialization failures that must be retried

---

📸 MVCC Internals

• Each row has xmin/xmax transaction IDs; readers see a snapshot
• Vacuum removes dead tuples; prevents readers from blocking writers
• Visibility functions: xmin, xmax, pg_visible_in_snapshot(), txid_current()

---

🔐 Locks Overview

• Row-level locks: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE

SELECT * FROM orders WHERE id = 42 FOR UPDATE; -- prevents concurrent updates

• Table locks: ACCESS SHARE/EXCLUSIVE, etc. Mostly implicit
• Advisory locks: application-controlled, via bigints or key tuples

SELECT pg_advisory_lock(12345);
-- critical section
SELECT pg_advisory_unlock(12345);

Inspect locks:

SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks WHERE NOT granted OR mode LIKE '%Exclusive%';

---

⚠️ Deadlocks and Avoidance

• Acquire locks in consistent order
• Keep transactions short; avoid user waits inside
• Add indexes to reduce lock duration
• Use NOWAIT/SKIP LOCKED for queues

-- Avoid blocking consumers
SELECT * FROM jobs WHERE status = 'ready'
FOR UPDATE SKIP LOCKED LIMIT 10;

---

🐘 Long Transactions, Bloat, Autovacuum

• Long transactions prevent vacuum cleanup -> table/index bloat
• Monitor with pg_stat_activity, age(datfrozenxid), n_dead_tup
• Use idle_in_transaction_session_timeout; cancel bad actors

SET idle_in_transaction_session_timeout = '15s';

---

🧰 Practical Patterns

• Retry on serialization failures (SQLSTATE 40001)
• Idempotent writes with ON CONFLICT
• Transactional outbox for reliable events

-- Idempotent upsert
INSERT INTO payments(id, amount, status)
VALUES ($1, $2, 'processed')
ON CONFLICT (id) DO UPDATE
SET status = EXCLUDED.status, amount = EXCLUDED.amount;

---

💪 Challenge

• Implement a job queue using SKIP LOCKED and advisory locks
• Compare latency under READ COMMITTED vs REPEATABLE READ
• Create a dashboard query to spot blockers using pg_locks and pg_stat_activity

---

📚 Summary

• Choose isolation level per workload; default is often fine
• Keep transactions short, index predicates, consider SKIP LOCKED
• Monitor long transactions and bloat; tune autovacuum

Tomorrow (Day 13): Security Best Practices.

PostgreSQL #SQL #Transactions #Concurrency #Day12