Module 13: Transactions and concurrency

Keep money correct when things fail or run at once: ACID, isolation levels, MVCC, locks and deadlocks.

1. Transactions and ACID: BEGIN … COMMIT makes statements all-or-nothing; ROLLBACK undoes them; SAVEPOINT lets you undo part; an error aborts the whole transaction.
2. Concurrency problems: lost updates and friends: Dirty read, non-repeatable read, phantom and lost update; Postgres never allows dirty reads, and relative or guarded UPDATEs stop lost updates.
3. Isolation levels and MVCC: READ COMMITTED (default) takes a snapshot per statement, REPEATABLE READ one per transaction, SERIALIZABLE also stops write skew; MVCC keeps row versions so readers and writers never block.
4. Locks, FOR UPDATE and deadlocks: Writes lock rows until commit; FOR UPDATE locks what you read, SKIP LOCKED skips busy rows, a fixed lock order prevents deadlocks, and a version column gives optimistic locking.

Ends with a 13-question checkpoint (80% passes) and a project: A safe UPI transfer.