MySQL, MariaDB: Undo | Redo, mariadbredo

MySQL, MariaDB: Undo | Redo [convert], mariadbredo

This article describes how to roam the redo logs of the InnoDB Storage engine of MySQL databases.

00-Undo Log
The Undo Log is used to implement the atomicity of transactions. In the InnoDB Storage engine of MySQL database, the Undo Log is also used to implement multi-version concurrency control (MVCC ).

-Atomicity)
All operations in a transaction are either completed or not performed. If
The transaction is rolled back to the state before the start of the transaction, just as this transaction has never been executed.

-Principle
The principle of Undo Log is very simple. In order to satisfy the atomicity of transactions, data is first backed up to one place before any data is operated.
(The data backup is called the Undo Log ). Then modify the data. If an error occurs or the user executes
ROLLBACK statement, the system can use the backup in the Undo Log to restore data to the State before the start of the transaction.

In addition to ensuring the atomicity of transactions, Undo logs can also be used to assist in transaction persistence.

-Transaction Durability)
Once the transaction is completed, all modifications made to the database by the transaction will be permanently saved to the database. To ensure durability
The system records the modified data to persistent storage.

-Use Undo Log to simplify atomic and persistent transactions
Assume that there are two data types, A and B. The values are respectively 1 and 2.
A. Start the transaction.
B. Record A = 1 to undo log.
C. modify A = 3.
D. Record B = 2 to undo log.
E. Modify B = 4.
F. Write the undo log to the disk.
G. Write Data to the disk.
H. transaction commit
Here is an implicit precondition: 'Data is read to the memory first, then the data in the memory is modified, and then the data is written back to disk '.

Atomicity and persistence can be ensured at the same time because of the following features:
A. Record the Undo log before updating the data.
B. To ensure durability, data must be written to the disk before the transaction is committed. Data must be persistent as long as the transaction is successfully committed.
C. The Undo log must be persistent to the disk prior to data. If the system crashes between G and H, the undo log is complete,
Transactions can be rolled back and forth.
D. If the system crashes between A-F because the data is not persisted to the disk. Therefore, the data on the disk remains in the State before the transaction starts.

Defects:Before each transaction is committed, the data and Undo Log are written to the disk, which leads to a large number of disk IO, so the performance is very low.

If data can be cached for a period of time, I/O can be reduced to improve performance. However, the transaction persistence will be lost. Therefore, another
Persistence, that is, Redo Log.

01-Redo Log

-Principle
Opposite to the Undo Log, the Redo Log recordsNew data. Before committing a transaction, you only need to persist the Redo Log,
Data Persistence is not required. When the system crashes, the Redo Log is persistent even though the data is not persistent. The system can
Redo Log Content to restore all data to the latest status.

-Simplified Undo + Redo transaction process
Assume that there are two data types, A and B. The values are respectively 1 and 2.
A. Start the transaction.
B. Record A = 1 to undo log.
C. modify A = 3.
D. Record A = 3 to redo log.
E. Record B = 2 to undo log.
F. Modify B = 4.
G. Record B = 4 to redo log.
H. Write the redo log to the disk.
I. transaction commit

-Features of Undo + Redo transactions
A. To ensure durability, the Redo Log must be persistent before the transaction is committed.
B. Data is cached in the memory instead of being written to the disk before the transaction is committed.
C. Redo Log ensures transaction persistence.
D. Undo Log ensures the atomicity of transactions.
E. There is an implicit feature that data must be written later than redo log to persistent storage.

-IO Performance
The Undo + Redo Design mainly considers improving IO performance. Although data is cached, I/O of Data Writing is reduced.
However, a new IO is introduced, that is, the IO for writing Redo logs. If the IO performance of the Redo Log is poor, it cannot improve the performance.
To ensure good IO performance of Redo logs, the Redo Log Design of InnoDB has the following features:

A. Try to keep the Redo Log stored in A continuous space. Therefore, when the system starts for the first time, the space of the log file is fully allocated.
Redo logs are recorded in sequential append mode, and performance is improved through sequential IO.
B. Write logs in batches. Logs are not directly written to files, but are first written to the redo log buffer. When you need to refresh the logs to the disk
(Such as transaction commit), write many logs to the disk together.
C. Concurrent transactions share the storage space of the Redo Log. Their Redo logs are recorded alternately in the order of statement execution,
To reduce the space occupied by logs. For example, the record content in the Redo Log may be as follows:
Record 1: <trx1, insert...>
Record 2: <trx2, update...>
Record 3: <trx1, delete…>
Record 4: <trx3, update...>
Record 5: <trx2, insert...>
D. Because of C, when a transaction writes the Redo Log to the disk, it also writes the logs of other uncommitted transactions to the disk.
E. Redo Log only performs sequential append operations. When a transaction needs to be rolled back, its Redo Log records will not
Delete the Redo Log.

02-Recovery)

-Recovery policy
As mentioned above, uncommitted transactions and rolled back transactions will also record the Redo Log. Therefore, during the recovery, these transactions must carry out special
There are two different recovery policies:

A. Only committed transactions are redone during restoration.
B. During recovery, redo all the transactions, including uncommitted transactions and rolled back transactions. And then roll back
Uncommitted transactions.

-InnoDB Storage engine Restoration Mechanism
The InnoDB Storage engine of MySQL uses the B policy. The recovery mechanism in the InnoDB Storage engine has the following features:

A. When Redo Log is redoneDo not care about transactions. No BEGIN, COMMIT, or ROLLBACK actions are performed during restoration.
It does not care which transaction each log belongs. Although the transaction ID and other transaction-related content will be recorded in the Redo Log, the content is only treated
A part of the data to be operated.
B. To use the B policy, the Undo Log must be persistent and the corresponding Undo Log must be written to the disk before the Redo Log is written.
This association between Undo and Redo logs makes persistence complex. To reduce complexity, InnoDB regards the Undo Log
Therefore, the Undo Log operations are recorded in the redo log. In this way, undo logs can be cached like data,
Instead of writing data to the disk before the redo log.
The Redo Log containing the Undo Log operation looks like this:
Record 1: <trx1,Undo log insert<Undo_insert...>
Record 2: <trx1, insert...>
Record 3: <trx2,Undo log insert<Undo_update...>
Record 4: <trx2, update...>
Record 5: <trx3,Undo log insert<Undo_delete…>
Record 6: <trx3, delete…>
C. At this point, there is another problem that has not been clarified. Since Redo is not transactional, wouldn't the transaction be rolled back again?
This is indeed the case. At the same time, Innodb also records the operations during transaction rollback to the redo log. The rollback operation is essentially
Modify the data. Therefore, operations on the data during rollback are also recorded in the Redo Log.
The Redo Log of a rolled back transaction looks like this:
Record 1: <trx1, Undo log insert <undo_insert…>
Record 2: <trx1,Insert...>
Record 3: <trx1, Undo log insert <undo_update...>
Record 4: <trx1,Update B...>
Record 5: <trx1, Undo log insert <undo_delete…>
Record 6: <trx1,Delete C...>
RECORD 7: <trx1,Insert C>
Record 8: <trx1,Update BTo old value>
Record 9: <trx1,Delete A>
When a rolled back transaction is restored, redo is performed before undo, so data consistency is not damaged.

-Functions related to the InnoDB Storage Engine
Redo: recv_recovery_from_checkpoint_start ()
Undo: recv_recovery_rollback_active ()
Undo Log Redo Log: trx_undof_page_add_undo_rec_log ()