Two issues of MYSQL transaction rollback

Therefore, correct atomic operations are actually executed. Is physical execution.

You can see the inserted records in the current transaction. Finally, it is deleted. However, AUTO_INCREMENT does not need to be deleted and the value is changed.

1. Why is auto_increament not rolled back?

Because the current value of the innodb auto_increament counter record is stored in the memory, not on the disk, when the mysql server is running, this Count value will only increase with the change of insert, and will not decrease with the decrease of delete. When mysql server is started, when we need to query the auto_increment Count value, mysql will automatically execute: select max (id) FROM table name for update; statement to obtain the maximum value of the current auto_increment column, and then put this value in the auto_increment counter. Therefore, even the Rollback MySQL auto_increament counter does not perform negative operations.

2. Are MySQL transactions physical operations performed on tables?

MySQL transactions have redo and undo operations, and all information of redo operations is recorded in redo_log. That is to say, when a transaction performs a commit operation, you need to write the transaction operation to redo_log first, and then flush these operations to the disk. When a fault occurs, you only need to read redo_log, then, flush the disk again.

However, it is troublesome for undo. when MySQL processes transactions, it will apply for a segment in the data sharing tablespace called the segment and save the undo information. When it is processing rollback, it is not a complete physical undo, but a logical undo, that is, the previous operations will be reversed, but these shared tablespace will not be recycled. The recovery of these tablespaces must be performed by the mysql master thread process.