17.5 What are isolation levels?

On the train: The railway can let clerks work fully in parallel (fast, some mistakes), check each other's finished work only (fewer mistakes), or behave as if only one clerk works at a time (no mistakes, more waiting).

In the system: The isolation level decides how much concurrent transactions can affect each other. The SQL standard defines four, by the anomalies they allow:

Serializable means the result is as if transactions ran one at a time, in some order. It prevents lost updates and write skew too.

What real databases do (check your database's docs, because they differ):

• PostgreSQL defaults to Read Committed. Its Repeatable Read uses snapshot isolation (each transaction sees a frozen snapshot), which also prevents phantoms, and its Serializable detects dangerous patterns and aborts one transaction, which you must retry.
• MySQL InnoDB defaults to Repeatable Read.

Choosing:

• Read Committed plus targeted fixes (atomic updates, locks, unique constraints) is the common, efficient default.
• Serializable for complex invariants that are hard to protect by hand, accepting more aborts and retries.

Memory code: "The stronger the isolation, the more it's like one clerk at a time, and the more you wait or retry."

---