可以看出下一个自增列值为 1048665 ，TRX1执行时因为大量插入时无法预估插入值数量，TRX2执行时分配了4个自增值，但只使用了2个(262241~262242)，造成了2个间隙，TRX1和TRX2的自增列值是交叉分配的。
修改自增列当中的值无论 AUTO_INCREMENT 处于哪种锁模式下，更新自增列的值都有可能会产生 Duplicate entry 重复值错误。
-- 测试表： (root@localhost) [test] > create table t3 (c1 int not null auto_increment primary key); Query OK, 0 rows affected (0.06 sec) (root@localhost) [test] > show create table t3\G; *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) -- 1、插入几行数据 (root@localhost) [test] > insert into t3 values(0),(0),(3); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 (root@localhost) [test] > select * from t3; +----+ | c1 | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) (root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t3'; +----------------+ | auto_increment | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) -- 2、更新c1值为5 (root@localhost) [test] > update t3 set c1 = 5 where c1 = 3; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@localhost) [test] > select * from t3; +----+ | c1 | +----+ | 1 | | 2 | | 5 | +----+ 3 rows in set (0.00 sec) (root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t3'; +----------------+ | auto_increment | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) -- 3、再次插入几行数据 (root@localhost) [test] > insert into t3 values(0),(0),(3); ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY' (root@localhost) [test] > select * from t3; +----+ | c1 | +----+ | 1 | | 2 | | 5 | +----+ 3 rows in set (0.00 sec)可以看出更新了自增列的值之后产生了 Duplicate entry 重复值错误。建议不要更新自增列的值，或是把自增列值更新为大于 AUTO_INCREMENT 的值。
AUTO_INCREMENT计数器的初始化