transactions • Expert

Transaction Isolation Levels & Concurrency Anomalies

SQL standards define 4 isolation levels to prevent concurrency anomalies: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

Intuitive Mental Model

The Soundproof Meeting Rooms: Read Uncommitted has glass walls with speakers; Serializable puts every transaction into a private locked vault one at a time.

SQL DDL / DML QueryPostgreSQL / MySQL

-- Set transaction isolation level:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Concurrency Anomalies Matrix:
-- 1. Dirty Read: Transaction reads uncommitted data written by another transaction.
-- 2. Non-Repeatable Read: Reading the same row twice yields different column values.
-- 3. Phantom Read: A range query executed twice returns new inserted rows.
-- 4. Serialization Anomaly / Write Skew: Concurrent transactions violate global business constraints.

Key Architectural Takeaways

•Read Committed (PostgreSQL / Oracle default): Prevents Dirty Reads by taking a snapshot at each SQL statement.
•Repeatable Read (MySQL InnoDB default): Prevents Dirty and Non-Repeatable Reads by taking a snapshot at the start of the transaction.
•Serializable: Full mathematical equivalence to serial execution, using SSI (Serializable Snapshot Isolation) or 2PL.

Common Production Mistake

Assuming Repeatable Read prevents Write Skew anomalies (e.g. two doctors simultaneously checking out of on-call duty, leaving 0 on-call).