Isolation Levels

Transaction isolation levels define the degree to which a transaction is visible to other concurrent transactions. It's a trade-off between isolation and performance.

Isolation Levels Overview

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read	Performance
Read Uncommitted	Possible	Possible	Possible	Highest
Read Committed	Prevented	Possible	Possible	High
Repeatable Read	Prevented	Prevented	Possible*	Medium
Serializable	Prevented	Prevented	Prevented	Lowest

*PostgreSQL's Repeatable Read also prevents phantom reads

Read Uncommitted

The lowest isolation level where transactions can read uncommitted data from other transactions.

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Rarely used in practice because of dirty read risk

Problem Demonstration

Transaction A (Read Uncommitted)  Transaction B
─────────────────────────────────────────────────────
BEGIN;                           BEGIN;

                                 UPDATE products
                                 SET price = 50
                                 WHERE id = 1;
                                 -- Not committed

SELECT price FROM products
WHERE id = 1;
-- Returns 50 (dirty read!)

                                 ROLLBACK;
                                 -- B rolled back

-- A made decisions based on invalid data

Read Committed

Default isolation level for PostgreSQL and Oracle. Can only read committed data.

-- PostgreSQL default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Each query sees the currently committed snapshot

Behavior Demonstration

Transaction A (Read Committed)   Transaction B
─────────────────────────────────────────────────────
BEGIN;                           BEGIN;

SELECT balance FROM accounts
WHERE id = 1;
-- Returns 1000

                                 UPDATE accounts
                                 SET balance = 900
                                 WHERE id = 1;

SELECT balance FROM accounts
WHERE id = 1;
-- Still returns 1000 (B not committed)

                                 COMMIT;

SELECT balance FROM accounts
WHERE id = 1;
-- Returns 900 (non-repeatable read!)

COMMIT;

Use Cases

-- Suitable: Most OLTP scenarios, balance between performance and consistency
-- Not suitable: Scenarios requiring multiple reads of the same data in a transaction

-- Example: Report generation needs consistent snapshot
-- Read Committed may cause:
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 100
-- Other transactions modified some order statuses
SELECT SUM(total) FROM orders WHERE status = 'pending';  -- Count and sum don't match!

Repeatable Read

Default isolation level for MySQL InnoDB. Guarantees consistent results for multiple reads within the same transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Creates consistent snapshot when transaction starts
-- All reads are based on that snapshot

Behavior Demonstration

Transaction A (Repeatable Read)  Transaction B
─────────────────────────────────────────────────────
BEGIN;                           BEGIN;

SELECT balance FROM accounts
WHERE id = 1;
-- Returns 1000

                                 UPDATE accounts
                                 SET balance = 900
                                 WHERE id = 1;
                                 COMMIT;

SELECT balance FROM accounts
WHERE id = 1;
-- Still returns 1000 (repeatable read!)

COMMIT;
-- Only sees new value after transaction ends

Phantom Read Problem

Transaction A (Repeatable Read)  Transaction B
─────────────────────────────────────────────────────
BEGIN;                           BEGIN;

SELECT COUNT(*) FROM orders
WHERE user_id = 1;
-- Returns 5

                                 INSERT INTO orders
                                 (user_id, total)
                                 VALUES (1, 100);
                                 COMMIT;

SELECT COUNT(*) FROM orders
WHERE user_id = 1;
-- MySQL: May return 6 (phantom read)
-- PostgreSQL: Returns 5 (no phantom read)

-- But if performing write operation:
INSERT INTO order_summary
(user_id, order_count)
VALUES (1, 5);
-- May be inconsistent with actual count!

Serializable

Highest isolation level with fully serialized execution, preventing all concurrency anomalies.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Transactions are fully isolated, as if executed serially
-- Highest performance overhead

Implementation Methods

PostgreSQL: Serializable Snapshot Isolation (SSI)
┌─────────────────────────────────────────────────────────┐
│ Uses MVCC for optimistic execution, detects conflicts   │
│ at commit time. Aborts one transaction on conflict.     │
│ No traditional locks, better concurrency performance.   │
└─────────────────────────────────────────────────────────┘

MySQL: Traditional Locking
┌─────────────────────────────────────────────────────────┐
│ Reads use shared locks (S Lock)                         │
│ Writes use exclusive locks (X Lock)                     │
│ May cause significant lock waits and deadlocks          │
└─────────────────────────────────────────────────────────┘

Behavior Demonstration

Transaction A (Serializable)     Transaction B (Serializable)
─────────────────────────────────────────────────────
BEGIN;                           BEGIN;

SELECT SUM(balance)
FROM accounts;
-- Returns 10000

                                 SELECT SUM(balance)
                                 FROM accounts;
                                 -- Returns 10000

UPDATE accounts
SET balance = balance + 100
WHERE id = 1;

                                 UPDATE accounts
                                 SET balance = balance + 50
                                 WHERE id = 2;

COMMIT;
                                 -- PostgreSQL: May fail due to serialization conflict
                                 -- ERROR: could not serialize access
                                 COMMIT; -- or ROLLBACK

Comparison by Database

PostgreSQL

-- Supports all four levels
-- Default: Read Committed
-- Repeatable Read uses MVCC snapshot, no phantom reads
-- Serializable uses SSI, optimistic concurrency control

-- Check current isolation level
SHOW default_transaction_isolation;

-- Set session level
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set for single transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

MySQL (InnoDB)

-- Supports all four levels
-- Default: Repeatable Read
-- Repeatable Read may have phantom reads (but gap locks reduce them)
-- Serializable uses traditional locking

-- Check current isolation level
SELECT @@transaction_isolation;

-- Set global level
SET GLOBAL transaction_isolation = 'SERIALIZABLE';

-- Set session level
SET SESSION transaction_isolation = 'READ-COMMITTED';

Choosing Isolation Level

Decision Guide

┌──────────────────────────────────────────────────────────┐
│ Need highest data correctness? (financial, inventory)    │
│ └─ YES → Serializable                                    │
│                                                          │
│ Need consistent reads within transaction? (reports)      │
│ └─ YES → Repeatable Read                                 │
│                                                          │
│ Standard OLTP operations? (web applications)             │
│ └─ YES → Read Committed (recommended default)            │
│                                                          │
│ Read-only analysis, tolerates dirty data?                │
│ └─ YES → Read Uncommitted (rarely used)                  │
└──────────────────────────────────────────────────────────┘

Scenario Recommendations

Scenario	Recommended Level	Reason
Bank transfers	Serializable	Prevent overdrafts
Inventory deduction	Serializable + row locks	Prevent overselling
User order lists	Read Committed	Standard CRUD
Report generation	Repeatable Read	Consistent snapshot
Real-time dashboards	Read Committed	Approximations OK
Config reading	Read Uncommitted	Performance priority

Practical Patterns

Optimistic Locking

-- Use version numbers for optimistic locking
-- Suitable for Read Committed level

-- Get version when reading
SELECT id, name, price, version FROM products WHERE id = 1;
-- version = 5

-- Check version when updating
UPDATE products
SET price = 99.99, version = version + 1
WHERE id = 1 AND version = 5;

-- If affected rows = 0, data was modified, need to retry

Pessimistic Locking

-- Use SELECT FOR UPDATE for exclusive locks
-- Suitable for high-conflict scenarios

BEGIN;

-- Lock row, other transactions must wait
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- Execute business logic
UPDATE products SET stock = stock - 1 WHERE id = 1;

COMMIT;

Gap Locks (MySQL)

-- MySQL InnoDB uses gap locks in Repeatable Read to prevent phantom reads

BEGIN;

-- Lock range [10, 20)
SELECT * FROM orders WHERE amount BETWEEN 10 AND 20 FOR UPDATE;

-- Other transactions cannot insert in this range
-- INSERT INTO orders (amount) VALUES (15);  -- Blocked

COMMIT;

Best Practices

Know the defaults: PostgreSQL is Read Committed, MySQL is Repeatable Read
Choose based on needs: Don't blindly use the highest level
Handle retries: Serializable transactions may rollback due to conflicts
Keep transactions short: Shorter transactions mean fewer conflicts
Monitor lock waits: High isolation levels may cause lock contention
Consider application-level control: Sometimes optimistic locking is more efficient than high isolation levels

Isolation Levels

On this page