Transaction Isolation Levels (Deep Dive)

Interview Definition:

"When many people use a database at the same time, their actions can interfere with each other. Isolation levels are just settings that control how strictly the database keeps these simultaneous actions separated to prevent data mix-ups."

1. Concurrency Anomalies (Read Phenomena)

A. Dirty Read

A transaction reads data written by a concurrent uncommitted transaction. If that other transaction rolls back, the first transaction has read data that "never existed".

    Time | Transaction 1 (Writer)       | Transaction 2 (Reader)
    --------------------------------------------------------------
     t1  | BEGIN;                       |
     t2  | UPDATE users SET age=30;     | 
     t3  |                              | BEGIN;
     t4  |                              | SELECT age FROM users; -> Returns 30
     t5  | ROLLBACK;                    |
     t6  |                              | COMMIT;
    --------------------------------------------------------------
    T2 used the age 30 to calculate something, but T1 rolled back. 
    The age is actually 25. T2 operated on "dirty" data.

B. Non-Repeatable Read (Fuzzy Read)

A transaction reads the same row twice and gets different results each time because a concurrent transaction committed a modification in between the reads.

    Time | Transaction 1 (Reader)       | Transaction 2 (Writer)
    --------------------------------------------------------------
     t1  | BEGIN;                       |
     t2  | SELECT age FROM users (25)   | 
     t3  |                              | BEGIN;
     t4  |                              | UPDATE users SET age=30;
     t5  |                              | COMMIT;
     t6  | SELECT age FROM users (30)   | <-- Wait, it changed!
     t7  | COMMIT;                      |
    --------------------------------------------------------------
    T1 cannot trust its own reads within the same transaction.

C. Phantom Read

Similar to a non-repeatable read, but involves a set of rows based on a condition rather than a specific row. A concurrent transaction inserts or deletes rows matching the condition.

    Time | Transaction 1 (Reader)       | Transaction 2 (Writer)
    --------------------------------------------------------------
     t1  | BEGIN;                       |
     t2  | SELECT COUNT(*) WHERE age>20 | -> Returns 5
     t3  |                              | BEGIN;
     t4  |                              | INSERT INTO users (age) VALUES (25);
     t5  |                              | COMMIT;
     t6  | SELECT COUNT(*) WHERE age>20 | -> Returns 6! <-- Phantom row!
     t7  | COMMIT;                      |
    --------------------------------------------------------------

2. The 4 Standard Isolation Levels

Different isolation levels prevent different phenomena by taking stronger locks or maintaining multiple versions of data. There is always a trade-off: Higher isolation = Lower concurrency/performance.

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Concurrency Speed
Read Uncommitted	Allowed	Allowed	Allowed	Highest
Read Committed	Prevented	Allowed	Allowed	High (Default in Postgres)
Repeatable Read	Prevented	Prevented	Allowed	Medium (Default in MySQL)
Serializable	Prevented	Prevented	Prevented	Lowest

1. Read Uncommitted

Mechanism: No read locks are taken.
Use Case: Almost never used, except for rough analytical queries where slight inaccuracy is fine.

2. Read Committed

Mechanism: A query only sees data committed before the query began. If another transaction holds a write lock on a row, this transaction will wait for it to commit/rollback before reading.
Use Case: The default for most databases (PostgreSQL, SQL Server). Good balance of safety and speed.

3. Repeatable Read

Mechanism: A query only sees data committed before the entire transaction began (not just the current query). In MySQL InnoDB, it uses Gap Locks to prevent insertions, effectively preventing Phantom Reads as well in most cases.
Use Case: Default for MySQL InnoDB. Used when calculating aggregates or generating reports where the data snapshot must remain completely static throughout the transaction.

4. Serializable

Mechanism: Executes transactions in a way that is equivalent to running them sequentially (one after the other). Can use table-level locks, range locks, or SSI (Serializable Snapshot Isolation).
Use Case: Highly critical financial transactions, but prone to high lock contention and deadlocks.

3. MVCC (Multi-Version Concurrency Control)

Modern databases (PostgreSQL, MySQL InnoDB, Oracle) do not strictly use locks for everything because "readers waiting for writers" destroys performance. Instead, they use MVCC.

How MVCC Works:

When a row is updated, it is not overwritten immediately. Instead, a new version of the row is created with a timestamp (or transaction ID).

    Transaction ID 100 updates Row A from ₹50 to ₹60.
    
    Database physically stores:
    [Row A | Value: ₹50 | Created_By: 1 | Deleted_By: 100 ] <-- Old version
    [Row A | Value: ₹60 | Created_By: 100 | Deleted_By: NULL] <-- New version

If Transaction 90 wants to read Row A, it sees its ID is less than 100, so it reads the OLD version (₹50). Readers don't block writers!
If Transaction 105 wants to read Row A, it reads the NEW version (₹60).
Periodically, a background process (like VACUUM in Postgres) removes old row versions that no active transaction can possibly see anymore.

03-transaction-isolation-levels.md