Isolation Levels: The 'I' in ACID

In a distributed or highly concurrent system, multiple transactions will attempt to read and write the exact same rows simultaneously. Isolation levels define the rules of engagement for these conflicts. The SQL standard defines four levels, each trading performance for stricter data integrity.

1. Read Uncommitted & The Dirty Read

A Dirty Read happens when a transaction reads data that has been written by another transaction but not yet committed. If the other transaction rolls back, your system just made decisions based on phantom data.

Note on Postgres: The PostgreSQL engine strictly enforces data integrity at a baseline. Even if you explicitly set ISOLATION LEVEL READ UNCOMMITTED, Postgres will silently upgrade it to READ COMMITTED. It physically cannot perform a dirty read, so we skip straight to the next level.

2. Read Committed & The Non-Repeatable Read

This is the default isolation level in PostgreSQL. It guarantees you will only read committed data. However, it allows Non-Repeatable Reads: reading the same row twice in one transaction but getting different data because another transaction updated it in between your reads.

The Sandbox

Let's prove this. Use the terminals below to simulate two concurrent users hitting your database. Both terminals are connected to the same WASM Postgres instance.

Step 1: In Terminal A, create a dummy table and insert a row. ```sql CREATE TABLE accounts (id INT, balance INT); INSERT INTO accounts VALUES (1, 1000); ```

Step 2: Start a transaction in Terminal A and check the balance. ```sql BEGIN; SELECT balance FROM accounts WHERE id = 1; ```

Step 3: In Terminal B, update that exact account and commit. ```sql UPDATE accounts SET balance = 500 WHERE id = 1; ```

Step 4: Back in Terminal A, run the SELECT query again. ```sql SELECT balance FROM accounts WHERE id = 1; COMMIT; ```

// Lab 01: Concurrency Conflict

[ Booting Execution Engine... ]

Notice the result. Even though Terminal A was inside a continuous transaction, the data shifted underneath it.

3. Repeatable Read & Snapshot Isolation

To fix the shifting data problem, we step up to REPEATABLE READ. When a transaction starts in this level, it takes a freeze-frame snapshot of the database. It will only ever see data committed before the transaction began.

Let's run the exact same scenario from above, but upgrade the engine's isolation level.

Step 1: In Terminal A, begin a new transaction with strict isolation. ```sql BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id = 1; ```

Step 2: In Terminal B, try to sabotage the read again. ```sql UPDATE accounts SET balance = 9999 WHERE id = 1; ```

Step 3: Back in Terminal A, read the balance. ```sql SELECT balance FROM accounts WHERE id = 1; COMMIT; ```

// Lab 02: Snapshot Isolation

[ Booting Execution Engine... ]

Result: Terminal A still sees 500 (or whatever the balance was when the transaction started). It is completely insulated from Terminal B's background noise.

4. Serializable & Write Skew

SERIALIZABLE is the strictest level. It guarantees that if transactions run concurrently, the final database state will be identical to if they had run sequentially (one after the other).

It prevents complex anomalies like Write Skew.

Imagine a hospital system. The rule is: At least one doctor must be on call. Currently, both Alice and Bob are on call. They both try to book time off at the exact same millisecond.

Step 1: Setup the system in Terminal A. ```sql CREATE TABLE doctors (name VARCHAR(50), on_call BOOLEAN); INSERT INTO doctors VALUES ('Alice', true), ('Bob', true); ```

Step 2: Both doctors begin transactions simultaneously. Terminal A (Alice): ```sql BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count( * ) FROM doctors WHERE on_call = true; ``` Terminal B (Bob): ```sql BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count( * ) FROM doctors WHERE on_call = true; ``` (Both see count = 2. The rule is safe).

Step 3: They both update their own status and commit. Terminal A (Alice): ```sql UPDATE doctors SET on_call = false WHERE name = 'Alice'; COMMIT; ```

Terminal B (Bob): ```sql UPDATE doctors SET on_call = false WHERE name = 'Bob'; COMMIT; ```

// Lab 03: Serialization Engine

[ Booting Execution Engine... ]

When Terminal B tries to commit, the Postgres engine detects the serialization conflict and throws a fatal error: ERROR: could not serialize access due to read/write dependencies among transactions.

The database intentionally aborted Bob's transaction to protect the system's integrity, ensuring at least one doctor remains on call.