Mysql自增锁(Auto-inc Locks)用法

当表中存在 AUTO_INCREMENT 列时，MySQL 在生成自增值的过程中，需要一种机制保证并发插入时不产生重复 ID，这就涉及 自增锁。

这个锁用于 控制自增值分配的顺序和唯一性，而不是保护整行数据。

自 MySQL 5.1 起支持通过参数innodb_autoinc_lock_mode来控制自增锁模式，常见值有 0 / 1 / 2。

1 2 3 4 5 6 7 8 9

#查看 SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';   #运行时设置 SET GLOBAL innodb_autoinc_lock_mode = 2;   #配置设置 [mysqld] innodb_autoinc_lock_mode = 1

1、innode_autoinc_lock_mode=0（传统模式）

1）特点

• 插入时使用 表级 AUTO-INC 锁，也就是说：如果一个事务正在往表中插入记录，所有其他事务的插入必须等待。
• 串行生成自增 ID，锁一直持有到 语句结束

说这种模式下可以保证，一个事物内，一次insert插入多条，或多次insert插入数据的ID一定是连续的，不受其他事物影响（其他事物需要等待）。但是也无法保证整张表的ID都是连续的，因为存在回滚。

2）问题

• 并发插入性能差，容易成为瓶颈

3）易混淆点

innodb_autoinc_lock_mode = 0 只解决“并发插入顺序”问题，并不能解决“事务回滚回收自增 ID”的问题。

也就是说：

• 同一事务内、多事务并发下，插入顺序是串行的
• 回滚的自增 ID 不会被重用，所以事务回滚仍然会导致自增 ID 跳号。

所以，不要在代码中使用自增ID作为业务连续性的逻辑处理。

2、innode_autoinc_lock_mode=1（默认，连续模式）

性能和安全性这种，多数业务推荐使用。

1）特点

• Simple INSERT：几乎不加表锁
• Bulk / 非确定性 INSERT：@www.xuepai.net仍使用 AUTO-INC 锁
• 这种模式下可以支持：保证同一条语句内自增值连续（insert into table(a,b,c) values (), ()...）

什么是Simple INSERT？

1 2 3 4 5 6 7

#一下为Simple INSERT INSERT INTO t(a) VALUES (1); INSERT INTO t(a) VALUES (1),(2);   #非 Simple INSERT INSERT INTO t(a) SELECT ...; LOAD DATA INFILE ...

2）和AUTO-INC锁对比

上面介绍了，AUTO-INC模式下可以保证一个事物内的插入操作产生的ID是连续的；

而autonic_lock_mode=1这种模式下只能保证一个insert多次插入的ID是连续的。

因为，在这种模式下，AUTO_INCREMENT 的分配与事务提交无关，只与“INSERT 语句执行顺序”有关，所以同一个事务内多次 INSERT，也不能保证 ID 连续

看一个例子：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

#1.事务A插入，但是不提交，插入后id=1 BEGIN; INSERT INTO t(v) VALUES (1);   #2.事务B插入，并提交，此时id=2 BEGIN; INSERT INTO t(v) VALUES (2); COMMIT;   #3.事务A继续插入，插入后id=3 INSERT INTO t(v) VALUES (3);   #4.这时在事务A中插叙，发现id为1和3 SELECT id, v FROM t;

可以看到，因为没有了锁，ID自增器类似于一个发号器，每个insert都从这里取号。

3、innode_autoinc_lock_mode=2（交错模式/无锁模式）

1）特点

• 完全不使用 AUTO-INC 表锁，多线程并发性能最好
• 自增值只保证唯一，不保证连续@www.haoshilao.net

这种模式下，即使同一条 INSERT 内 ID 可能不连续，即：出现跳号。

2）例子

1 2 3 4 5 6 7 8

#线程A INSERT INTO t(a) VALUES (1),(2);   #线程B INSERT INTO t(a) VALUES (3),(4);   #查询结果 实际 ID 可能交错：1,3,2,4