Understand undo logs, redo logs, checkpoints in the database

Understand undo logs, redo logs, checkpoints in the database

2014-6-18

Original: https://www.letiantian.me/2014-06-18-db-undo-redo-checkpoint/

The database holds the data file, which is referred to in this document.
The contents of the database are cached in memory, which is named DB buffer. One operation, we take the data from a table in the database, and this data is cached in memory for some time. The modification of this data at the beginning also only modifies the contents in memory. When DB buffer is full or otherwise encountered, it is written to the data file.

Undo,redo

The log is also cached in memory, which is called log buffer. The log files on the disk are called log file. The log file is generally appended, and can be considered sequential, and the disk IO overhead of sequential writes is less than the random write.

The Undo log records the value before a data is modified, which can be used to rollback;redo the value of a data block when a transaction fails, and can be used to recover data that has been updated by a successful transaction that has not been written to the data file. The following example is from Yang Shunhui, "Principles and architecture practices for big data distributed storage Systems", with minor changes.

For example, the transaction sequence number for a transaction is T1, it modifies the data x, the original value of X is 5, the modified value is 15, then the Undo log is <T1, X, 5> , and the redo log is <T1, X, 15> .

There is also the practice of combining undo and redo, called the Undo/redo log, in this example Undo/redo log as <T1, X, 5, 15> .

When a user generates a database transaction, undo log Buffer records the original value of the modified data, and redo records the updated value of the modified data.

The redo log should be persisted on the disk first, and then the operation result of the transaction is written to DB buffer, (at this point, the data in memory is different from that of the database file, we think the data in memory is dirty), and DB buffer chooses the right time to persist the data File. This sequence ensures that the last modification is resumed when a recovery is required. The policy of persisting the log first is called Write Ahead Log , that is, the pre-write log.

In many systems, the undo log is not stored in a log file but is placed in a special section inside the database. In this article, these storage behaviors are generalized to the undo log file.

For a transaction T, the record in log file must start at the start of the transaction (such as "Start T") and end at the end of the transaction tag (such as "End T", "Commit T"). In the case of system recovery, if a transaction does not have a transaction end tag in the log file, the transaction needs to be undo and redo if there is a transaction end tag.

Before the content in DB buffer is written to the disk database file, the contents of the log buffer should be written to the disk log file.

There is a problem, what is the number of transactions stored in redo log buffer and undo log buffer, and what are the rules for writing logs to log file? If the number of transactions stored is 1, it means that the log is immediately flash into the disk, then the consistency of data is very good assurance. When doing a thing T, a sudden power outage, if the redo log file on the disk does not have an append operation, you can consider this transaction T as unsuccessful. If the redo log file is modified, the transaction is considered successful, and the restart database uses redo log to recover the data to DB buffer and to data file.

If you store more than one, it's really good to explain. That is, before the DB buffer is written to the data file, the log is written to log file. This way, you can reduce disk IO and increase throughput. However, this approach applies to situations where conformance requirements are not high. The completed transactions in log buffer and DB buffer will be lost if there is a system failure such as a power outage. For example, if a user's transfer transaction is lost in this case, this means that the user needs to be re-transferred after the system resumes.

Check Point checkpoint

The checkpoint is intended to periodically flush the contents of DB buffer to the data file. When an out-of-memory, DB buffer is full, the contents/portions of DB buffer (especially dirty data) need to be dumped into the data file. At the time of dumping, the "moment" that checkpoint occurs is recorded. At the time of failback, you only need to redo/undo the last checkpoint operation.

idempotent problems

The record of operations in the log file should be idempotent. Idempotent, which means that the same operation executes multiple times and executes once, the result is the same. For example, the 5*1 = 5*1*1*1 multiply 1 operation of 5 is idempotent. Log files in the recovery, may be played back several times (such as the first playback to half of the system power off, have to replay), if the operation record does not meet the idempotent, can cause data errors.

Information

What is Undo?
Redo Log
Oracle Learning Note: Redo log (redo log) function
Database log files –undo log, redo log, Undo/redo log
Analysis of the principle of undo log and redo log
Analysis of Oracle Redo and undo
Redolog Checkpoint and SCN relationships
MySQL DBA system Learning (INNODB) The principle of the redo log of the engine
2.4 Checkpoint Technology
MySQL InnoDB log mechanism in-depth analysis
"MySQL Technology insider: InnoDB Storage Engine (2nd edition)"

Reference: https://www.cnblogs.com/xinysu/p/6555082.html

Understand undo logs, redo logs, checkpoints in the database