The concept of MTR in MySQL

MTR stands for Mini-Transaction. As the name suggests, it can be understood as "smallest transaction". In MySQL, the process of an atomic access to the underlying page is called a Mini-Transaction. The atomic operation here means that either all succeed or all fail, and there is no intermediate state.

MTR is mainly used in the scenarios of writing undo logs and redo logs. For example, if we want to insert a record into a B+ tree index, the insertion will either succeed or fail. This process can be called an MTR process. During this process, a set of redo logs will be generated. This set of logs is an indivisible whole when performing MySQL crash recovery.

If we have a transaction that contains 3 statements, the conceptual diagram of MTR is as follows:

Mini-Transaction generally follows three principles:

• 1. the fix rules
• 2. WAL
• 3. force-log-at-commit

Here we explain these three principles:

1. the fix rules

Before explaining the first rule, we need to understand the concept of latch in MySQL. In MySQL, latch is a lightweight lock. Unlike lock, it locks for a very short time. In InnoDB, latch can be divided into mutex (mutex) and rwlock (read-write lock). Its purpose is to ensure the correctness of concurrent threads operating critical resources.

Now that we understand the concept of latch, let's look at the fix rule:

To modify a data page, you need to obtain the x-latch of this data page;

Accessing a page requires obtaining an s-latch or an x-latch;

The latch holding the page is not released until the operation of modifying or accessing the page is completed.

2. WAL

I believe everyone is familiar with WAL technology. It is the fundamental reason why the Innodb storage engine supports crash recovery. That is, before persisting a data page, the corresponding log page in memory needs to be persisted first.

3. force-log-at-commit

This principle is very important. It means that when a transaction is committed, all MTR logs generated by it must be flushed to the persistence device to ensure the crash recovery logic.

The reason for introducing MTR is to prepare for the subsequent introduction of redo log optimization in MySQL 8.0. In MySQL 5.7, MTR ensures the atomicity of operations within transactions. When the user performs an operation, the data page is updated and the redo log is written at the same time. mtr is the carrier of the redo log and exists in the private variable of each connection session. When mtr is submitted, the local redo log will be copied to the global log_buffer. In order to ensure the orderliness of the redo log, a lock is required to access the log_buffer. This lock is the mutex mentioned above. Under the protection of this lock, in addition to copying the local log to the global buffer, the data page needs to be added to the flush_list for the background thread to flush and assist the database checkpoint to continue to move forward. Therefore, this lock is very competitive in old versions of MySQL. MySQL 8.0 has optimized this problem, which will be analyzed in detail in the following articles.

The above is the detailed content of the concept of MTR in MySQL. For more information about MySQL MTR, please pay attention to other related articles on 123WORDPRESS.COM!