Transactions & Isolation Levels

ACID

• Atomicity — a transaction's writes all happen or none do.
• Consistency — a transaction moves the database from one valid state to another (constraints hold before and after).
• Isolation — concurrent transactions don't see each other's uncommitted changes (to a degree set by the isolation level).
• Durability — once committed, a transaction's changes survive a crash.

Isolation levels, weakest to strongest

• Read Uncommitted — can see other transactions' uncommitted writes ("dirty reads").
• Read Committed — only sees committed data, but a value can change between two reads in the same transaction ("non-repeatable read").
• Repeatable Read — the same query returns the same rows for the whole transaction, but new rows matching a filter can still appear ("phantom read").
• Serializable — behaves as if transactions ran one at a time; strongest guarantee, most contention/lowest throughput.

The trade-off

Stronger isolation prevents more anomalies but requires more locking (or retries), reducing concurrent throughput — most applications default to Read Committed and opt into stronger isolation only where a specific anomaly would cause a real bug.