MySQL事务 - 自增ID的回滚以及Auto Increment在InnoDB的实现

版权声明：本文为博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/BDuck2014/article/details/81709777
自增ID未回滚
首先做一个测试

CREATE TABLE auto_inc_test( id int auto_increment, test_id int, primary key id(id))ENGINE=InnoDB;

mysql> start transaction;
mysql> INSERT INTO auto_inc_test (test_id) values (1);
mysql> INSERT INTO auto_inc_test (test_id) values (2);
mysql> INSERT INTO auto_inc_test (test_id) values (3);
mysql> rollback;

mysql> INSERT INTO auto_inc_test (test_id) values (1);
mysql> SELECT * FROM auto_inc_test;
+----+---------+
| id | test_id |
+----+---------+
|  3 |       1 |
+----+---------+
1 row in set (0.01 sec)
可以看到rollback之后新增一行数据，id列并没有跟随test_id列一起回滚清空，而是继续从3开始增长。

这是数据库有意设置成不跟随回滚的，因为没有办法解决多个事务commit/rollback时自增id变化的问题。

举例一种情况：

业务1开启事务 
业务1插入一条数据，得到一个id（自增列），假设为666 
业务2开启事务，插入一条数据，得到一个id，为667 
业务2 commit 
业务2打印出这条数据 
业务1回滚

这时如果自增ID跟着回滚，但是业务2已经使用上了id 667，所以如果说要让id回滚，业务2的数据也要回滚，这个矛盾是不能解决的，因此设计成自增列不会跟着回滚。

Auto Increment的实现
InnoDB的自增（Auto Increment）是通过一个自增计数器（AUTO_INCREMENT counters）来实现的。 
这个计数器的计数存储分两种： 
1、MySQL5.7及更早的版本中，计数存储在内存（Main Memory）中，也就是说，每次关机、重启都会导致计数器的计数丢失。因此，当重启系统/清空内存等操作之后，在下一次插入数据的时候，会首先执行如下语句以将正确的计数值赋予自增计数器：

SELECT MAX(auto_increment_field) FROM table_name FOR UPDATE;
1
2、在最新的MySQL8中，每一次新增数据行，或者说每次自增计数器的变动，当前最新的自增计数器数值都会被写入到redo log中，同时还会在每次checkpoints保存至engine-private system table。这让计数器的数值不会因为重启而丢失，因为redo log是一种disk-based的存储，用于在系统crash时恢复数据用的。

延伸阅读 
redo log ：The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. （https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html） 
checkpoint：As changes are made to data pages that are cached in the buffer pool, those changes are written to the data files sometime later, a process known as flushing. The checkpoint is a record of the latest changes (represented by an LSN value) that have been successfully written to the data files.（https://dev.mysql.com/doc/refman/8.0/en/innodb-checkpoints.html）

存储之后轮到crash后自增列的恢复，系统会先获取表中最大的自增列数值，记为v1，同时又获取上一次checkpoints后redo log记录的计数器值，记为v2，最终生效的为v1、v2中更大的值作为当前的counter值。v1大于v2的情况应该是crash前未来得及写入到redo log的情况。

更多InnoBD实现Auto Increment的内容参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html