Isolation levels and concurrency anomalies

Isolation decides what transactions running at the same time can see of each other. Full isolation is expensive, so databases offer isolation levels. Read committed, the PostgreSQL default, stops dirty reads (seeing changes that were never committed).

But a value can still change between two reads in the same transaction. Repeatable read, or snapshot isolation, gives each transaction a frozen snapshot of the data. It still allows write skew.

For example, two doctors each check that someone else is on call, and both go off duty. Serializable behaves as if transactions ran one at a time. The cost is aborted transactions that you must retry.

Learn these anomalies by name: dirty read, lost update, write skew. Learn the fixes too, such as SELECT ... FOR UPDATE (lock the rows you read) or an atomic UPDATE balance = balance - 10.