Transazioni Isolation — Cheat Sheet

Tags: database, transactions, acid, isolation-levels, concurrency, sql, postgresql

Level: intermediate

TL;DR: Guide to transaction isolation levels, concurrency anomalies (dirty/non-repeatable reads, phantoms, write skew), and real-world trade-offs between consistency and performance.

Key concepts

ACID Isolation guarantees how concurrent transactions observe each other's changes.

Read Uncommitted allows dirty reads; transactions see uncommitted changes from others.

Read Committed prevents dirty reads; queries only see data committed before statement execution.

Repeatable Read prevents non-repeatable reads; reads stay consistent throughout the transaction.

Serializable provides full isolation equivalent to sequential transaction execution.

Dirty read: reading uncommitted data that is later rolled back.

Non-repeatable read: re-reading the same row within a transaction returns altered values.

Phantom read: re-executing a range query returns newly inserted or deleted rows.

Write skew: concurrent transactions evaluate overlapping data, update disjoint rows, and violate constraints.

Engines use Two-Phase Locking (2PL) or Multi-Version Concurrency Control (MVCC) to implement isolation.

Typical questions and ready answers

Q: What is the difference between Read Committed and Repeatable Read?R: In Read Committed, each individual query inside my transaction reads the latest committed snapshot. In Repeatable Read, the entire transaction uses a single snapshot taken when the transaction starts.

Q: How does MVCC avoid blocking reads during writes?R: MVCC maintains multiple immutable row versions with creation and expiration transaction IDs. My read queries scan row versions visible to their snapshot without acquiring shared read locks.

Q: What is write skew and which isolation level prevents it?R: Write skew occurs when two concurrent transactions read overlapping state, make disjoint updates, and break a global business invariant. Repeatable Read does not prevent it; Serializable or explicit locking is required.

Pitfalls and common mistakes

Assuming default database isolation is always Serializable; PostgreSQL and Oracle default to Read Committed.

Expecting Repeatable Read to prevent phantom rows in MySQL InnoDB without next-key locks.

Relying on application-level checks without SELECT FOR UPDATE under Read Committed.

Overusing Serializable isolation, leading to frequent serialization failures and retry overhead.

Long-running transactions holding MVCC snapshots and preventing table bloat cleanup through vacuuming.

Snippets and commandsSQL

```-- Check and set isolation level in PostgreSQL

SHOW default_transaction_isolation;

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Explicit pessimistic locking to prevent write skew in Read Committed

SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 42;

COMMIT;

-- Transaction isolation level setting per session

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

```

To review

[ ] Understand snapshot isolation anomalies vs true serializability (SSI).

[ ] Review how PostgreSQL vacuuming is affected by open transaction snapshots.

[ ] Compare 2PL locking mechanisms in MySQL InnoDB vs SSI in PostgreSQL.

[x] Memorize standard ANSI SQL isolation levels and their permitted anomalies.

References

ANSI/ISO SQL-92 Transaction Isolation Specifications

PostgreSQL Documentation: Concurrency Control and Isolation Levels

Designing Data-Intensive Applications (Martin Kleppmann), Chapter 7: Transactions