"Reprint" MySQL log Undo | Redo

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

00–undo Log
Undo log is for the atomicity of transactions, in the MySQL database InnoDB storage engine, also with the undo log to achieve multi-version concurrency control (abbreviation: MVCC).

-Atomicity of the transaction (atomicity)
All operations in a transaction are either complete or do nothing, and cannot do only a subset of the operations. If the process occurs during the execution
The error, to roll back (Rollback) to the state before the transaction started, as if the transaction had never been executed.

-Principle
The principle of Undo log is simple, in order to satisfy the atomicity of the transaction, before any data is manipulated, the data is first backed up to a place
(The place where the backup of the data is stored is called Undo Log). The data is then modified. If an error occurs or the user executes the
Rollback statement, the system can use the backup in the Undo log to restore the data to the state before the transaction began.

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

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

-Simplified process for atomic and persisted transactions with undo log
Suppose there is a, b two data, the values are 1, 2, respectively.
A. Start of the transaction.
B. Record a=1 to undo log.
C. Modify the A=3.
D. Record b=2 to undo log.
E. Modify the b=4.
F. Write the undo log to disk.
G. Write data to disk.
H. Transaction submission
There is an implied precondition: ' The data is read into memory first, then the data in memory is modified, and the data is then written back to disk '.

Both atomicity and persistence can be guaranteed at the same time because of the following characteristics:
A. Record the undo log before updating the data.
B. To ensure persistence, the data must be written to disk before the transaction commits. As long as the transaction is successfully committed, the data must be persisted.
C. Undo log must be persisted to disk before data. If the system crashes between g,h, undo log is complete,
can be used to roll back a transaction.
D. If the system crashes between a-f, the data is not persisted to disk. So the data on the disk remains in the state before the transaction begins.

flaw: writes data and undo log to disk before each transaction commits, which results in a large amount of disk IO and therefore low performance.

If you can cache data for a period of time, you can reduce IO to improve performance. However, this will lose the persistence of the transaction. This has led to the introduction of another
mechanism to achieve persistence, i.e. redo Log.

01–redo Log

-Principle
In contrast to undo log, Redo log is a backup of the new data . Just persist the redo log before committing the transaction,
You do not need to persist data. When the system crashes, the redo log is persisted, although the data is not persisted. System can be based on
Redo log content to restore all data to the latest state.

-Undo + Redo Transaction Simplification process
Suppose there is a, b two data, the values are each.
A. Start of the transaction.
B. Record a=1 to undo log.
C. Modify the 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 redo log to disk.
I. Transaction Submission

- features of Undo + Redo transaction
A. To ensure persistence, the redo log must be persisted before the transaction is committed.
B. The data does not need to be written to the disk before the transaction commits, but 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 implied characteristic that 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, the IO to write data is reduced.
But it introduces a new IO, that is, the IO that writes redo log. If the IO performance of redo log is not good, it can not be the purpose of lifting high 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. Therefore, the space of the log file is fully allocated when the system is first started.
Record redo log in sequential append mode to improve performance through sequential IO.
B. Bulk write logs. Instead of writing directly to the file, the log writes redo log buffer first. When you need to flush logs to disk
(such as transaction commits), writing many logs together to disk.
C. Concurrent transaction sharing redo log storage space, their redo log in the order of execution of the statement, sequentially alternating records together,
To reduce the space occupied by the log. For example, the record in Redo log might be something like this:
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 redo log to disk, the log of other uncommitted transactions is also written to disk.
E. Redo log only for sequential append operations, and when a transaction needs to be rolled back, its Redo log record does not
Delete the Redo log.

02– Recovery (Recovery)

-Recovery Strategy
The redo log is also logged when the uncommitted transactions and rolled-back transactions are made, so that when the recovery occurs, these transactions are subject to special
The processing of the. There are 2 different recovery strategies:

A. When recovering, only the transactions that have already been committed are re-made.
B. When recovering, redo all transactions include 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 the redo log, anddo not care about transactional。 When recovering, there is no begin and no commit,rollback behavior.
Nor do you care which transaction each log is. Although transaction-related content such as transaction ID will be credited to redo Log, the content is only treated as
Part of the data to manipulate.
B. Using the B policy, you must persist the undo log, and you must write the corresponding undo log to disk before writing redo log.
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.
The redo log that contains 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: &LT;TRX3,Undo Log Insert<undo_delete ...>>
Record 6: <trx3, delete ...>
C. Here, there is one more question that is not clear. Since redo is not transactional, would it not be re-executing the transaction that was rolled back?
That's true. At the same time, InnoDB also records the operation of the transaction when it is rolled back into the redo log. Rollback operations are essentially
The data is modified so that 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 Bto 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 ()

Reprint to: http://www.mysqlops.com/2012/04/06/innodb-log1.html

"Reprint" MySQL log Undo | Redo