Explain transaction isolation levels and the anomalies they prevent.

Summary

Read Uncommitted allows dirty reads; Read Committed prevents dirty reads; Repeatable Read prevents non-repeatable reads (and in Postgres/MySQL InnoDB mostly phantoms too); Serializable prevents all anomalies, including write skew, making transactions behave as if run one at a time.

Longer Answer

Defaults: PostgreSQL uses Read Committed, MySQL InnoDB uses Repeatable Read. Anomalies: dirty read (seeing uncommitted data), non-repeatable read (a row changes between reads), phantom (new rows appear in a range), lost update, and write skew (two transactions each read and then write based on stale assumptions, e.g., two doctors both going off call). Serializable in Postgres uses SSI and may abort transactions with serialization failures that clients must retry.

Key Points

• Read Committed (Postgres default)
• Repeatable Read (MySQL default)
• Serializable prevents write skew
• Retry serialization failures

Comparison / Tradeoffs