MySQL (II): Transactions and MVCC

MySQL Transactions, Locks, MVCC, and Logs#

Transactions#

ACID Properties#

The ACID properties and how they are guaranteed.

• Atomicity: Guaranteed by undo log.
• Consistency: Guaranteed by the other three properties.
• Isolation: Guaranteed by locking mechanisms / MVCC.
• Durability: Guaranteed by redo log.

Transaction Isolation Levels#

1. Read Uncommitted: Changes made by a transaction before it commits can be seen by other transactions.
2. Read Committed: Changes made by a transaction can only be seen by other transactions after it commits.
3. Repeatable Read: Data seen during a transaction’s execution is consistent with the data seen when the transaction started. This is the default isolation level for MySQL’s InnoDB engine.
4. Serializable: Acquires read and write locks on records. When multiple transactions perform read/write operations on a record and a read-write conflict occurs, subsequent transactions must wait for the previous transaction to complete before continuing execution.

Generally speaking, using Repeatable Read (the default) can largely avoid phantom read issues (though they can still occur). The Serializable isolation level impacts performance. Phantom reads are basically resolved through the following two methods:

1. For ordinary SELECT statements (snapshot reads), MVCC resolves phantom reads.
2. For SELECT…FOR UPDATE statements (current reads), next-key locks (record lock + gap lock) resolve phantom reads.

Implementation methods:

• For the Read Uncommitted isolation level, because uncommitted changes from other transactions can be read, the latest data is read directly.
• For the Serializable isolation level, concurrent access is prevented by adding read/write locks.
• For the Read Committed and Repeatable Read isolation levels, they are implemented using Read Views. Their difference lies in when the Read View is created. Think of a Read View as a data snapshot, like a camera capturing the scenery at a specific moment. The Read Committed isolation level re-generates a Read View before each statement execution, whereas the Repeatable Read isolation level generates a Read View when the transaction starts and uses it throughout the entire transaction.

Commands to start a transaction in MySQL:

1. BEGIN / START TRANSACTION: The transaction is not truly considered started until the first SELECT statement is executed.
2. START TRANSACTION WITH CONSISTENT SNAPSHOT: Starts the transaction immediately.

Dirty Read / Non-repeatable Read / Phantom Read#

• Dirty Read: A transaction reads data that has been modified by another uncommitted transaction.
• Non-repeatable Read: In the same transaction, reading the same data multiple times results in different values.
• Phantom Read: In the same transaction, querying the number of records that meet a certain condition multiple times results in different counts.

MVCC (Important)#

How does Read View work in MVCC? Read View:

After creating a Read View, the record’s trx_id falls into one of these three categories:

This method of controlling concurrent transaction access to the same record through a “version chain” is called MVCC (Multi-Version Concurrency Control).

Simply put, the MVCC logic chain can be remembered like this:

• Each row record carries trx_id and roll_pointer.
• trx_id indicates the transaction ID that last modified this row.
• roll_pointer points from the current version to the previous undo log version.
• Thus, a record forms a version chain through undo logs.
• During a snapshot read, a transaction uses its own Read View to traverse the version chain backwards until it finds a version visible to it.

Add a few points most likely to be asked in follow-up questions from Apple Notes:

• undo log is not just for rollback; it’s also the true source of MVCC historical versions.
• For DELETE, InnoDB does not physically delete immediately. It first marks the record for deletion, which is later cleaned up by the purge thread.
• For UPDATE:
  • If the primary key column is updated, it’s essentially treated as “delete old row + insert new row”.
  • If a non-primary key column is updated, the old value is recorded in the undo log. During rollback or snapshot reads, the historical version can be accessed along the version chain.
• undo pages themselves also go into the Buffer Pool. True persistence still relies on redo log as the fallback.

Read View Visibility Determination#

• trx_id < min_trx_id: The transaction that modified this version had already committed when the snapshot was created. The current version is visible.
• trx_id >= max_trx_id: This transaction ID was assigned after the snapshot was created. The current version is not visible; need to look for an older version.
• min_trx_id <= trx_id < max_trx_id:
  • If trx_id is in the list of active transactions, it means this transaction hadn’t committed when the snapshot was taken. The version is not visible.
  • Otherwise, the transaction had already committed, so the version is visible.

How does MVCC implement Read Committed / Repeatable Read?#

For Read Committed:

• A new Read View is regenerated before each SELECT statement execution.

For Repeatable Read:

• A Read View is generated when the transaction starts (upon the first SELECT or BEGIN), and this Read View remains valid throughout the entire transaction lifecycle without being regenerated.

In which scenario can MVCC not completely prevent phantom reads?#

## Transaction A-----------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_stu where id = 5;
Empty set (0.01 sec)

## Transaction B-----------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_stu values(5, 'Xiaomei', 18);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

## Transaction A-----------------
mysql> update t_stu set name = 'Xiaolin Coding' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_stu where id = 5;
+----+----------------+------+
| id | name           | age  |
+----+----------------+------+
|  5 | Xiaolin Coding |   18 |
+----+----------------+------+
1 row in set (0.00 sec)

Attention: The main reason is that MVCC only supports SELECT. It’s ineffective when UPDATE is involved…

However, phantom reads can be completely resolved using MVCC + next-key lock!

The InnoDB storage engine resolves phantom reads at the RR level through MVCC and Next-key Lock:

1. Executing ordinary SELECT: Data is read using MVCC snapshot reads. In snapshot reads, the RR isolation level generates a Read View only upon the first query in the transaction and uses it until the transaction commits. Therefore, updates and inserts made by other transactions after the Read View is generated are not visible to the current transaction, achieving repeatable reads and preventing “phantom reads” under snapshot reads.

2. Executing current reads like SELECT...FOR UPDATE / LOCK IN SHARE MODE, INSERT, UPDATE, DELETE: Under current reads, the latest data is always read. If another transaction inserts a new record that falls within the current transaction’s query scope, a phantom read would occur! InnoDB uses Next-key Lock to prevent this. When a current read is executed, it locks the records that are read and also locks the gaps between them, preventing other transactions from inserting data within the query scope. Preventing insertion prevents phantom reads.