Shulou(Shulou.com)05/31 Report--

This article will explain in detail what undo log and redo log are in mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

00-Undo Log

Undo Log is to realize the atomicity of transactions. In the MySQL database InnoDB storage engine, Undo Log is also used to implement multi-version concurrency control (MVCC for short).

-atomicity of transactions (Atomicity)

All the operations in the transaction are either completed or nothing is done, and only part of the operation can be done. If it occurs in the course of execution

When an error occurs, Rollback it to the state it was before the transaction started, as if the transaction had never been executed.

-principle

The principle of Undo Log is very simple. In order to satisfy the atomicity of the transaction, the data is backed up to a place before any data is manipulated.

(the place where data backups are stored is called Undo Log.) Then modify the data. If an error occurs or if the user executes

ROLLBACK statement, the system can use the backup in Undo Log to restore the data to the state it was before the transaction began.

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

-transaction persistence (Durability)

Once the transaction is completed, all changes made by the transaction to the database will be persisted to the database. To ensure persistence, the database

The system will completely record the modified data to persistent storage.

Simplified process of atomic and persistent transactions using Undo Log

Suppose there are two data An and B, and the values are 1 and 2 respectively.

a. The business begins.

b. Record Agg1 to undo log.

c. Modify Atom 3.

d. Record bread2 to undo log.

e. Modify Bamboo 4.

f. Write undo log to disk.

g. Write the data to disk.

h. Transaction commit

There is an implicit prerequisite: 'the data is first read into memory, then modified, and finally written back to disk'.

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 have been persisted.

C. Undo log must be persisted to disk before the data is persisted. If the system crashes between GMAH, the undo log is complete.

Can be used to roll back transactions.

d. If the system crashes between Amurf because the data is not persisted to disk. So the data on disk remains the same as it was before the transaction started.

Flaw: data and Undo Log are written to disk before each transaction is committed, which results in a large amount of disk IO, so performance is very low.

If you can cache data for a period of time, you can reduce IO and improve performance. But this loses the durability of the transaction. So another one was introduced.

Mechanism to achieve persistence, that is, Redo Log.

01-Redo Log

-principle

In contrast to Undo Log, Redo Log records a backup of new data. Just persist the Redo Log before the transaction commits

There is no need to persist the data. When the system crashes, the data is not persisted, but the Redo Log is persisted. The system can be based on

The contents of Redo Log to restore all data to the latest state.

-simplified process of Undo + Redo transactions

Suppose there are two data An and B, and the values are 1 and 2 respectively.

a. The business begins.

b. Record Agg1 to undo log.

c. Modify Atom 3.

d. Record Aban 3 to redo log.

e. Record bread2 to undo log.

f. Modify Bamboo 4.

g. Record bread4 to redo log.

h. Write redo log to disk.

i. Transaction commit

-characteristics of Undo + Redo transactions

a. To ensure persistence, the Redo Log must be persisted before the transaction commits.

b. Data does not need to be written to disk before the transaction is committed, but is cached in memory.

C. Redo Log guarantees the persistence of transactions.

D. Undo Log guarantees the atomicity of the transaction.

e. An implicit feature is that data must be written to persistent storage later than redo log.

-IO performance

The design of Undo + Redo is mainly about improving the performance of IO. Although by caching data, the IO of writing data is reduced.

But a new IO, the IO for writing Redo Log, is introduced. If the IO performance of Redo Log is not good, it will not achieve the purpose of improving performance.

In order to ensure that Redo Log can have better IO performance, the design of InnoDB Redo Log has the following characteristics:

a. Try to keep the Redo Log stored in a continuous space. Therefore, the log file space is fully allocated when the system starts for the first time.

Redo Log is recorded as sequential appends, and performance is improved by sequential IO.

b. Write to the log in bulk. The log is not written directly to the file, but first to redo log buffer. When you need to flush the log to disk

(such as transaction commit), write many logs to disk together.

c. Concurrent transactions share the storage space of the Redo Log, and their Redo Log are recorded alternately in the order in which the statements are executed.

To reduce the space consumed by the log. For example, a record in Redo Log might look like this:

Record 1:

Record 2:

Record 3:

Record 4:

Record 5:

d. Because of C, when a transaction writes Redo Log to disk, the logs of other uncommitted transactions are also written to disk.

Only sequential append operations are performed on E. Redo Log, and when a transaction needs to be rolled back, its Redo Log record does not derive from the

Delete it from Redo Log.

02-restore (Recovery)

-recovery strategy

As mentioned earlier, uncommitted and rolled-back transactions also record Redo Log, so when resuming, these transactions perform a special

To deal with. There are 2 different recovery strategies:

a. When resuming, only the committed transactions are redone.

b. When recovering, redo all transactions, including uncommitted transactions and rolled-back transactions. And then roll back those through Undo Log

Uncommitted transactions.

-recovery mechanism of InnoDB storage engine

The MySQL database InnoDB storage engine uses the B strategy, and the recovery mechanism in the InnoDB storage engine has several characteristics:

a. Transactionality is not a concern when redoing Redo Log. When recovering, there is no BEGIN, and there is no COMMIT,ROLLBACK behavior.

I don't care which transaction each log belongs to. Although transaction-related contents such as transaction ID are recorded in Redo Log, they are only treated as

Part of the data to be manipulated.

b. Using strategy B requires that the Undo Log be persisted and the corresponding Undo Log must be written to disk before writing the Redo Log.

This association between Undo and Redo Log complicates persistence. To reduce complexity, InnoDB sees Undo Log as

Data, so the operation of recording the Undo Log is also logged to the redo log. So that undo log can be cached like data.

Instead of writing to disk before redo log.

The Redo Log that contains the Undo Log operation looks like this:

Record 1:

Record 2:

Record 3:

Record 4:

Record 5:

Record 6:

c. At this point, there is still one question that has not been clarified. Since Redo is not transactional, won't the rolled-back transaction be reexecuted?

Yes, indeed. At the same time, Innodb also records the operation when the transaction is rolled back to redo log. The rollback operation is essentially the same

Make changes to the data, so the operations on the data during the rollback are also recorded in the Redo Log.

The Redo Log of a rolled-back transaction looks like this:

Record 1:

Record 2:

Record 3:

Record 4:

Record 5:

Record 6:

Record 7:

Record 8:

Record 9:

The recovery operation of a rolled-back transaction is redo and then undo, so it does not break the consistency of the data.

-related functions in the InnoDB storage engine

Redo: recv_recovery_from_checkpoint_start ()

Undo: recv_recovery_rollback_active ()

Redo Log of Undo Log: trx_undof_page_add_undo_rec_log ()

This is the end of the article on "what is undo log and redo log in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.