Database Transaction Isolation Levels and Their Implementation Mechanisms

Problem Description
Transaction isolation level is the core standard for defining visibility between transactions in a Database Management System (DBMS), used to balance concurrency performance and data consistency. It addresses issues such as dirty reads, non-repeatable reads, and phantom reads. Different isolation levels (e.g., Read Uncommitted, Read Committed, Repeatable Read, Serializable) are implemented using techniques like locking mechanisms or Multi-Version Concurrency Control (MVCC). This problem requires a deep understanding of the characteristics, problem scenarios, and implementation principles of each level.

Problem-Solving Process

1. Three Types of Problems Caused by Transaction Concurrency

• Dirty Read: Transaction A reads uncommitted modifications made by Transaction B. If B rolls back, A reads invalid data.
  • Example: A sees a row value changed by B to 100, but the actual value is 50 after B rolls back.
• Non-repeatable Read: Transaction A reads the same row multiple times. Meanwhile, Transaction B modifies and commits that row, causing A's subsequent reads to return inconsistent results.
  • Example: A first reads a row value as 50, B changes it to 100 and commits, A's second read returns 100.
• Phantom Read: Transaction A queries a set of data based on a condition. Meanwhile, Transaction B inserts or deletes data that meets that condition, causing the result set size to change when A queries again.
  • Difference from Non-repeatable Read: Phantom reads focus on additions/deletions to the dataset, while non-repeatable reads focus on modifications to a single row's data.

2. Four Standard Isolation Levels and the Problems They Solve

Isolation levels are ordered from low to high as follows:

• Read Uncommitted: A transaction can read uncommitted modifications from other transactions. Dirty reads, non-repeatable reads, and phantom reads may occur.
• Read Committed: A transaction can only read committed modifications from other transactions. Solves dirty reads, but non-repeatable reads and phantom reads may still occur.
• Repeatable Read: A transaction reads the same data multiple times during its execution and gets consistent results. Solves dirty reads and non-repeatable reads, but phantom reads may still occur.
• Serializable: Transactions are executed completely serially, solving all problems, but concurrency performance is the lowest.

3. Implementation Mechanisms for Isolation Levels

Mechanism One: Locking Mechanism

• Shared Lock (S Lock): Used for read operations, allows other transactions to read but prohibits writes.
• Exclusive Lock (X Lock): Used for write operations, prohibits other transactions from both reading and writing.
• Implementation Methods:
  • Read Uncommitted: X locks are added for writes, but reads use no locks, allowing reads of uncommitted changes.
  • Read Committed: X locks for writes, released after transaction ends; S locks for reads, released immediately after the read. Prevents dirty reads (uncommitted X locks block reads), but non-repeatable reads may occur due to early release of S locks.
  • Repeatable Read: X locks for writes, released after transaction ends; S locks for reads, also held until transaction end. Ensures consistency across multiple reads but cannot prevent insertion of new data (phantom reads).
  • Serializable: Uses range locks (e.g., Next-Key Locking) to lock the data range involved in the query condition, prohibiting other transactions from modifying or inserting, achieving complete isolation.

Mechanism Two: Multi-Version Concurrency Control (MVCC)

• Core Idea: Maintain multiple versions for each data row. When a transaction reads, it selects a visible version based on a timestamp or transaction ID.
• Implementation Steps:
  1. Assign a unique ID (e.g., an incrementing transaction ID) to each transaction when it starts.
  2. Each row has two hidden fields: creation transaction ID and deletion transaction ID.
  3. During a read operation, only data versions are read where the creation transaction ID ≤ the current transaction ID, and the deletion transaction ID > the current transaction ID (or not deleted).
• Application by Level:
  • Read Committed: Each read selects the latest committed version.
  • Repeatable Read: A snapshot of data versions is determined on the transaction's first read; subsequent reads are based on this snapshot, avoiding non-repeatable reads. However, phantom reads may occur due to new version inserts (some databases like MySQL address this with Next-Key Lock).

4. Practical Case: Implementation in MySQL's InnoDB Engine

• Read Committed: Uses MVCC, each query reads the latest committed snapshot.
• Repeatable Read (Default Level): Uses MVCC + Next-Key Lock. A consistent view is established on the first read and reused for subsequent reads; meanwhile, Gap Locks are used to lock index ranges, preventing phantom reads.
  • Example: Transaction A queries records where age>20. Gap locks lock the index range for age>20, preventing other transactions from inserting data that meets the condition.

5. Trade-offs in Choosing Isolation Levels

• Lower Levels (e.g., Read Committed): High concurrency, suitable for scenarios with less strict data consistency requirements (e.g., statistical queries).
• Higher Levels (e.g., Repeatable Read): Ensure data consistency, but increased lock contention may lead to deadlocks. Suitable for critical operations like financial transactions.
• Practical Advice: Choose the lowest feasible level based on business tolerance, avoiding excessive use of Serializable.

Summary
Isolation levels achieve a balance between concurrency and consistency through locking or MVCC. Understanding the characteristics and implementation mechanisms of each level helps in making reasonable choices during database design, optimizing performance, and avoiding data anomalies.