MySQL InnoDB Storage Engine undo Redo Resolution

This article is about MySQL database InnoDB storage engine redo log roaming


00–undo Log
The Undo Log is to implement the atomicity of the transaction in the MySQL database InnoDB storage engine. Also use undo log to implement multiple version concurrency control (abbreviation: MVCC).

-Atomicity of the transaction (atomicity)
All operations in the transaction are either complete or do not do whatever. You can't just do a partial operation.

Assumed to occur during the run
The error, to roll back (Rollback) to the state before the transaction started, as if the transaction had never run.

-Principle
The principle of Undo log is very easy. To satisfy the atomicity of a transaction, the data is first backed up to a place before any data is manipulated
(The place where the backup of the data is stored is called Undo Log). Then make changes to the data.

If an error occurs or the user runs the
Rollback statement, the system can use the backup in the Undo log to restore the data to the state before the transaction started.

In addition to guaranteeing the atomicity of the transaction, Undo Log can also be used to assist in the persistence of the transaction.


-Persistence of the transaction (durability)
Once the transaction is complete, all changes made to the database by the transaction are persisted to the database. To ensure durability, the database
The system will completely record the changed data to the persistent storage.


-Simplified process for atomic and persisted transactions with undo log
If there is a, b two data. The values are 1, 2, respectively.
A. The transaction starts.
B. Record a=1 to undo log.
C. Change A=3.
D. Record b=2 to undo log.
E. Change b=4.
F. Write the undo log to disk.
G. Write data to disk.
H. Transaction submission
Here is an implied precondition: ' The data is read into memory first. Then change the data in memory. Finally write the data back to disk '.

The reason to ensure atomicity and persistence at the same time is due to the following features:
A. Record the undo log before updating the data.
B. To ensure persistence, the data must be written to disk before the transaction commits. The data must be persisted only if the transaction is committed successfully.
C. Undo log must be persisted to disk before data. Assuming that the system crashes between g,h, undo log is complete,
can be used to roll back a transaction.
D. Assume that the system crashes between a-f because the data is not persisted to disk. So the data on the disk remains in the state before the transaction starts.


Flaw: Writes data and undo log to disk before each transaction commits, which results in a large amount of disk IO. Therefore, the performance is very low.


Suppose you can cache data for a period of time. can reduce IO to improve performance. However, this will lose the persistence of the transaction.

This has led to the introduction of another
mechanisms to achieve persistence. That is, redo Log.


01–redo Log


-Principle
In contrast to undo log, Redo log is a backup of the new data. It is only possible to persist the redo log before committing the transaction.
There is no need to persist data. When the system crashes. Although the data is not persisted. But redo log has been persisted. System can be based on
Redo log content to restore all data to the latest state.


-Undo + Redo Transaction Simplification process
If there is a, b two data, the values are respectively.
A. The transaction starts.
B. Record a=1 to undo log.
C. Change A=3.
D. Record a=3 to redo log.
E. Record b=2 to undo log.
F. Change b=4.
G. Record b=4 to redo log.
H. Write redo log to disk.

I. Transaction Submission


-Features of Undo + Redo Transaction
A. To ensure durability. The redo log must be persisted before the transaction commits.

B. The data does not need to be written to disk before the transaction commits. Instead, it is cached in memory.
C. Redo Log guarantees the persistence of the transaction.
D. Undo Log guarantees the atomicity of the transaction.
E. There is an implicit feature. Data must be written to persistent storage later than redo log.


-IO Performance
The design of Undo + redo is primarily concerned with improving IO performance. Although the data is cached. Write data is reduced by IO.
However, it introduces a new IO, that is, the IO that writes redo log. Assuming that the IO performance of the redo log is poor, it is not possible to raise performance.

In order to ensure Redo log can have better IO performance, InnoDB's Redo log design has the following features:


A. Try to keep the redo log stored in a contiguous space. As a result, the log file space is fully allocated at the first boot of the system.

Record redo log in sequential append mode to improve performance through sequential IO.

  B. Bulk write logs. The log is not written directly to the file. Instead, write the redo log buffer first. When you need to flush the log to disk
      (such as transaction commit), write many logs together to disk.
  C. Concurrent transaction sharing redo log storage space, their redo log in the order in which the statements are run, alternately sequentially recorded together,
      to reduce the space occupied by the log. For example, the contents of the record in Redo log might be this:
      record 1: <trx1, insert ...,
      record 2: <trx2, update ...
      record 3: <trx1, delete ...;
      record 4: <trx3, update ...;
  &nbs P   Record 5: <trx2, insert ...;
  D. Because of C, when a transaction writes redo log to disk. Logs of other uncommitted transactions are also written to disk.
  E. Redo logs are only sequentially appended, and when a transaction needs to be rolled back, its Redo log record is not removed from the
     redo log.


02 – Restore (Recovery)


-recovery policy
  Previously, the uncommitted transaction and the rollback transaction also recorded redo log, so when the recovery These transactions are handled in a special
 . There are 2 different recovery strategies:


  A. When recovering, simply redo the transaction that has already been committed.

B. When the recovery is in progress. Redo all transactions contain uncommitted transactions and rolled-back transactions. Then roll back those with undo log
Uncommitted transactions.


-recovery mechanism of INNODB storage engine
The MySQL database InnoDB storage engine uses a B policy, and the recovery mechanism in the INNODB storage engine has several features:


A. When you redo redo log, you do not care about transactional. When recovering, there is no begin. There is no commit,rollback behavior.

Nor do you care what the transaction is for each log. Transaction-related content, such as transaction ID, is recorded in Redo Log. These elements are only considered as
Part of the data to manipulate.
B. Using the B policy requires that the undo log be persisted, and the corresponding undo log must be written to disk before the redo log is written.
This association of undo and redo log makes persistence complicated. To reduce complexity, innodb the undo log as
Data, the operation to record the Undo log is also recorded in the Redo log.

This way, undo log can be cached like data.
Instead of writing to the disk before redo log.

Redo log that includes the undo log operation. It 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. Here, another question is not clear.

Since redo is not transactional. Wouldn't that be another run of the rolled-back transaction?
That's true.

At the same time, InnoDB also logs the operation of the transaction back to redo log. Rollback operations are essentially
Make changes to the data. Therefore, the operation of the data during rollback is also recorded in the Redo log.

A rollback of the transaction's redo Log, which looks like this:
Record 1: <trx1, Undo log Insert <undo_insert ...>>
Record 2: <trx1, insert a...>
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 B to old value>
Record 9: <trx1, delete a>
A transaction that has been rolled back is redo and then undo, so it does not break the consistency of the data.


-InnoDB related functions in the storage engine
Redo:recv_recovery_from_checkpoint_start ()
Undo:recv_recovery_rollback_active ()
Undo Log's Redo Log:trx_undof_page_add_undo_rec_log ()

MySQL InnoDB Storage Engine undo Redo Resolution