The Implementation Principles of ACID in MySQL

ACID

• Atomic: Either all operations succeed, or all fail (e.g., in a transfer, one person's balance decreases while another's increases; either both succeed or both fail)

• Consistency: Before and after an operation, the state of the data must not change in a way that violates rules; it must remain a valid state (e.g., balances cannot become negative)

• Isolation: Concurrent transactions must not affect each other and must behave as if they were executed serially (e.g., if two people transfer money to the same person, the recipient should receive $2x$ yuan, not $x$ yuan)

• Durability: Data must not be lost and must be persisted to disk (data must survive even if the database crashes)

Innodb

Innodb, as the most commonly used storage engine in MySQL, has its internal structure as shown below:

Among its most important components are: buffer pool, redo log, and undo log

Atomic Implementation Principle: undo log

Undo Log is a logical log that records incremental changes to data. It can be used to roll back transactions, thereby ensuring transaction atomicity. It also implements Multi-Version Concurrency Control (MVCC), providing the foundation for isolation, and solving read-write conflicts and consistent reads. Here, we mainly introduce the process by which undo log implements atomicity:

As shown in the figure, when executing SQL to insert content into or modify content in a database table, the undo log correspondingly records the deletion and reverse modification operations. If execution fails, the SQL in the undo log will be executed to roll back the changes.

Consistency Implementation Principle

Consistency is generally achieved through business logic or constraints, such as validating in business code that balances cannot be negative.

Isolation Implementation Principle: MVCC

When multiple transactions execute concurrently, the following three scenarios can occur:

• Read-read: A simple solution is to use shared locks

• Write-write: A simple solution is to use exclusive locks

• Read-write: A simple solution is to use locking

Locking is the most direct method but also the slowest. MySQL uses MVCC to provide the implementation of transaction isolation levels, aiming to improve concurrent transaction performance.

The snapshot-based solution of MVCC (Multi-Version Concurrency Control) is specifically:

• The database maintains multiple version snapshots for each row of data
• A transaction can only read the latest version of data as of the start of the current transaction
• Uncommitted data is invisible to other transactions

Transaction concurrency in databases is not quite the same as concurrency in Java. Database transactions can be rolled back on failure (Atomic property), but Java cannot, so they cannot be fully compared.

Possible isolation levels for transactions in a database:

• Dirty Read: Transaction A modifies data, Transaction B reads the modified data, but then Transaction A rolls back due to an error. The data B obtained is then incorrect.

  • MVCC solution: Read Committed
    • A transaction can only read the latest snapshot from when it started, so it won't be affected.
    • As long as a transaction hasn't committed, other transactions cannot read its version.
    • Write operations (update/delete) on the same row require locks, but read operations do not, so no blocking occurs.

• Non-Repeatable Read: Transaction A reads a piece of data for the first time, Transaction B immediately modifies this data, and when Transaction A reads the same data a second time, it finds the data is inconsistent with the first read.

  • MVCC solution: Repeatable Read
    • At the start of a transaction, a snapshot of the data is created, and all subsequent operations are based on this snapshot, unaffected by other transactions. This achieves repeatable reads within a transaction.

• Phantom Read: Transaction A performs a statistical operation counting rows in the entire table, and Transaction B adds or removes rows during this period, causing Transaction A's statistical results before and after to be inconsistent.

  • MVCC solution: Serializable (table lock)
    • Since statistical operations involve all rows, multi-version snapshots of a single row are useless; the entire table must be locked.
    • Read operations acquire shared locks, and write operations acquire exclusive locks.

The data structure maintained by Undo Log is shown below. Each record row has a pointer to its previous historical version.

The specific steps by which Undo Log implements isolation in MVCC:

• Each transaction has an incrementing transaction ID

• The row records in the data page contain three hidden columns: DB_ROW_ID, DB_TRX_ID (the transaction ID of the current operation), and DB_ROLL_PTR (rollback pointer, recording the previous version of the current row)

• DB_ROLL_PTR links all snapshot records of a data row together in a linked list structure (which is the structure of the Undo Log shown above)

MVCC finds the corresponding version in the Undo Log through a Read View and performs CAS. Different isolation levels use different Read Views. I'll skip the details here since there's quite a lot of content.

In summary, the significance of MVCC:

• Read and write operations do not block each other

• Reduces the probability of deadlocks

• Enables consistent reads

Durability Implementation Principle: redo log

To prevent data loss due to database crashes, a direct method is to write data to disk simultaneously when modifying it, i.e., writing pages to disk before transaction commit. However, this approach faces two problems:

1. Slow random I/O: Since modified data is likely not to satisfy the principle of locality, random I/O is very time-consuming, greatly reducing database performance.

2. Write amplification: Since the database writes to disk not row by row but in entire pages, when modifications occur on only a small amount of data, an entire page still needs to be written, which also reduces performance.

Based on the above analysis, writing to disk before transaction commit is the only solution to achieve durability, but random I/O is too slow. So why must it be random I/O? Why must physical data be flushed to disk? Why not just write the operations to disk? Why not use sequential I/O directly?

Therefore, the mature solution is WAL (Write-ahead logging):

Every update operation in MySQL is first written to the redo log, and then written to the buffer pool. The redo log is a physical log that records changes to pages. Since the redo log uses sequential I/O, its write speed is very fast. If a failure occurs before writing to disk, restarting MySQL and redoing based on the redo log will recover the data.