This is the 30th day of my participation in the August Text Challenge.More challenges in August
SQL update statement
The whole SQL update process and query statement process is basically the same. Only the redo log and binlog sections were added in the executor phase
redo log
InnoDB engine specific to ensure crash_safe capability. WAL write-Ahead Logging is used, that is, Logging is written before disk
The differences between redo log and binlog are as follows:
| redo log | binlog |
|---|---|
| InnoDB engine is unique | Server layer, available to each storage engine |
| Physical logs – What are the operations performed on this data page | Logical log – The original logic of the statement |
| I’m going to loop, I’m going to run out of space | Appending, data will not be overwritten |
The specific process
Graph TD A --> B{if the data page is in memory}; B - > | No | C [read from disk into memory] - > D/return rows of data; B ---->|Yes| D; D --> E[calculate row data]; E --> F[call engine returns new line]; F --> G[new row updated to memory]; G --> H[redo log]; H --> I[write binlog]; I --> J[commit transaction, redo log becomes COMMIT];Two-phase commit
The two-phase commit is used to ensure the logical consistency of the two logs and avoid data inconsistency caused by an abnormal MySQL restart.
If not two-phase commit
- Description Binlog abnormally restarts after the redo log is complete. At this point, the redo log contains the updated state, while the bin log still contains the original state. Data can be restored thanks to the redo log crash_safe capability. But once you restore the temporary library with binlog, you will find that the values are different from the original library.
- Description An abnormal restart occurred during the binlog writing. At this point, the bin log is the original value, and it will remain the same after crash recovery. However, the value in binlog is new, and then using binlog to restore the value will be an additional transaction, which is different from the original value.
If two phases commit
Description Binlog abnormally restarts after the redo log is complete. The redo log is in the prepare state and fails in the binlog. Therefore, the transaction itself is rolled back to its original value. If binlog restores a library to the same value, the logic is consistent
Redo_log – Innodb_flush_log_at_trx_commit set to 1 – indicates that the redo log of the transaction is persisted directly to disk
The binlog-sync_binlog parameter is set to 1 – to indicate that the binlog of each transaction is persisted to disk.
SQL update statement
The whole SQL update process and query statement process is basically the same. Only the redo log and binlog sections were added in the executor phase
redo log
InnoDB engine specific to ensure crash_safe capability. WAL write-Ahead Logging is used, that is, Logging is written before disk
The differences between redo log and binlog are as follows:
| redo log | binlog |
|---|---|
| InnoDB engine is unique | Server layer, available to each storage engine |
| Physical logs – What are the operations performed on this data page | Logical log – The original logic of the statement |
| I’m going to loop, I’m going to run out of space | Appending, data will not be overwritten |
| ### Specific process |
Graph TD A --> B{if the data page is in memory}; B - > | No | C [read from disk into memory] - > D/return rows of data; B ---->|Yes| D; D --> E[calculate row data]; E --> F[call engine returns new line]; F --> G[new row updated to memory]; G --> H[redo log]; H --> I[write binlog]; I --> J[commit transaction, redo log becomes COMMIT];Two-phase commit
The two-phase commit is used to ensure the logical consistency of the two logs and avoid data inconsistency caused by an abnormal MySQL restart.
If not two-phase commit
- Description Binlog abnormally restarts after the redo log is complete. At this point, the redo log contains the updated state, while the bin log still contains the original state. Data can be restored thanks to the redo log crash_safe capability. But once you restore the temporary library with binlog, you will find that the values are different from the original library.
- Description An abnormal restart occurred during the binlog writing. At this point, the bin log is the original value, and it will remain the same after crash recovery. However, the value in binlog is new, and then using binlog to restore the value will be an additional transaction, which is different from the original value.
If two phases commit
Description Binlog abnormally restarts after the redo log is complete. The redo log is in the prepare state and fails in the binlog. Therefore, the transaction itself is rolled back to its original value. If binlog restores a library to the same value, the logic is consistent