MySQL Transaction and Lock Mechanism: ACID Properties Deep Dive

ACID

In relational databases, a logical unit of work must satisfy 4 properties to become a transaction: atomicity, consistency, isolation, durability.

1. Atomicity

Operations in a transaction either all execute successfully or none execute. If any part of the transaction fails, the entire transaction will roll back to its initial state.

2. Consistency

Ensures that when a transaction completes, it must transition the database from one consistent state to another.

3. Isolation

When multiple transactions execute concurrently, one transaction’s execution should not affect other transactions’ execution.

Isolation Levels:

  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable

4. Durability

Once a transaction commits, its modifications will be permanently stored in the database.

Atomicity

Transaction is the basic unit of database operations:

  1. After transaction starts, data modifications first occur in Buffer Pool
  2. Corresponding Redo logs and Undo logs are generated simultaneously
  3. These logs are immediately written to disk log files
  4. Finally, dirty pages in Buffer Pool are flushed to disk

Durability

A complete commit action triggers the following key operation sequence:

  1. binlog落地
  2. Send binlog
  3. Storage engine commit
  4. flush_logs
  5. check_point
  6. Transaction commit marker

Isolation

Read Uncommitted

  • Allows transactions to read data not committed by other transactions
  • May cause dirty reads

Read Committed

  • Only allows reading committed data
  • Solves dirty read problem

Repeatable Read

  • MySQL InnoDB engine’s default isolation level
  • Ensures consistent results when reading the same data multiple times within a transaction
  • Implemented through MVCC

Serializable

  • Highest isolation level
  • Completely serializes transaction execution
  • Solves all concurrency problems

Consistency

Constraint Consistency

  • Foreign key constraints
  • Unique indexes
  • Check constraints

Data Consistency

  • Guaranteed jointly by atomicity, durability, and isolation

WAL: Write-Ahead Logging, write logs first, then write to disk.