MySQL Transaction Isolation Levels & Lock Mechanisms: Dirty Reads, Phantom Reads, Deadlocks Explained

What: What Is a Transaction? (Plain Language)

Previous lessons covered indexing, SQL optimization, large table optimization, sharding, and read-write separation — all addressing database performance issues . Starting here, we tackle database concurrency safety issues : data corruption, concurrent overwrites, payment anomalies, order status confusion, and deadlock errors.

All concurrent data anomalies stem from two root causes: transaction isolation levels and database lock mechanisms .

A transaction is a group of SQL statements executed as a single unit: either all succeed or all roll back, ensuring data stays clean, consistent, and correct.

The four ACID properties in plain terms:

Atomicity : Indivisible — all succeed or all fail.

Consistency : Database state remains valid before and after, no dirty data or corruption.

Isolation : Concurrent transactions interfere with each other to a controllable degree (core of this lesson).

Durability : Once committed, data persists permanently, surviving crashes.

Why: Why Must You Master Isolation Levels and Locks? (Root Cause of Production Incidents)

90% of production concurrency bugs come from misunderstanding isolation levels and misusing locks:

❌ Concurrent payment amounts messed up — under/over charging.

❌ Order status overwritten, status chaos.

❌ Inventory overselling, concurrency overflow.

❌ Random deadlocks, random API errors.

❌ Two queries in the same transaction return inconsistent results.

Core truth : MySQL's default isolation level is not the highest; it sacrifices consistency for performance . Without understanding isolation levels, you cannot write safe concurrent business code.

Where: Four Isolation Levels & Three Concurrency Problems (Precise Mapping)

Concurrent transactions produce three classic data problems, increasing in severity:

Dirty Read < Non-Repeatable Read < Phantom Read

1. Dirty Read (Reading Uncommitted Dirty Data)

Transaction A reads data that Transaction B has not yet committed ; B eventually rolls back, making A's read completely invalid.

2. Non-Repeatable Read (Same Transaction, Two Queries Differ)

Within the same transaction, two queries of the same row return different results because another transaction modified and committed the row in between.

3. Phantom Read (Range Data Appears/Disappears)

Transaction A queries a range of data; Transaction B inserts or deletes rows and commits, causing A's subsequent count or traversal to differ — like a hallucination.

Four Isolation Levels (MySQL Standard, Must Know)

Read Uncommitted : Dirty reads, non-repeatable reads, phantom reads all exist (never used).

Read Committed (RC) : Solves dirty reads; non-repeatable reads and phantom reads remain.

Repeatable Read (RR | MySQL Default) : Solves dirty reads and non-repeatable reads; phantom reads still exist .

Serializable : Solves everything, fully serial execution, extremely poor performance.

How: Step-by-Step Reproduction of the Three Concurrency Problems

Hands-on practice to completely distinguish the three problems and eliminate conceptual confusion.

1. Dirty Read Reproduction

Transaction B starts a transaction, updates data but does not commit; Transaction A reads the uncommitted data; B rolls back, leaving A with dirty data.

Solution : Upgrade to RC or higher isolation level.

2. Non-Repeatable Read Reproduction

Transaction A starts, first query shows amount = 100; Transaction B changes amount to 0 and commits; Transaction A queries again, data has changed.

Solution : Upgrade to RR isolation level.

3. Phantom Read Reproduction

Transaction A counts total orders = 10; Transaction B inserts 1 order and commits; Transaction A counts again = 11.

Key distinction : Non-repeatable read is a single row being modified ; phantom read is range data increasing or decreasing .

How: MySQL Lock Mechanisms Complete Guide (Row Locks / Table Locks / Gap Locks)

Isolation levels are fundamentally implemented via locks + MVCC ; if you don't understand locks, you'll never solve concurrency problems.

1. Table-Level Locks

Locks the entire table; low overhead, coarse granularity, poor concurrency.

Scenarios : Queries without indexes, full-table updates, DDL statements.

Consequence : All reads and writes blocked during lock — a fatal production pitfall.

2. Row-Level Locks (InnoDB Core)

Locks only the specific rows hit; fine granularity, high concurrency.

Prerequisite for activation : Must use precise index equality lookups !

High-frequency pitfall : Index failure, implicit conversion, missing index — row lock escalates directly to table lock.

3. Gap Locks + Next-Key Locks (Core Solution for Phantom Reads)

Under RR level, range queries trigger gap locks, locking empty positions within the interval to prevent new inserts.

Pros : Solves phantom read problem.

Cons : Gap lock range can be too large, easily causing deadlocks.

Production Deadlock Causes and Root-Cause Solutions

Deadlock is not a bug; it is the inevitable result of lock contention with circular wait .

1. Four Necessary Conditions for Deadlock

Mutual exclusion, hold and wait, no preemption, circular wait.

2. Most Frequent Production Deadlock Scenario

Two transactions acquire locks in opposite order , each holding a lock the other needs, waiting forever.

3. Root-Cause Solutions (Enterprise Standard)

✅ Unify code lock acquisition order — all transactions follow the same sequence.

✅ Minimize transaction scope — keep transactions short, open and close quickly.

✅ Avoid range updates — reduce gap lock generation.

✅ Timeout retry mechanism — catch deadlock exceptions and auto-retry.

RC vs RR Isolation Level Enterprise Selection Guidelines

1. RR (MySQL Default)

Advantages: Avoids non-repeatable reads, high data stability.

Disadvantages: Phantom reads exist, many gap locks, high deadlock probability.

Applicable: Orders, payments, core transactional business.

2. RC (Read Committed)

Advantages: No gap locks, smaller lock scope, high concurrency performance, almost no deadlocks.

Disadvantages: Non-repeatable reads exist.

Applicable: Admin backends, list queries, non-core statistical business.

High-Frequency Production Pitfalls Summary

Pitfall 1: Assuming Transactions Guarantee Safety

Transactions do not equal concurrency safety; wrong isolation level or messy locks still cause data corruption.

Pitfall 2: Index Failure Causes Row Lock Escalation to Table Lock

WHERE clause lacks index; single-row update locks entire table, dragging down the whole business.

Pitfall 3: Large Transactions Trigger Chain Reactions

Long-running transactions hold locks too long, causing blocking buildup and frequent deadlocks.

Pitfall 4: Confusing Non-Repeatable Read with Phantom Read

Single-row modification is non-repeatable read; range addition/removal is phantom read — solutions are completely different.

Pitfall 5: Blindly Using Highest Serializable Level

Completely sacrifices concurrency performance; high-concurrency business freezes instantly.

Core Takeaways

Transaction ACID properties; isolation level is the core of solving concurrency data problems.

Dirty read = reads uncommitted; non-repeatable read = single row changed; phantom read = range changed.

MySQL defaults to RR level, solves dirty reads and non-repeatable reads, leaves phantom reads.

Precise index → row lock; no index / index failure → table lock escalation.

RR level has gap locks — they solve phantom reads but are the root cause of frequent deadlocks.

Deadlock solution core: unify lock order, shrink transaction scope.