可以看出下一个自增列值为 103，因为自增列的值是在每条插入语句执行时分配的，而不是一开始就分配完的。
innodb_autoinc_lock_mode = 1 (root@localhost) [test] > show global variables like 'innodb_autoinc_lock_mode'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 1 | +--------------------------+-------+ 1 row in set (0.01 sec) -- 1、开启session 1执行TRX1 TRX1: insert into t1(c2) select c2 from t2; (root@localhost) [test] > insert into t1(c2) select c2 from t2; Query OK, 1000000 rows affected (5.88 sec) Records: 1000000 Duplicates: 0 Warnings: 0 -- 2、在TRX1执行期间开启session 2执行TRX2 TRX2: insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); (root@localhost) [test] > insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); Query OK, 4 rows affected (4.38 sec) Records: 4 Duplicates: 0 Warnings: 0 -- 3、查看TRX2插入值的记录 (root@localhost) [test] > select * from t1 where c2 in ('test_inc_a','test_inc_b','test_inc_c','test_inc_d'); +---------+------------+ | c1 | c2 | +---------+------------+ | 1 | test_inc_a | | 5 | test_inc_c | | 1048661 | test_inc_b | | 1048662 | test_inc_d | +---------+------------+ 4 rows in set (0.32 sec) -- 4、查看当前AUTO_INCREMENT值 (root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 1048665 | +----------------+ 1 row in set (0.00 sec) -- 5、查看t1表的前10行记录、后10行记录和总行数 (root@localhost) [test] > select * from t1 order by c1 asc limit 10; +-----+------------+ | c1 | c2 | +-----+------------+ | 1 | test_inc_a | | 5 | test_inc_c | | 101 | CwAkHbEWs | | 102 | re | | 103 | uqrQbj | | 104 | SQ | | 105 | ojyPkMA | | 106 | 03qNqZ | | 107 | G8J | | 108 | Uo3 | +-----+------------+ 10 rows in set (0.00 sec) (root@localhost) [test] > select * from t1 order by c1 asc limit 999994,10; +---------+------------+ | c1 | c2 | +---------+------------+ | 1000093 | o4AzuR | | 1000094 | NJMJJk | | 1000095 | 0o5xPuDnE | | 1000096 | QLLtImQC | | 1000097 | N1Fojm | | 1000098 | 6aZemarPC | | 1000099 | 4OUGSM1JzL | | 1000100 | l8g6J | | 1048661 | test_inc_b | | 1048662 | test_inc_d | +---------+------------+ 10 rows in set (0.32 sec) (root@localhost) [test] > select count(*) from t1; +----------+ | count(*) | +----------+ | 1000004 | +----------+ 1 row in set (0.17 sec)在此模式下：
TRX1：大量插入时持有 AUTO-INC 表锁，自增列的值是预先分配的，101~10000100，总共100万个连续值；
TRX2：混合插入情况下，语句为简单 INSERT 语句，有的行自增列有指定值，而有的行没有，这时TRX2是无须等待持有 AUTO-INC 锁的，由于TRX1语句还在执行，InnoDB并不知道需要分配多少个自增列值，也不清楚TRX用了多少个自增列值，所以在并行执行 INSERT 时对于未指定行的自增列值分配就会产生间隙(1000100~1048661之间的间隙)，但是语句当中分配的自增列(1048661和1048662)值依然是连续的。
可以看出下一个自增列值为 1048665 ，因为自增列值个数在语句执行开始就已经分配了4个(1048661~1048664)，但实际语句只使用了2个。
innodb_autoinc_lock_mode = 2 (root@localhost) [test] > show global variables like 'innodb_autoinc_lock_mode'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 2 | +--------------------------+-------+ 1 row in set (0.01 sec) -- 1、开启session 1执行TRX1 TRX1: insert into t1(c2) select c2 from t2; (root@localhost) [test] > insert into t1(c2) select c2 from t2; Query OK, 1000000 rows affected (4.67 sec) Records: 1000000 Duplicates: 0 Warnings: 0 -- 2、在TRX1执行期间开启session 2执行TRX2 TRX2: insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); (root@localhost) [test] > insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 -- 3、查看TRX2插入值的记录 (root@localhost) [test] > select * from t1 where c2 in ('test_inc_a','test_inc_b','test_inc_c','test_inc_d'); +--------+------------+ | c1 | c2 | +--------+------------+ | 1 | test_inc_a | | 5 | test_inc_c | | 262241 | test_inc_b | | 262242 | test_inc_d | +--------+------------+ 4 rows in set (0.28 sec) -- 4、查看当前AUTO_INCREMENT值 (root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 1048665 | +----------------+ 1 row in set (0.00 sec) -- 5、查看t1表的前10行记录、后10行记录和总行数 (root@localhost) [test] > select * from t1 order by c1 asc limit 10; +-----+------------+ | c1 | c2 | +-----+------------+ | 1 | test_inc_a | | 5 | test_inc_c | | 101 | CwAkHbEWs | | 102 | re | | 103 | uqrQbj | | 104 | SQ | | 105 | ojyPkMA | | 106 | 03qNqZ | | 107 | G8J | | 108 | Uo3 | +-----+------------+ 10 rows in set (0.00 sec) (root@localhost) [test] > select * from t1 order by c1 asc limit 999994,10; +---------+------------+ | c1 | c2 | +---------+------------+ | 1000095 | KHukB | | 1000096 | bnpyaevl79 | | 1000097 | o4AzuR | | 1000098 | NJMJJk | | 1000099 | 0o5xPuDnE | | 1000100 | QLLtImQC | | 1000101 | N1Fojm | | 1000102 | 6aZemarPC | | 1000103 | 4OUGSM1JzL | | 1000104 | l8g6J | +---------+------------+ 10 rows in set (0.22 sec) (root@localhost) [test] > select count(*) from t1; +----------+ | count(*) | +----------+ | 1000004 | +----------+ 1 row in set (0.17 sec) -- 6、查看TRX2插入时产生间隙位置行记录 (root@localhost) [test] > select * from t1 where c1 between 262240 and 262250; +--------+------------+ | c1 | c2 | +--------+------------+ | 262240 | mNfx37 | | 262241 | test_inc_b | | 262242 | test_inc_d | | 262245 | Taqi | | 262246 | Ojpakez | | 262247 | 2fKtmm6rW | | 262248 | AysE | | 262249 | wqWGs | | 262250 | lC | +--------+------------+ 9 rows in set (0.00 sec)在此模式下：
TRX1：因为大量插入时是不持有 AUTO-INC 表锁，所在TRX1在执行期间，TRX2很快就插入完成并分配了自增列值。
TRX2：简单插入语句时可以判断出须分配4个自增列值，但是只使用了2个，这样就造成了2个间隙(262243~262244)。