Transaction log for Mysql-innodb

"Reference book: MySQL technology insider InnoDB Storage Engine" "References some blog content"

The isolation of transactions is implemented by the locking mechanism, and the atomicity, consistency, and persistence of transactions are accomplished through InnoDB's redo log and undo log.

Redo log: Known as redo logs, is used to guarantee the atomicity and persistence of transactions.

Undo log: Used to guarantee the consistency of the transaction.

The difference between the two:

Undo log: Records the value of a data modification before it can be used to rollback when a transaction fails. Undo is a logical log that is recorded according to each line of records.

Redo log: Used to record the value after the data has been modified to recover data that has not been written to datafile but has been successfully commit. Redo log is usually a physical log that records the physical modification of a page.

Redo log:

Redo logs are used to achieve the persistence of transactions, there are two parts: first: The memory of the redo log buffer, easy to lose; second: Redo log file, is persistent.

generation of redo log logs

To minimize performance problems with IO bottlenecks when data is written, MySQL uses a caching mechanism: When query modifies data in the database, InnoDB first reads the data from the disk into memory, modifies the in-memory copy of the data, and persist the modified behavior to the transaction log on disk (write redo log buffer first, and then batch write periodically), instead of logging the modified data directly to the hard disk each time, and so on, after the transaction log is persisted, the dirty data in memory can be slowly brushed back to the disk, called Write-ahead Logging. The transaction log uses append writes, and sequential IO provides a better performance advantage.

In order to avoid data loss problems caused by power-down or system failure during dirty data brush back to disk, InnoDB uses transaction log (redo log) to solve the problem

The Undo log log generates:

To satisfy the atomicity of a transaction, before any data is manipulated, the data is first backed up to the undo Log, and then the data is 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. Unlike redo log, a separate undo log file does not exist on the disk, and it is stored in a special segment (segment) inside the database, which is called the Undo segment (undo segment), and the Undo field is in the shared table space.

An example is used to illustrate the use of undo log with redo log:

5 >.

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.

Idempotent:

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.

"This blog post refers to many times"

50885693

40537839

Wait a minute

Transaction log for Mysql-innodb