MySQL InnoDB中的锁-自增锁（AUTO-INC Locks）

锁列表

• 共享与列排他锁
• 意向锁
• 记录锁
• 间隙锁
• Next-Key锁
• 插入意向锁
• AUTO-INC锁

这次我们只来讨论和实验AUTO-INC锁。

AUTO-INC锁

概述

AUTO-INC锁是表级锁，如果一张表中有自增的列（例如： id int NOT NULL AUTO_INCREMENT,）那么当向这张表插入数据时，InnoDB会先获取这张表的AUTO-INC锁。

如果一个事务正在插入数据到有自增列的表时，其他事务会阻塞等待正在持有AUTO-INC锁的事务释放AUTO-INC锁。

自增分类

“INSERT-like”语句

INSERT-LIKE：指所有的插入语句，包括： INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等。

“Simple inserts”

指在插入前就能确定插入行数的语句，包括：INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。

“Bulk inserts”

指在插入钱不能确定行数的语句，包括：INSERT … SELECT/REPLACE … SELECT/LOAD DATA。

“Mixed-mode inserts”

混合模式分为两种：

1. 插入的语句有一些自增列时确定的值，一些是不确定的。
   例如：MySQL官网给的例子，表t1有两个列（c1和c2）,其中c1列时自增列，那么构造如下SQL语句就是混合模式：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

2. INSERT … ON DUPLICATE KEY UPDATE
   这种语句会使用锁来为AUTO_INCREMENT列分配自增值，但是在更新阶段可能不会用这些分配的自增值。

锁模式（三种）

AUTO-INC锁可以使用innodb_autoinc_lock_mode变量来配置自增锁的算法。innodb_autoinc_lock_mode变量可以选择三种值如下表：

innodb_autoinc_lock_mode	变量含义
0	传统锁模式
1	连续锁模式
2	**交错锁模式(MySQL8默认) **

传统锁模式

在传统锁模式下，所有的"INSERT-LIKE"语句为AUTO_INCREMENT列使用表级AUTO_INC锁。一个事务的"INSERT-LIKE"语句在语句执行结束后释放AUTO_INC表级锁，而不是在事务结束后释放。这样做的目的是为了保证这个"INSERT-LIKE"语句的自增列的值是连续的。

连续锁模式

连续锁模式对于“Simple inserts”不会使用表级锁，而是使用一个轻量级锁来生成自增值，因为InnoDB可以提前直到插入多少行数据。自增值生成阶段使用轻量级互斥锁来生成所有的值，而不是一直加锁直到插入完成。但是如果其他事务持有AUTO_INC锁，那么“Simple Inserts”类语句也需要等待其他事务完成才能使用轻量级锁来生成所有的自增值。

连续锁模式对于“bulk inserts”类语句使用AUTO_INC表级锁直到语句完成。使用表级AUTO_INC锁的语句：INSERT … SELECT、REPLACE … SELECT、LOAD DATA 。

当innodb_autoinc_lock_mode=1时，在语句复制格式下(BINLOG_FORMAT=STATEMENT)，BINLOG中没有记录主库执行过程中获取到的所有自增值及其对应行的信息，要保证"Bulk insert"操作主从复制数据一致就必须保证语句在主库和从库执行时获取到相同自增值，而因此只能通过控制“获取连续自增值”的方式来实现，同时为避免受其他事务插入操作影响，就必须在表级别加锁且保证持有锁至语句结束。

在行复制格式下(BINLOG_FORMAT=ROW)，主库BINLOG中保存有记录的所有列信息包括自增列值，因此无需通过AUTO-INC锁来保证主从数据一致。在MySQL 8.0版本前，参数BINLOG_FORMAT的默认值为STATEMENT，参数innodb_autoinc_lock_mode的默认值为1。在MySQL 8.0版本后，参数BINLOG_FORMAT的默认值被调整为ROW格式，参数innodb_autoinc_lock_mode的默认值为2。

交错锁模式

所有的“INSERT-LIKE”语句都不使用表级锁，而是使用轻量级互斥锁。

交错锁模式速度快、可扩展性高，但是对于基于语句复制会有问题，只能使用基于ROW复制。

之所以称为交错模式是因为并发插入场景下自增值的分配大概率是交替这来的，时刻1事务1获得自增值，时刻2事务2获得自增值，以此类推。

自增值“丢失”与间隙

如果一个插入数据的事务回滚，那么为这个事务生成的自增值就会丢失，因为自增值不会被回滚。

回滚造成的自增值丢失InnoDB是不会被重用，所以不能依赖InnoDB自增列是连续值。

实验

这里实验几个简单场景。

表结构

建表语句：

CREATE TABLE `sys_user` ( `id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名', 
`name_pinyin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名拼音',
`id_card` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '身份证号',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手机号', 
`age` int(11) NOT NULL DEFAULT 1 COMMENT '年龄',
PRIMARY KEY (`id`), 
UNIQUE KEY `uni_idx_id_card` (`id_card`) USING BTREE COMMENT '唯一索引-身份证号', 
KEY `idx_phone_name` (`phone`,`name`) USING BTREE COMMENT '普通索引-手机号' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户'；

插入记录

insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (1, '小六', 'xiaoliu', 300000000, 13000008000, 10);

insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (2, '小六', 'xiaoliu', 300000001, 13000008000, 11);

insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (3, '小六', 'xiaoliu', 300000002, 13000008000, 13);

insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (4, '小六', 'xiaoliu', 300000003, 13000008000, 20);

自增锁模式查询

show variables like '%innodb_autoinc_lock_mode%';

MySQL 8.0.x版本输出：

MySQL [employees]> show variables like '%innodb_autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 2     |
+--------------------------+-------+
1 row in set (0.95 sec)

自增锁丢失与间隙

数据库有一张sys_user表，表中有四条记录，id为自增列，id最大值为4：

MySQL [employees]> select * from sys_user;
+----+--------+-------------+-----------+-------------+-----+
| id | name   | name_pinyin | id_card   | phone       | age |
+----+--------+-------------+-----------+-------------+-----+
|  1 | 小六   | xiaoliu     | 300000000 | 13000008000 |  10 |
|  2 | 小六   | xiaoliu     | 300000001 | 13000008000 |  11 |
|  3 | 小六   | xiaoliu     | 300000002 | 13000008000 |  13 |
|  4 | 小六   | xiaoliu     | 300000003 | 13000008000 |  20 |
+----+--------+-------------+-----------+-------------+-----+
4 rows in set (0.01 sec)

启动事务A，执行插入语句后回滚事务A：

begin;
insert into sys_user (name, name_pinyin, id_card, phone, age)values ('小六', 'xiaoliu', 3000000010, 13000008000, 10);
rollback;

事务A执行结果：

MySQL [employees]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [employees]> insert into sys_user (name, name_pinyin, id_card, phone, age)values ('小六', 'xiaoliu', 3000000010, 13000008000, 10);
Query OK, 1 row affected (0.00 sec)

MySQL [employees]> rollback;
Query OK, 0 rows affected (0.97 sec)

启动事务B,执行插入语句后提交事务B：

begin;
insert into sys_user (name, name_pinyin, id_card, phone, age)values ('小六', 'xiaoliu', 3000000010, 13000008000, 10);
commit;
select * from sys_user;

事务B执行完成后，查询输出如下（事务A使用的id=5值丢失出现自增间隙）：

MySQL [employees]> select * from sys_user;
+----+--------+-------------+------------+-------------+-----+
| id | name   | name_pinyin | id_card    | phone       | age |
+----+--------+-------------+------------+-------------+-----+
|  1 | 小六   | xiaoliu     | 300000000  | 13000008000 |  10 |
|  2 | 小六   | xiaoliu     | 300000001  | 13000008000 |  11 |
|  3 | 小六   | xiaoliu     | 300000002  | 13000008000 |  13 |
|  4 | 小六   | xiaoliu     | 300000003  | 13000008000 |  20 |
|  6 | 小六   | xiaoliu     | 3000000010 | 13000008000 |  10 |
+----+--------+-------------+------------+-------------+-----+
5 rows in set (0.00 sec)

参考

1. https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-locks
2. https://www.infoq.cn/article/zau0ewzsdtx9zofr6c8w
3. https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
4. https://www.cnblogs.com/gaogao67/p/11123772.html