preface

We know how a select statement is executed. In the case of an UPDATE statement, the procedure is the same as for a query statement. Before the statement is executed, you need to connect to the database. This is the connector’s job. MySQL 8.0 has removed the query cache from the MySQL database. MySQL 8.0 has removed the query cache from the MySQL database. MySQL 8.0 has removed the query cache from the MySQL database. Next, the analyzer, optimizer, and actuator do their jobs and query the result set back to the client.

Unlike select, update also involves two important log files: the redo log and the binlog

redo log

Conversely, what would happen if these two logs did not exist?

The update operation is actually divided into two steps. First, the corresponding row records are queried, and then the update operation is performed according to the conditions. If you do not have the redo log, MySQL will update the disk file with each update. If you do not have the redo log, MySQL will update the disk file with each update. If you do not have the REDO log, MySQL will update the disk file with each update. To solve this problem, the InnoDB engine designers came up with a solution: write the log to the Redo log and update the memory. At this point, the update is complete, and the memory is much faster than the disk. At the same time, InnoDB updates the redo log to disk files when appropriate. This update is usually done when the system is idle.

Each update should be written to the redo log. What if the redo log is full and runs out of space?

InnoDB has a fixed size redo log. For example, a set of 4 files can be configured with a size of 1GB each. InnoDB will write from the first file until the fourth file is full. Go back to the beginning of the first file and loop, as shown below.

[Photo from the Internet]

Write pos is the position of the current record, moving backwards as you write, and returning to the beginning of file 0 after you write to the end of file 3. “Checkpoint” is the current location to erase. It also moves forward and loops. Before erasing the record, update the record to the data file.

The blank space between write POS and checkpoint is used to record new operations. If write POS reaches the checkpoint, the redo log is full and no new updates can be performed. The log should stop and erase some records to push the checkpoint forward.

With the redo log, InnoDB can guarantee that all previously committed records will not be lost if the database restarts abnormally. This capability is called crash-safe.

binlog

Redo log is a log file in the execution engine layer. MySQL is divided into Server layer and engine layer. Binlog is a log file in the Server layer

So why does InnoDB have a log file and MySQL has a log file?

Since MySQL did not have InnoDB engine, MyISAM engine was used before MySQL5.5, but MyISAM does not have crash-safe capability, and binlog can only be used for archiving. InnoDB was later introduced as a plug-in as an engine for MySQL. Since the capability of Crash-Safe cannot be achieved by binlog alone, InnoDB uses another logging system, the Redo Log.

Update Operation Process

update T set c=c+1 where ID=2;

1. The actuator first queries the data in the row id = 2 through the engine, where ID is the primary key, directly traverses the primary key index tree and inserts the data in the row. If the data page of the row is in the memory, the result is directly returned to the actuator; otherwise, it needs to read the data into the memory from disk first and then return it.

2. The actuator gets the row data given by the engine, adds the value +1 to get a new row of data, and then calls the engine interface to write this row of data.

3. The engine updates the row to memory and records it in the redo log. At this time, the redo log is in the Perpare state, informing the executor that the update is complete and the transaction is ready to commit.

4, the executor generates the binlog of this operation and writes the binlog to disk

5. The executor calls the commit transaction interface of the engine. The engine changes the redo log to commit, and the update is complete

The following figure shows the execution process of the UPDATE statement. The dark color represents the execution in the MySQL executor and the light color represents the execution inside InnoDB.

[Photo from the Internet]

Two-phase commit

Writing the redo log consists of two steps: PREPARE and COMMIT. This is called a two-phase commit.

The reason for two-phase commit is to keep the redo log and binlog files consistent. Let’s use reductio AD absurda again to illustrate what happens if there is no two-stage mention:

  • If the redo log is complete and the binlog is not complete, MySQL restarts unexpectedly. If the redo log is complete and the binlog is not complete, MySQL can restore the redo log, even if the system crashes. If you use the binlog to restore the temporary library, you will lose the operation. If you use the binlog to restore the temporary library, you will lose the operation. If you use the binlog to restore the temporary library, you will lose the operation. The restored data is different from the value of the original library.
  • If the log file crashes after the redo log is written, the transaction is invalid because the redo log has not been written, so the data in the disk data file will not be operated by this statement, but will be logged in the binlog. Therefore, when we use this binlog to do data recovery, we have an additional transaction operation, which is inconsistent with the data of the original library.

Without two-phase commit, operations recorded in the redo log and binLog are inconsistent, and the state of the database may be inconsistent with the database data recovered from its logs.

The redo log is in the prepare state, and the redo log is in the binlog state. The transaction is committed. Set the redo log to the COMMIT state.

Redo log and binlog

  1. The Redo log is InnoDB engine specific; Binlog is implemented in the MySQL Server layer
  2. The redo log is a physical log that records changes made to a data page. A binlog is a logical log that records the original logic of a statement. Such asupdate T set c=c+1 where ID=2;For this SQL, the redo log records:Xx page number, xx offset data changed to XXX;The binlog records:Id = 2; id = 2; id = 2
  3. The redo log is written in cycles and runs out of fixed space. A binlog can be appended. When a file is full, it switches to the next file and does not overwrite the previous record
  4. The redo log records DML and DDL statements after a transaction is initiated. Binlog records DML and DDL statements after the commit is complete
  5. The redo log is used for data recovery after abnormal downtime or media failure. Binlog is used for recovery data, and master/slave replication is set up.

undo log

The undo log and redo log are also engine layer log files. The Undo log provides rollback and multiple line versioning (MVCC). During database modification operations, not only the redo log but also the undo log is logged. If the transaction fails and is rolled back for some reason, you can use the undo log to roll back the transaction.

While both the Undo log and redo log are unique to InnoDB, the Undo log is a logical log and the Redo log is a physical log. Update T set c= C +1 where ID=2; update T set c= C +1 where ID=2; For this SQL, the value of C before +1 is recorded in the Undo log. If the update is abnormal and needs to be rolled back, you can use the undo log to implement rollback to ensure transaction consistency.

Multi-version concurrency control (MVCC) also uses undo log. When a row is read by another transaction, it can obtain the previous data of the row from the Undo log to provide the version information of the row, so that the user can achieve non-lock consistent read.

Undo records are logged to the system tablespace (ibdatA1) by default, but starting with MySQL5.6, separate undo tablespaces are available. Don’t worry about undo making the ibData1 file bigger.

An undo log is recorded in segments. Each undo operation occupies one undo log segment

Rollback segments are called rollback segments. Each rollback segment contains 1024 undo log segments. In previous versions, only one undo log segment is supported. MySQL 5.5 supports 128 rollback segments (128 x 1024 undo operations). Innodb_undo_logs allows you to customize the number of rollback segments (128 by default)

conclusion

The redo log is used to ensure that the database is crash-safe, the binlog is used to ensure that the database state can be restored to any point in time, and the undo log is used to ensure that the transaction needs to be rolled back and the MVCC is used to record the data version information.