版权声明:本文为博主原创文章,遵循 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