We know that transactions have four characteristics: atomicity, consistency, isolation and persistence. So how do transactions in MySQL guarantee these features?

InnoDB, MySQL’s storage engine, uses redo logs for consistency and persistence, rollback logs for atomicity, and locks for isolation.

Redo log

In case there are dirty pages not written to disk at the time of the failure, the MySQL service will be restarted according to the redo log to achieve the persistence of the transaction feature. Redo log consists of two parts, one is the redo log cache in memory, which is volatile; The second is the redo log file, which is persistent. Redo logs are physical logs that record changes made to each page. Innodb storage engine writes redo logs to the cache (Innodb_log_buffer) after the transaction starts. Logs from the InnoDB log buffer are then flushed to disk in one of three ways.

  1. The Master Thread refreshes Innodb_log_buffer to redo log files once per second.
  2. The redo log is flushed to the redo log file as each transaction commits.
  3. When less than half of the redo log cache space is available, the redo log cache is flushed to the redo log file

When a transaction commits, all logs for that transaction must first be written to a redo log file for persistence. To ensure that every log is written to the redo log file, InnoDB storage engine needs to call the fsync operation once every time the log buffer is written to the redo log file. But it’s not absolute. You can control the flush policy for redo logs to disk by changing the innodb_flush_LOG_at_trx_commoit parameter. The default value of the parameter is 1, which means that the fsync operation must be invoked once the transaction commits. 0 indicates that when a transaction commits, the redo log cache does not write to the redo log file immediately, but fsync operations are performed at intervals with the Master Thread. 2 indicates that the redo log is written to the redo log file when the transaction is committed, but only to the cache of the file system. Fsync is not performed. In the InnoDB storage engine. Redo logs are stored in 512 bytes, called redo log blocks, which are the same size as the disk sector, meaning that redo logs can be written atomically without doublewrite technology.

Rollback log (undo log)

The log needs to be rolled back when the transaction is abnormal. Rollback logs are different from redo logs. They are logical logs, and changes to the database are logically cancelled. When a transaction rolls back, it actually does the opposite of what it did before. For each INSERT, the InnoDB storage engine completes a DELETE; For each UPDATE, the InnoDB storage engine performs a reverse UPDATE.

Rollback logs are generated at the start of a transaction according to the current version, and transactions can also use rollback logs for multi-version concurrency control to ensure transaction isolation. The rollback log is not deleted immediately after the transaction commits, because there may be other transactions that need to rollback the log to get version information. So the rollback log is placed in the linked list when the transaction commits, and whether it can be deleted is determined by the Purge thread.

Going back to redo logging, uncommitted transactions and rolled back transactions also generate redo logs. InnoDB storage engine will redo all transactions including uncommitted transactions and rolled back transactions, and then roll back those uncommitted transactions through the rollback log. Using this strategy requires the rollback log to be written to disk before the redo log, which complicates persistence. To reduce complexity, InnoDB storage engine uses rollback logs as data, and operations that record rollback logs are also recorded in redo logs. This allows the rollback log to be cached as data rather than written to disk before the log is overwritten.

Simplification of Undo + Redo transactions

Suppose we have data A and B, and the values are 1,2.

  1. The transaction start
  2. Record A=1 to undo log
  3. To modify A = 3
  4. Log A=3 to the redo log
  5. Record B=2 to undo log
  6. Modified B = 4
  7. Log B=4 to redo log
  8. Write redo log to disk
  9. Transaction commit

Binary log

There is also a binary log in the MySQL database for point-in-time restore and master-slave replication. On the surface, this is very similar to redo logging in that it logs database operations. But there’s a big difference in nature. First redo logs are generated in the InnoDB storage engine layer, while binary logs are generated in the MySQL database layer. Second, the two types of logging have different forms of content. Binary logs are logical logs that record the corresponding SQL statements. Redo logs are physical logs that record changes made to each page. In addition, the two types of log records are written to disk at different points in time, with binary logs being written only once after the transaction has committed, and redo logs being written continuously while the transaction is in progress.

@COPY

MySQL Tech Insider :InnoDB Storage Engine

MySQL redo log, undo log, and binary log summary

MySQL Database InnoDB storage engine Log roaming