Concurrency & Isolation
Lost updates, dirty reads, phantoms, write skew; isolation levels; MVCC; locks and deadlocks.
When two transactions interleave, five specific things can go wrong — lost update, dirty read, non-repeatable read, phantom read, write skew — and each has a name because each has a different cause and a different fix.
Read Uncommitted, Read Committed, Repeatable Read and Serializable are four points on a dial between throughput and anomalies; PostgreSQL implements three of them, stronger than the standard requires, and the right one depends on which anomaly your code can survive.
Instead of overwriting a row, an update writes a new version and marks the old one superseded; each transaction’s snapshot decides which versions it can see — so readers never block writers, and dead versions have to be vacuumed.
Row and table locks serialise conflicting writers; a deadlock is a cycle in who-waits-for-whom, which the database detects and breaks by killing one side — and which you prevent by acquiring locks in a consistent order.