Transactions & Concurrency

In multi-user databases, correct concurrent access is critical. This lesson covers ACID properties, transaction isolation levels, deadlocks, advisory locks, optimistic vs pessimistic locking, and how PostgreSQL's MVCC works under the hood.

---

ACID Properties

Every transaction in PostgreSQL guarantees:

Property	Description
Atomicity	All statements succeed or all are rolled back
Consistency	The database moves from one valid state to another
Isolation	Concurrent transactions do not interfere
Durability	Committed data survives crashes

Basic Transaction

BEGIN;

UPDATE accounts SET balance = balance - 100.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE id = 2;

-- If everything is fine:
COMMIT;

-- If something goes wrong:
-- ROLLBACK;

Savepoints

BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 250.00);

SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 999, 1);
-- Oops, product 999 does not exist
ROLLBACK TO SAVEPOINT before_items;

-- Continue with valid items
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 42, 2);
COMMIT;

---

Isolation Levels