Weak Isolation Levels: Read Committed and Snapshot Isolation

Key Insight

Due to the performance cost of full serializable isolation, many databases employ weaker isolation levels that protect against a subset of concurrency issues, making application development more manageable than having no transactions at all. The most common and basic isolation level is 'read committed,' which makes two core guarantees: preventing dirty reads and preventing dirty writes. These guarantees are crucial but still leave some concurrency problems unaddressed.

To prevent 'dirty reads,' a transaction will only see data that has already been committed by another transaction; any uncommitted writes are invisible. This prevents applications from seeing partially updated or subsequently rolled-back data. 'Dirty writes' are prevented by ensuring one transaction does not overwrite data that another transaction has written but not yet committed. Databases typically implement this using row-level locks, where a transaction holds an exclusive lock on an object it's modifying until it commits or aborts, forcing other potential writers to wait.

While read committed prevents dirty reads and writes, it does not safeguard against 'read skew' (also known as 'nonrepeatable reads'), where a transaction might observe different parts of the database at different points in time, potentially seeing temporary inconsistencies. For example, a user checking account balances during a transfer might temporarily see a total that appears to have lost money. 'Snapshot isolation' (often called 'repeatable read' in PostgreSQL and MySQL, or 'serializable' in Oracle) addresses read skew by allowing each transaction to read from a consistent snapshot of the database as it existed at the start of that transaction. This is commonly implemented using multi-version concurrency control (MVCC), where the database retains multiple versions of data objects, allowing readers to access older consistent states without blocking concurrent writers.