Isolation Levels and Locking

Learning Focus

Use this lesson to understand isolation levels — the mechanism that controls what one transaction can see while another transaction is still in progress.

Concept Overview

The Concurrency Problem

When multiple transactions run simultaneously, they can interfere with each other. Without isolation, you get three categories of anomalies:

Anomaly	What Happens	Example
Dirty Read	Transaction reads uncommitted data from another transaction	You see a $0 balance because someone started a debit but hasn't committed
Non-Repeatable Read	Same query returns different values within one transaction	First SELECT shows 10 items; second SELECT shows 8 (someone updated between reads)
Phantom Read	Same query returns different rows within one transaction	First SELECT finds 5 orders; second SELECT finds 6 (someone inserted between reads)

Isolation Levels

MySQL offers four isolation levels, each preventing different anomalies:

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
READ UNCOMMITTED	❌ Possible	❌ Possible	❌ Possible	Fastest
READ COMMITTED	✅ Prevented	❌ Possible	❌ Possible	Fast
REPEATABLE READ (default)	✅ Prevented	✅ Prevented	✅ Mostly prevented*	Balanced
SERIALIZABLE	✅ Prevented	✅ Prevented	✅ Prevented	Slowest

*InnoDB's REPEATABLE READ uses gap locking to prevent most phantom reads, which is unique to MySQL.

MySQL's Default: REPEATABLE READ

MySQL defaults to REPEATABLE READ, which is stricter than most databases (PostgreSQL defaults to READ COMMITTED). This means MySQL reads are very consistent by default, but you may encounter more lock contention.

---

Basic Syntax & Rules

Checking and Setting Isolation Level

-- Check current level
SELECT @@transaction_isolation;
-- Result: REPEATABLE-READ

-- Set for current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set for next transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set globally (affects new connections)
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

Locking Reads

Within a transaction, you can explicitly lock rows:

Syntax	Lock Type	What It Does
SELECT ... FOR UPDATE	Exclusive lock	Prevents other transactions from reading or writing the locked rows
SELECT ... FOR SHARE	Shared lock	Allows other reads but blocks writes
SELECT ... FOR UPDATE SKIP LOCKED	Skip locked rows	Returns only unlocked rows (queue pattern)
SELECT ... FOR UPDATE NOWAIT	Fail immediately	Returns error if rows are already locked

START TRANSACTION;

-- Lock the row so nobody else can modify it
SELECT balance FROM accounts
WHERE account_id = 1001
FOR UPDATE;

-- Now safely update
UPDATE accounts SET balance = balance - 500
WHERE account_id = 1001;

COMMIT;  -- Lock released

---

Step-by-Step Examples

Example 1: Demonstrating Dirty Reads

Session 1 (READ UNCOMMITTED):

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;

SELECT balance FROM accounts WHERE account_id = 1001;
-- Shows: 5000 (the actual committed value)

Session 2 (starts a debit but doesn't commit):

START TRANSACTION;
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 1001;
-- NOT committed yet!

Session 1 (reads again):

SELECT balance FROM accounts WHERE account_id = 1001;
-- Shows: 0  ← DIRTY READ! This value was never committed

Session 2 rolls back:

ROLLBACK;
-- Balance goes back to 5000, but Session 1 already saw 0

This is why READ UNCOMMITTED is dangerous for anything that makes decisions based on data.

---

Example 2: REPEATABLE READ — Consistent Snapshot

Session 1 (uses default REPEATABLE READ):

START TRANSACTION;

SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Result: 10

Session 2 (inserts and commits a new pending order):

INSERT INTO orders (customer_id, total_amount, status) VALUES (1, 100, 'pending');
COMMIT;

Session 1 (reads again — same transaction):

SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Still shows: 10 (not 11!)
-- REPEATABLE READ provides a consistent snapshot
COMMIT;

After Session 1 commits and starts a new transaction, it will see 11.

---

Example 3: FOR UPDATE — Preventing Lost Updates

Scenario: Two users try to purchase the last item in stock simultaneously.

Without locking (race condition):

-- Both sessions read stock = 1
SELECT stock_quantity FROM products WHERE product_id = 5;
-- Both see: 1

-- Both update (both think stock is available)
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 5;
-- Stock goes to -1! Oversold!

With FOR UPDATE (safe):

-- Session 1
START TRANSACTION;
SELECT stock_quantity FROM products WHERE product_id = 5 FOR UPDATE;
-- Returns: 1 (and locks the row)

-- Session 2 tries to read the same row:
START TRANSACTION;
SELECT stock_quantity FROM products WHERE product_id = 5 FOR UPDATE;
-- ⏳ BLOCKED! Waits for Session 1's lock to release

-- Session 1 completes:
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 5;
COMMIT;

-- Session 2 now gets the lock:
-- Returns: 0 (sees the committed update)
-- Knows stock is 0, does NOT oversell
ROLLBACK;

---

Example 4: SKIP LOCKED — Queue Processing Pattern

-- Worker 1 grabs unlocked tasks
START TRANSACTION;
SELECT task_id, payload FROM task_queue
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- Gets task #101

-- Worker 2 runs the same query simultaneously
START TRANSACTION;
SELECT task_id, payload FROM task_queue
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- Gets task #102 (skips #101 because it's locked by Worker 1)

This pattern enables parallel queue processing without conflicts.

---

Practical Use Cases

1. Inventory Management

Use FOR UPDATE to lock product rows before decrementing stock — prevents overselling.

2. Financial Transactions

Use REPEATABLE READ or SERIALIZABLE for balance checks and transfers.

3. Job/Task Queues

Use FOR UPDATE SKIP LOCKED to let multiple workers process queue items in parallel.

4. Reporting with Consistency

Use REPEATABLE READ to generate reports with a consistent snapshot of data.

5. Configuration Updates

Use FOR UPDATE when reading configuration values that will be updated conditionally.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Using READ UNCOMMITTED for business logic	Decisions based on uncommitted (possibly rolled-back) data	Use READ COMMITTED or higher
Forgetting FOR UPDATE on read-then-write patterns	Race conditions and lost updates	Always lock rows you intend to update based on read values
Over-using SERIALIZABLE	Massive lock contention, slow throughput	Use REPEATABLE READ for most cases; SERIALIZABLE only when absolutely necessary
Not understanding the default level	Assuming MySQL works like PostgreSQL (READ COMMITTED)	Check @@transaction_isolation and understand REPEATABLE READ behavior
Locking too many rows	Blocks unrelated transactions	Use precise WHERE clauses to lock only the needed rows
Holding locks during slow operations	Timeout and deadlock risk	Keep transactions short; do validation outside the transaction

---

Best Practices

• Use the default REPEATABLE READ — it's appropriate for most applications
• Use FOR UPDATE for read-then-write patterns — prevents lost updates
• Consider READ COMMITTED for high-throughput OLTP — less locking, better concurrency
• Use SKIP LOCKED for queue patterns — enables parallel processing
• Keep transactions short — hold locks for the minimum time necessary
• Set isolation per-session, not globally — different workloads may need different levels
• Test concurrent scenarios — open two MySQL sessions and simulate concurrent access

---

Hands-On Practice

Exercise 1: Observe Isolation Behavior (Easy)

Open two MySQL sessions. In Session 1, start a transaction and insert a row. In Session 2, try to read it under READ UNCOMMITTED and then READ COMMITTED. Observe the difference.

Exercise 2: Prevent Overselling (Medium)

Simulate the inventory overselling scenario:

1. Set stock to 1
2. Open two sessions, both reading stock
3. Both try to decrement
4. Show how FOR UPDATE prevents the race condition

Exercise 3: Build a Task Queue (Advanced)

1. Create a task_queue table with status = 'pending'
2. Write a "claim task" query using FOR UPDATE SKIP LOCKED
3. Simulate two workers claiming different tasks simultaneously
4. Show that no task is processed twice

---

Connection to Other Concepts

Related Concept	How It Connects
ACID Properties	Isolation is the "I" in ACID
Deadlocks	Lock contention between transactions can cause deadlocks
Indexes	Locks are placed on index entries; missing indexes can cause wider locks
Stored Procedures	Transaction and locking logic often lives in procedures
Performance	Higher isolation = more locking = potential performance impact

What to Learn Next

• Deadlocks and Retry Patterns — handle lock conflicts gracefully

---

Visual Learning Diagram

flowchart TD
    A["Two transactions\naccessing same data"] --> B{"Isolation Level?"}
    B -->|READ UNCOMMITTED| C["Can see uncommitted changes\n⚠️ Dirty reads possible"]
    B -->|READ COMMITTED| D["Sees only committed data\nBut different reads in same TX\nmay return different results"]
    B -->|REPEATABLE READ| E["Consistent snapshot\nfor entire transaction\n✅ MySQL default"]
    B -->|SERIALIZABLE| F["Full isolation\nMax consistency\n🐢 Most locking"]

    classDef warning fill:#ffc107,stroke:#333,color:#333
    classDef good fill:#28a745,stroke:#fff,color:#fff
    classDef slow fill:#6c757d,stroke:#fff,color:#fff
    class C warning
    class E good
    class F slow

Quick Reference

-- Check isolation level
SELECT @@transaction_isolation;

-- Set isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Locking reads
SELECT * FROM t WHERE id = 1 FOR UPDATE;         -- exclusive lock
SELECT * FROM t WHERE id = 1 FOR SHARE;           -- shared lock
SELECT * FROM t FOR UPDATE SKIP LOCKED LIMIT 1;   -- queue pattern
SELECT * FROM t WHERE id = 1 FOR UPDATE NOWAIT;   -- fail if locked

-- Release locks
COMMIT;    -- or ROLLBACK

What's Next

• Deadlocks and Retry Patterns