Isolation Levels

Concurrency Problems in Databases

When multiple transactions execute simultaneously, they can interfere with each other if not properly isolated. There are three main concurrency anomalies that isolation levels address.

The Three Concurrency Problems

Transaction A                Transaction B
─────────────                ─────────────
BEGIN;                       BEGIN;
SELECT balance;              
  → $1000                    UPDATE accounts SET balance = 900
                             WHERE id = 'A';
SELECT balance;              
  → $900 (dirty read!)       
                             ROLLBACK;
                             -- Balance was never actually changed
                             -- But Transaction A saw $900!

Why Concurrency Matters

Databases serve thousands of concurrent users. Without proper isolation:

• Users might see inconsistent data
• Financial calculations could be wrong
• Reports might show incorrect aggregations
• Application logic could make wrong decisions

Trade-off: Isolation vs Performance

Higher isolation prevents more problems but reduces concurrency:

Isolation Level	Problems Prevented	Performance
READ UNCOMMITTED	None	Highest
READ COMMITTED	Dirty reads	High
REPEATABLE READ	Dirty + Non-repeatable reads	Medium
SERIALIZABLE	All three	Lowest

Most applications use READ COMMITTED (default in PostgreSQL, SQL Server) or REPEATABLE READ (default in MySQL InnoDB).

Dirty Reads

A dirty read occurs when a transaction reads data that has been modified by another transaction but not yet committed. If the other transaction rolls back, the first transaction has read invalid data.

Example of a Dirty Read

-- Session 1: Account balance is $1000
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 'A';
-- Balance changed to $500, but NOT committed yet

-- Session 2 (concurrent, using READ UNCOMMITTED):
BEGIN;
SELECT balance FROM accounts WHERE id = 'A';
-- Returns $500 (dirty read!)
-- This value doesn't exist yet - it might be rolled back

-- Session 1:
ROLLBACK;  -- Oops! Revert the change
-- Balance is actually still $1000

-- Session 2:
SELECT balance FROM accounts WHERE id = 'A';
-- Now returns $1000 (correct value)
-- But Session 2 already used $500 in calculations!

Real-World Impact

-- Inventory check with dirty read:
BEGIN;
SELECT stock FROM products WHERE id = 5;
-- Returns 10 (from another transaction that hasn't committed)
-- User thinks 10 units available

-- Meanwhile, other transaction rolls back:
-- Actual stock was 0, not 10

-- User places order for 5 units:
INSERT INTO orders (product_id, qty) VALUES (5, 5);
-- Now we're oversold!

How to Prevent Dirty Reads

-- Use READ COMMITTED or higher isolation level:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 'A';
-- Returns only committed values ($1000)

Which Isolation Levels Allow Dirty Reads?

Isolation Level	Dirty Read Possible?
READ UNCOMMITTED	Yes
READ COMMITTED	No
REPEATABLE READ	No
SERIALIZABLE	No

Only READ UNCOMMITTED allows dirty reads. All other levels prevent them by only reading committed data.

Non-Repeatable Reads

A non-repeatable read occurs when a transaction reads the same row twice and gets different values each time, because another transaction modified the row between the reads.

Example of a Non-Repeatable Read

-- Session 1: Read balance twice in same transaction
BEGIN;
SELECT balance FROM accounts WHERE id = 'A';
-- Returns $1000

-- Session 2 (concurrent):
BEGIN;
UPDATE accounts SET balance = 1200 WHERE id = 'A';
COMMIT;  -- Change is committed

-- Session 1:
SELECT balance FROM accounts WHERE id = 'A';
-- Returns $1200 (different from first read!)
-- Non-repeatable read occurred
COMMIT;

Impact on Application Logic

-- Financial report with non-repeatable read:
BEGIN;
SELECT SUM(balance) FROM accounts;  -- $10,000
-- ... application processes data ...
SELECT SUM(balance) FROM accounts;  -- $10,500 (changed!)
-- Report shows inconsistent totals
-- Average calculation is wrong

Non-Repeatable Read vs Dirty Read

Issue	What Happens	Data Source
Dirty Read	Read uncommitted data	Another uncommitted transaction
Non-Repeatable Read	Read committed data changes	Another committed transaction

Non-repeatable reads are less severe than dirty reads because the data IS committed, but it creates inconsistency within a transaction.

Which Isolation Levels Prevent Non-Repeatable Reads?

Isolation Level	Non-Repeatable Read Possible?
READ UNCOMMITTED	Yes
READ COMMITTED	Yes
REPEATABLE READ	No
SERIALIZABLE	No

REPEATABLE READ and SERIALIZABLE prevent non-repeatable reads by ensuring that if a transaction reads a row, the row cannot be modified by other transactions until the first transaction completes.

Phantom Reads

A phantom read occurs when a transaction runs the same query twice and gets a different set of rows each time, because another transaction inserted or deleted rows that match the query's WHERE clause.

Example of a Phantom Read

-- Session 1: Count active users twice
BEGIN;
SELECT COUNT(*) FROM users WHERE status = 'active';
-- Returns 100

-- Session 2 (concurrent):
BEGIN;
INSERT INTO users (name, status) VALUES ('New User', 'active');
COMMIT;  -- New user is committed

-- Session 1:
SELECT COUNT(*) FROM users WHERE status = 'active';
-- Returns 101 (phantom row appeared!)
-- A 'phantom' row has appeared
COMMIT;

Phantom Read Impact

-- Batch processing with phantom read:
BEGIN;
SELECT order_id FROM orders WHERE status = 'pending';
-- Returns orders 1-50

-- Process orders 1-50...

-- Session 2 inserts new pending order
BEGIN;
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- Session 1:
SELECT order_id FROM orders WHERE status = 'pending';
-- Returns orders 1-51 (order 51 is a phantom!)
-- If processing again, order 51 gets processed twice

Phantom Read vs Non-Repeatable Read

Issue	What Changes
Non-Repeatable Read	Existing row VALUES change
Phantom Read	NUMBER of rows changes (insert/delete)

Which Isolation Levels Prevent Phantom Reads?

Isolation Level	Phantom Read Possible?
READ UNCOMMITTED	Yes
READ COMMITTED	Yes
REPEATABLE READ	Yes (in most databases)
SERIALIZABLE	No

Note: Some databases (like PostgreSQL with certain locking) can prevent phantom reads at REPEATABLE READ, but standard SQL defines SERIALIZABLE as the level that prevents all three anomalies.

SQL Isolation Levels

The SQL standard defines four isolation levels, each preventing specific concurrency anomalies while trading off performance.

READ UNCOMMITTED

The lowest isolation level. Allows dirty reads, non-repeatable reads, and phantom reads.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT * FROM accounts WHERE id = 'A';
-- Can read uncommitted changes from other transactions
COMMIT;

Use case: Rarely used. Only for monitoring or approximate counts where speed matters more than accuracy.

READ COMMITTED

Only reads committed data. Prevents dirty reads but allows non-repeatable reads and phantom reads.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 'A';  -- $1000
-- Even if another transaction modifies this row, we only see committed data
SELECT balance FROM accounts WHERE id = 'A';  -- $1000 (if not modified)
COMMIT;

Use case: Default for most databases. Good balance of isolation and performance.

REPEATABLE READ

Ensures that if you read a row twice in the same transaction, you get the same values. Prevents dirty and non-repeatable reads.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 'A';  -- $1000
-- Even if another transaction commits a change, we still see $1000
SELECT balance FROM accounts WHERE id = 'A';  -- Still $1000
COMMIT;

Use case: Reporting, analytics, financial calculations where consistency within a transaction matters.

SERIALIZABLE

The highest isolation level. Transactions execute as if they were run sequentially. Prevents all three anomalies.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT COUNT(*) FROM users WHERE status = 'active';  -- 100
-- Other transactions cannot insert/delete rows matching this query
SELECT COUNT(*) FROM users WHERE status = 'active';  -- Still 100
COMMIT;

Use case: Critical transactions where correctness is more important than performance (banking, inventory).

Isolation Level Summary

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes
SERIALIZABLE	No	No	No

Setting Isolation Level

-- PostgreSQL / MySQL / Standard SQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- For entire session (PostgreSQL)
SET default_transaction_isolation = 'read committed';

-- MySQL
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- SQL Server
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- JDBC
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

Deadlocks: Causes and Prevention

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular dependency. Neither transaction can proceed.

Classic Deadlock Example

-- Transaction 1                          -- Transaction 2
BEGIN;                                    BEGIN;
UPDATE accounts SET balance = 900         UPDATE accounts SET balance = 800
WHERE id = 'A';  -- Locks row A          WHERE id = 'B';  -- Locks row B
                                          
UPDATE accounts SET balance = 1100        UPDATE accounts SET balance = 1200
WHERE id = 'B';  -- Waits for row B      WHERE id = 'A';  -- Waits for row A
-- BLOCKED: T2 holds row B                -- BLOCKED: T1 holds row A
                                          
-- DEADLOCK! Neither can proceed

Transaction 1 → Row B → held by T2
Transaction 2 → Row A → held by T1
     ↑_______________↓ (circular wait)

Deadlock Detection

Databases automatically detect deadlocks using wait-for graphs and choose a victim transaction to roll back.

-- PostgreSQL: Check for deadlocks in log
-- ERROR: deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 67890
-- HINT: See server log for query details.

-- SQL Server: Check deadlocks
SELECT * FROM sys.dm_tran_locks;
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0;

Deadlock Prevention Strategies

-- 1. Lock ordering: Always lock rows in the same order
-- Transaction 1
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 'A';
UPDATE accounts SET balance = 1100 WHERE id = 'B';
COMMIT;

-- Transaction 2 (same order: A then B)
BEGIN;
UPDATE accounts SET balance = 800 WHERE id = 'A';
UPDATE accounts SET balance = 1200 WHERE id = 'B';
COMMIT;

-- 2. Short transactions: Hold locks for minimal time
BEGIN;
-- Do minimal work here
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
COMMIT;  -- Release lock quickly

-- 3. Use SELECT ... FOR UPDATE to lock rows explicitly
BEGIN;
SELECT * FROM accounts WHERE id = 'A' FOR UPDATE;
-- Now we have an explicit lock, other transactions wait
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
COMMIT;

Deadlock Avoidance Techniques

Technique	Description
Lock ordering	Always acquire locks in consistent global order
Timeout	Set lock timeout to fail fast
Retry logic	Catch deadlock errors and retry transaction
Lower isolation	Use READ COMMITTED to reduce lock scope
Application design	Minimize transaction duration

Handling Deadlocks in Application Code

# Python example with retry logic
import psycopg2
from psycopg2 import errors

max_retries = 3
for attempt in range(max_retries):
    try:
        conn = psycopg2.connect(dsn)
        cur = conn.cursor()
        cur.execute("BEGIN")
        cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 'A'")
        cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 'B'")
        conn.commit()
        break
    except errors.DeadlockDetected:
        conn.rollback()
        if attempt == max_retries - 1:
            raise