Transaction Isolation

Isolation controls how and when changes made by one transaction become visible to other concurrent transactions.

Concept

Isolation controls how and when changes made by one transaction become visible to other concurrent transactions. Perfect isolation (as if transactions ran one after another) is expensive; weaker isolation is faster but allows anomalies. The SQL standard defines four isolation levels in terms of which anomalies they prevent.

Why It Matters

Concurrency bugs are rare, intermittent, and hard to reproduce — double bookings, overdrawn accounts, wrong inventory counts. Choosing the right isolation level (and understanding what your database's default actually guarantees) prevents them.

Syntax

-- Standard (PostgreSQL, SQL Server, MySQL, Oracle accept this form)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- PostgreSQL: per transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- MySQL: next transaction / session
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- SQL Server: session setting
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;   -- requires ALLOW_SNAPSHOT_ISOLATION ON for the database

Example

22.1 The concurrency anomalies Dirty read — reading data written by another transaction that has not committed (and may roll back).

Non-repeatable read — reading the same row twice in one transaction and getting different values because another transaction committed an update in between.

Phantom read — re-running the same query with a range condition returns new or missing rows because another transaction inserted/deleted matching rows.

Lost update — two transactions read the same value, both compute a new value, and the second write overwrites the first.

Write skew (allowed by snapshot isolation) — two transactions read overlapping data, make decisions based on it, and write different rows, jointly violating a rule.

22.2 Isolation levels and anomalies (SQL standard) Fastest, least safe

Default in PostgreSQL, SQL Server, Oracle Possible (standard) Default in MySQL InnoDB

Equivalent to some serial order; may abort transactions Lost updates: prevented at SERIALIZABLE; prevented at REPEATABLE READ in PostgreSQL (the second writer gets a serialization error) and SQL Server (locks); not automatically prevented in MySQL REPEATABLE READ for plain read-then-write logic, nor at READ COMMITTED anywhere — use atomic updates or SELECT... FOR UPDATE.

22.3 How databases really implement isolation Locking (pessimistic): readers take shared locks, writers take exclusive locks; conflicting transactions wait. SQL Server's default READ COMMITTED uses short shared locks (readers can block on writers).

MVCC (Multi-Version Concurrency Control): writers create new row versions; readers see a consistent snapshot of committed data as of a point in time. Readers do not block writers and writers do not block readers. Used by PostgreSQL, Oracle, MySQL InnoDB, and SQL Server (when snapshot options are enabled).

MVCC. READ UNCOMMITTED behaves as READ COMMITTED. REPEATABLE READ = snapshot isolation (also prevents phantoms). SERIALIZABLE = Serializable Snapshot Isolation (SSI), which aborts conflicting transactions with serialization errors to be retried.

MVCC consistent reads; locking reads (FOR UPDATE) use next-key/gap locks that prevent phantoms for those reads.

READ COMMITTED (locking) Options: READ_COMMITTED_SNAPSHOT ON (statement-level MVCC, default in Azure SQL Database) and SNAPSHOT isolation (transaction-level MVCC).

MVCC via undo. Supports READ COMMITTED and SERIALIZABLE (actually snapshot isolation) only.

Database-level locking; one writer at a time (WAL mode allows concurrent readers).

Snapshot isolation vs Serializable: snapshot isolation prevents dirty reads, non-repeatable reads, phantoms, and lost updates (first-committer-wins) but allows write skew. True SERIALIZABLE prevents write skew too.

22.4 Practical example: preventing double booking 22.5 Choosing an isolation level Typical OLTP web application

READ COMMITTED (default) + atomic updates / row locks where needed

Reports needing a consistent view across several queries REPEATABLE READ / SNAPSHOT (read-only transaction)

Complex invariants across rows (on-call rule, budget limits)

SERIALIZABLE with retry logic, or explicit locking Approximate monitoring queries on busy systems

READ UNCOMMITTED / NOLOCK only if dirty data is acceptable (rarely a good idea)

Time  T1                                         T2
1     BEGIN;
2     UPDATE accounts SET balance = 0
      WHERE id = 1;          (was 1000)
3                                                BEGIN;
4                                                SELECT balance FROM accounts WHERE id = 1;
                                                 → 0   (dirty: uncommitted)
5     ROLLBACK;              (balance back to 1000)
6                                                decides "account empty" → wrong decision

Time  T1                                         T2
1     BEGIN;
2     SELECT price FROM products WHERE id = 101; → 75000
3                                                UPDATE products SET price = 70000 WHERE id = 101;
4                                                COMMIT;
5     SELECT price FROM products WHERE id = 101; → 70000   (changed within T1)

Time  T1                                         T2
1     BEGIN;
2     SELECT COUNT(*) FROM orders
      WHERE status = 'PENDING';  → 1
3                                                INSERT INTO orders (... status 'PENDING' ...);
4                                                COMMIT;
5     SELECT COUNT(*) FROM orders
      WHERE status = 'PENDING';  → 2   (a "phantom" row appeared)

Time  T1                                   T2
1     read stock = 10
2                                          read stock = 10
3     write stock = 10 - 3 = 7
4     COMMIT
5                                          write stock = 10 - 2 = 8   (T1's decrement is lost; should be 5)
6                                          COMMIT

Rule: at least one doctor must be on call.  Alice and Bob are both on call.
T1: SELECT COUNT(*) on call → 2; UPDATE doctors SET on_call = false WHERE name = 'Alice'
T2: SELECT COUNT(*) on call → 2; UPDATE doctors SET on_call = false WHERE name = 'Bob'
Both commit under snapshot isolation → nobody on call.

CREATE TABLE seats (
    screening_id INT,
    seat_no      VARCHAR(5),
    booked_by    INT NULL,
    PRIMARY KEY (screening_id, seat_no)
);

-- Approach 1: atomic conditional update (works at READ COMMITTED)
UPDATE seats
SET booked_by = 42
WHERE screening_id = 7 AND seat_no = 'A10' AND booked_by IS NULL;
-- rowcount = 1 → booked; rowcount = 0 → someone else got it

-- Approach 2: pessimistic row lock
BEGIN;
SELECT booked_by FROM seats WHERE screening_id = 7 AND seat_no = 'A10' FOR UPDATE;
-- if NULL:
UPDATE seats SET booked_by = 42 WHERE screening_id = 7 AND seat_no = 'A10';
COMMIT;

-- Approach 3: unique constraint on a bookings table (database guarantees one winner)
CREATE TABLE seat_bookings (
    screening_id INT, seat_no VARCHAR(5), customer_id INT,
    CONSTRAINT uq_seat UNIQUE (screening_id, seat_no)
);
-- The second concurrent INSERT fails with a duplicate key error.

-- Approach 4: SERIALIZABLE + retry on serialization failure (PostgreSQL SQLSTATE 40001)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM seat_bookings WHERE screening_id = 7 AND seat_no = 'A10';
INSERT INTO seat_bookings VALUES (7, 'A10', 42);
COMMIT;   -- may fail with "could not serialize access";
…

Expected Result

Two sessions in PostgreSQL at REPEATABLE READ:

Under READ COMMITTED, Session A's second SELECT would return 70000.00 and its UPDATE would succeed, producing 70100.00.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT price FROM products WHERE product_id = 101;    -- 75000.00

-- Session B
UPDATE products SET price = 70000 WHERE product_id = 101;   -- autocommits

-- Session A
SELECT price FROM products WHERE product_id = 101;    -- still 75000.00 (snapshot)
UPDATE products SET price = price + 100 WHERE product_id = 101;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;

Common Mistakes

Assuming the default isolation level prevents all anomalies.

Assuming the same level name means the same behavior on every DBMS.

Using WITH (NOLOCK) in SQL Server everywhere "for performance" — it allows dirty reads, and can even skip or double-read rows.

Using SERIALIZABLE without retry logic.

Read-then-write logic at READ COMMITTED without locks.

Best Practices

Know your DBMS's default and its exact semantics.

Prefer atomic single-statement updates and constraints to enforce correctness.

Use SELECT ... FOR UPDATE for read-modify-write sequences.

Use SERIALIZABLE for complex invariants, with automatic retries.

Keep transactions short to reduce contention.

Interview Relevance

Senior-level favorite: "Explain isolation levels", "Dirty read vs non-repeatable read vs phantom read", "What is MVCC?", "What is the default isolation level in PostgreSQL/MySQL/SQL Server?", "How do you prevent lost updates?", "What is write skew?".

Practice Questions

Draw a timeline that demonstrates a phantom read.

Which isolation level is the minimum to prevent non-repeatable reads?

Explain why MVCC readers do not block writers.

Design a concurrency-safe "withdraw" statement that never allows a negative balance.

Explain write skew with an example from inventory or scheduling.

Quiz Checkpoint 4 (Modules 18 – 22)

Q1. (True/False) A normal view stores a copy of the data returned by its query.

Answer: False. Only materialized views store results.

Q2. (Multiple choice) How many clustered indexes can a table have? A) 0 B) 1 C) 16 D) Unlimited

Answer: B. Row data can be physically ordered only one way.

Q3. (Predict the behavior) Index on (last_name, first_name). Can WHERE first_name = 'Neha' perform an index seek?

Answer: Generally no, because first_name is not the leading column (some DBMSs may use a skip scan or a full index scan).

Q4. (Multiple choice) Which ACID property guarantees committed data survives a crash? A) Atomicity B) Consistency C) Isolation D) Durability Answer: D. Q5. (Find the problem)

The table has an index on status, but the query scans the whole table. Why?

Answer: The function on the column makes the predicate non-sargable. Store status in a consistent case and filter status = 'PENDING', or create an expression index on LOWER(status).

Q6. (Multiple choice) Which anomaly is prevented by READ COMMITTED? A) Dirty read B) Non-repeatable read C) Phantom read D) Write skew Answer: A.

Q7. (True/False) EXPLAIN ANALYZE DELETE FROM orders; in PostgreSQL only shows the plan without deleting anything.

Answer: False. EXPLAIN ANALYZE executes the statement. Wrap it in BEGIN; ... ROLLBACK;.

Q8. (Write the query) Write a statement that decrements stock of product 102 by 3 only if enough stock exists.

Q9. (Multiple choice) What is MySQL InnoDB's default isolation level? A) READ UNCOMMITTED B) READ COMMITTED C) REPEATABLE READ D) SERIALIZABLE Answer: C.

Q10. (True/False) Adding more indexes always improves overall database performance.

Answer: False. Indexes speed up reads but slow down writes and consume storage and memory.

SELECT * FROM orders WHERE LOWER(status) = 'pending';

UPDATE products SET stock = stock - 3 WHERE product_id = 102 AND stock >= 3;