Mysql InnoDB之各类锁
站长
· 阅读数 37
前言
下面我们来了解下各种锁，已经它的使用场景。
mysql版本为：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)
测试用的表结构
mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` char(32) NOT NULL COMMENT '用户姓名',
`num` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `test_name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='test表' |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
乐观锁和悲观锁
锁主要分为两个大类，一类是乐观锁，一类是悲观，但是本质都是通过标记进行锁定，区别是乐观锁不会导致其他尝试锁定的会话阻塞进行等待如果想继续尝试就是重试或者一直重试-自旋。
使用场景
乐观锁可以使用在写少的场景中，原因在于很短时间内的重试很大几率会获得锁，就算多个线程同时进行重试因为写很少所以开销也不是很大。悲观锁在写多的场景中比较合适，原因在于短时间内重试获得锁的概率不大，不如让出cpu进行阻塞等待，超过规定时间内超时失败，因为写多大量线程同时获得锁因为让出cpu进行阻塞等待所以开销也不是很大。
那么悲观锁可以适用于写少的场景吗？答案是可以的，就是因为让出了cpu响应速度可能没有乐观锁快，但是在生产中，很少一业务会直写少，对于长期来看直接使用悲观锁是没问题的。
另外Mysql官网对InnoDB锁的描述也是悲观锁。
锁等待超时配置
因为本文主要对锁进行描述，所以我们来看看有关lock_wait_timeout的配置。
mysql> show variables like '%lock_wait_timeout%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 31536000 |
+--------------------------+----------+
2 rows in set (0.01 sec)
可以看到有两个，我们来分别看看。
innodb_lock_wait_timeout
| 命令行格式 | --innodb-lock-wait-timeout=# |
| 系统变量 | innodb_lock_wait_timeout |
| 影响范围 | Global，Session |
| 动态的 | Yes |
SET_VAR提示适用 | No |
| 类型 | Integer |
| 默认值 | 50 |
| 最小值 | 1 |
| 最大值 | 1073741824 |
InnoDB 事务在放弃之前等待行锁 的时间长度（以秒为单位）。一个事务为了对被另一个 InnoDB事务锁定的行进行写访问最多等待这么多秒就会发生如下错误：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
当发生锁等待超时时，当前语句被 回滚（而不是整个事务）。要使整个事务回滚，请使用该 --innodb-rollback-on-timeout 选项启动服务器。另请参阅第 15.21.5 节，“InnoDB 错误处理”。
对于高度交互的应用程序或OLTP系统，您可以降低该值，以便快速显示用户反馈，或将更新放入队列以供以后处理。对于长时间运行的后端操作，例如数据仓库中等待其他大型插入或更新操作完成的转换步骤，可以增加该值。
innodb_lock_wait_timeout 适用于InnoDB行锁。MySQL 表锁不会在InnoDB内部发生，并且此超时不适用于等待表锁。
锁定等待超时值在启用（默认值）时 不适用于 死锁，
因为InnoDB会立即检测死锁并回滚其中一个死锁事务。innodb_deadlock_detect禁用时，当发生死锁时InnoDB依赖innodb_lock_wait_timeout 进行事务回滚。请参见 第 15.7.5.2 节，“死锁检测”。
使用SET GLOBAL或者SET SESSION语句（不写默认session）在运行时设置innodb_lock_wait_timeout。更改全局设置需要足够的权限来设置全局系统变量（请参阅第5.1.9.1节“系统变量权限”），并影响随后连接的所有客户端的操作。任何客户端都可以更改innodb_lock_wait_timeout的会话设置，这只会影响该客户端。
笔者小结
可以注意到，innodb_lock_wait_timeout是InnoDB等待行锁超时的配置,超时候当前事务还会存在，因为你可以继续重试，但是一般业务不会进行重试，超时抛出异常后直接对该连接进行回收。
死锁检测innodb_lock_wait_timeout。在高并发系统上，当多个线程等待同一个锁时，死锁监测会导致速度变慢。有时，它可能是更高效的去禁止死锁监测而依赖innodb_lock_wait_timeout设置当死锁的时候进行事务的回滚。
lock_wait_timeout
| 命令行格式 | --lock-wait-timeout=# |
| 系统变量 | lock_wait_timeout |
| 影响范围 | Global，Session |
| 动态的 | Yes |
SET_VAR提示适用 | No |
| 类型 | Integer |
| 默认值 | 31536000 |
| 最小值 | 1 |
| 最大值 | 31536000 |
此变量指定尝试获取元数据锁的超时时间（以秒为单位）。允许值范围为 1 到 31536000（1 年）。默认值为 31536000。
此超时适用于所有使用元数据锁的语句。其中包括对表、视图、存储过程和存储函数的DML(data manipulation language)和DDL(data defintion language)操作，例如 LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER 语句。
此超时不适用于对mysql数据库中系统表的隐式访问，例如使用GRANT or REVOKE语句(属于DCL（data control language 数据库控制语言 授权，角色控制等）)对grant表进行修改会或者表日志语句。超时适用于表的直接访问，例如SELECT 或者 UPDATE。
超时值分别适用于每个元数据锁定尝试。一个给定的语句可能需要多个锁，因此 lock_wait_timeout在报告超时错误之前，该语句可能会阻塞比该值更长的时间。当发生锁定超时时， ER_LOCK_WAIT_TIMEOUT报告。
lock_wait_timeout还定义了LOCK INSTANCE FOR BACKUP语句在放弃之前等待锁定的时间量。
笔者小结
lock_wait_timeout是元数据锁，在开发的时候经常碰到的是修改/增加表字段、增加/修改表的索引、修改/增加索引等。
performance_schema.data_locks
data_locks表示持有和请求的数据锁。
数据锁定信息示例：
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139664434886512:1059:139664350547912
ENGINE_TRANSACTION_ID: 2569
THREAD_ID: 46
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139664350547912
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139664434886512:2:4:1:139664350544872
ENGINE_TRANSACTION_ID: 2569
THREAD_ID: 46
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139664350544872
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
使用该data_locks表可帮助诊断在高并发负载期间发生的性能问题。
data_lock_waits
有关那些锁请求被那些锁持有的锁阻止的信息。
mysql> SELECT * FROM performance_schema.data_lock_waits\G
*************************** 1. row ***************************
ENGINE: INNODB
REQUESTING_ENGINE_LOCK_ID: 140211201964816:2:4:2:140211086465800
REQUESTING_ENGINE_TRANSACTION_ID: 1555
REQUESTING_THREAD_ID: 47
REQUESTING_EVENT_ID: 5
REQUESTING_OBJECT_INSTANCE_BEGIN: 140211086465800
BLOCKING_ENGINE_LOCK_ID: 140211201963888:2:4:2:140211086459880
BLOCKING_ENGINE_TRANSACTION_ID: 1554
BLOCKING_THREAD_ID: 46
BLOCKING_EVENT_ID: 12
BLOCKING_OBJECT_INSTANCE_BEGIN: 140211086459880
共享锁和排他锁
InnoDB实现标准的行级锁，其中有两种类型，共享锁和排他锁。
共享锁只允许读共享，不允许进行更新或者删除。排他锁只允许持有锁的事务进行更新或者删除。
共享锁演示
如下，开启了一个事务对Id=1的行进行了共享锁定：
mysql> select * from my_test.test;
+----+--------+---------+
| id | name | num |
+----+--------+---------+
| 1 | 是的 | 1234567 |
| 2 | 是哈 | NULL |
| 3 | 桑 | NULL |
| 4 | 桑 | NULL |
| 5 | 桑 | NULL |
| 6 | 桑 | NULL |
| 10 | 使得 | NULL |
| 15 | 使得 | NULL |
| 16 | 桑 | NULL |
| 20 | 桑 | NULL |
| 25 | rt | NULL |
+----+--------+---------+
11 rows in set (0.00 sec)
mysql> begin;select * from my_test.test where id=1 lock in share mode;
Query OK, 0 rows affected (0.00 sec)
+----+--------+---------+
| id | name | num |
+----+--------+---------+
| 1 | 是的 | 1234567 |
+----+--------+---------+
1 row in set (0.00 sec)
看一下performance_schema.data_locks：
mysql> select * from performance_schema.data_locks;
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4831874176:1063:5266656984 | 281479808584832 | 49 | 39 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IS | GRANTED | NULL |
| INNODB | 4831874176:2:4:4:5268875800 | 281479808584832 | 49 | 39 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
可以发现目前事务持有一个表级的IS和行级的S，说明先进行意向共享锁之后确定了行之后对给定行进行S锁锁定。
接下来我们看看能都继续读（注意新开启一个终端/会话）：
mysql> select * from my_test.test where id=1;
+----+--------+---------+
| id | name | num |
+----+--------+---------+
| 1 | 是的 | 1234567 |
+----+--------+---------+
1 row in set (0.00 sec)
发现是可以读的。我们看看能都进行update：
mysql> update my_test.test set name='是哈' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from performance_schema.data_locks;
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4831874968:1063:5266658936 | 14094 | 50 | 33 | my_test | test | NULL | NULL | NULL | 5266658936 | TABLE | IX | GRANTED | NULL |
| INNODB | 4831874968:2:4:4:5268880408 | 14094 | 50 | 33 | my_test | test | NULL | NULL | PRIMARY | 5268880408 | RECORD | X,REC_NOT_GAP | WAITING | 1 |
| INNODB | 4831874176:1063:5266656984 | 281479808584832 | 49 | 39 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IS | GRANTED | NULL |
| INNODB | 4831874176:2:4:4:5268875800 | 281479808584832 | 49 | 39 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
可以发现另一个事务也想持有id=1的行锁。但是最终超时失败了。
在其他行中是可以进行更新的：
mysql> update my_test.test set name='哈哈哈' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
排他锁演示
这里笔者不进行演示了，读者可以自己试试。
使用场景
就如同它们的意思一样。但是笔者也没在生产中使用共享锁，在读的时候进行锁定，不让其他事务对锁定的行进行修改，一般没啥必要这样并发效率比较低，想要可重复读mysql8.0默认的隔离级别就是了。排他锁不用说用的比较多，只允许持有锁的事务进行更新或删除天经地义，不然在修改的时候别的也来修改提交的时候本事务的修改数据被覆盖事务的一致性无法保证。
意向锁
意向锁顾名思义就是意图上锁，意向锁是表锁，就是在确定具体锁定范围之前先把表锁住，也分意向共享锁和意向排他锁。
下面的表总结了表级别锁类型的兼容性：
| 排他锁（X） | 意向排他锁（IX） | 共享锁（S） | 意向共享锁（IS） | |
| 排他锁（X） | 冲突 | 冲突 | 冲突 | 冲突 |
| 意向排他锁（IX） | 冲突 | 兼容 | 冲突 | 兼容 |
| 共享锁（S） | 冲突 | 冲突 | 兼容 | 兼容 |
| 意向共享锁（IS） | 冲突 | 兼容 | 兼容 | 兼容 |
上面共享锁的演示中我们已经看到了，先是IS再是S，最终确定了行之后就不会锁表了，我们看看确定不了锁定范围的演示：
mysql> select * from my_test.test;
+----+-----------+---------+
| id | name | num |
+----+-----------+---------+
| 1 | 是的 | 1234567 |
| 2 | 哈哈哈 | NULL |
| 3 | 桑 | NULL |
| 4 | 桑 | NULL |
| 5 | 桑 | NULL |
| 6 | 桑 | NULL |
| 10 | 使得 | NULL |
| 15 | 使得 | NULL |
| 16 | 桑 | NULL |
| 20 | 桑 | NULL |
| 25 | rt | NULL |
+----+-----------+---------+
11 rows in set (0.00 sec)
mysql> begin;select * from my_test.test where num=1234567 lock in share mode;
Query OK, 0 rows affected (0.00 sec)
+----+--------+---------+
| id | name | num |
+----+--------+---------+
| 1 | 是的 | 1234567 |
+----+--------+---------+
1 row in set (0.00 sec)
下面可以看到因为确定不了行，把所有行都锁住了包括supremum pseudo-record，也就是锁了整个表。
mysql> select * from performance_schema.data_locks;
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 4831874176:1063:5266656984 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IS | GRANTED | NULL |
| INNODB | 4831874176:2:4:1:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | supremum pseudo-record |
| INNODB | 4831874176:2:4:3:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 2 |
| INNODB | 4831874176:2:4:4:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 1 |
| INNODB | 4831874176:2:4:5:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 5 |
| INNODB | 4831874176:2:4:6:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 10 |
| INNODB | 4831874176:2:4:7:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 15 |
| INNODB | 4831874176:2:4:8:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 20 |
| INNODB | 4831874176:2:4:9:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 25 |
| INNODB | 4831874176:2:4:10:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 16 |
| INNODB | 4831874176:2:4:11:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 6 |
| INNODB | 4831874176:2:4:12:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 3 |
| INNODB | 4831874176:2:4:13:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 4 |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
13 rows in set (0.00 sec)
下面证实下是锁了整个表：
mysql> insert into my_test.test(id,name) values(24,'好的');
^CEnter password:
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into my_test.test(id,name) values(26,'好的');
^CEnter password:
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into my_test.test(id,name) values(0,'好的');
^CEnter password:
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
使用场景
记录锁
记录锁是对索引记录的锁。
前面共享锁演示的id=xx就使用到了，innodb_lock_wait_timeout的lock_type为record。
间隙锁
间隙锁是索引记录之间的间隙的锁，或在第一条索引之前或在最后一条索引记录之后的间隙上的锁。
超过目前索引的范围会被锁住吗
mysql> select * from my_test.test;
+----+-----------+---------+
| id | name | num |
+----+-----------+---------+
| 1 | 是的 | 1234567 |
| 2 | 哈哈哈 | NULL |
| 3 | 桑 | NULL |
| 4 | 桑 | NULL |
| 5 | 桑 | NULL |
| 6 | 桑 | NULL |
| 10 | 使得 | NULL |
| 15 | 使得 | NULL |
| 16 | 桑 | NULL |
| 20 | 桑 | NULL |
| 25 | rt | NULL |
+----+-----------+---------+
11 rows in set (0.00 sec)
mysql> begin;select * from my_test.test where id>100 and id<200 lock in share mode;
Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)
来看看使用锁的情况：
mysql> select * from performance_schema.data_locks;
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 4831874176:1063:5266656984 | 281479808584832 | 49 | 71 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IS | GRANTED | NULL |
| INNODB | 4831874176:2:4:1:5268875800 | 281479808584832 | 49 | 71 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | supremum pseudo-record |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
可以发现LOCK_DATA为supremum pseudo-record说明不在范围内照样锁，锁的范围为(25,positive infinity)，可以自己试试。
如果不是唯一索引呢
mysql> select * from test;
+----+------+---------+
| id | name | num |
+----+------+---------+
| 1 | a | 1234567 |
| 2 | a | NULL |
| 3 | a | NULL |
| 4 | a | NULL |
| 5 | a | NULL |
| 6 | b | NULL |
| 10 | b | NULL |
| 15 | e | NULL |
| 16 | e | NULL |
| 20 | e | NULL |
| 24 | g | NULL |
| 25 | g | NULL |
+----+------+---------+
12 rows in set (0.00 sec)
mysql> begin;select * from test where name='e' lock in share mode;
Query OK, 0 rows affected (0.00 sec)
+----+------+------+
| id | name | num |
+----+------+------+
| 15 | e | NULL |
| 16 | e | NULL |
| 20 | e | NULL |
+----+------+------+
3 rows in set (0.00 sec)
来看看使用锁的情况：
mysql> select * from performance_schema.data_locks;
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+----------------------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+----------------------------------------+
| INNODB | 4831874176:1063:5266656984 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IS | GRANTED | NULL |
| INNODB | 4831874176:2:5:23:5268875800 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | test_name_index | 5268875800 | RECORD | S | GRANTED | 'e ', 20 |
| INNODB | 4831874176:2:5:24:5268875800 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | test_name_index | 5268875800 | RECORD | S | GRANTED | 'e ', 16 |
| INNODB | 4831874176:2:5:25:5268875800 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | test_name_index | 5268875800 | RECORD | S | GRANTED | 'e ', 15 |
| INNODB | 4831874176:2:4:7:5268876144 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | S,REC_NOT_GAP | GRANTED | 15 |
| INNODB | 4831874176:2:4:8:5268876144 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | S,REC_NOT_GAP | GRANTED | 20 |
| INNODB | 4831874176:2:4:10:5268876144 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | S,REC_NOT_GAP | GRANTED | 16 |
| INNODB | 4831874176:2:5:10:5268876488 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | test_name_index | 5268876488 | RECORD | S,GAP | GRANTED | 'g ', 24 |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+----------------------------------------+
8 rows in set (0.00 sec)
我们可以列下间隙锁的区间：
(negative infinity, a]
(a, b]
(b, e]
(e, g]
(g, positive infinity)
所以锁定的范围为(b, e]和(e, g)，我们可以验证下
看b是不是开区间：
mysql> begin;select * from test where name='e' lock in share mode;
Query OK, 0 rows affected (0.00 sec)
+----+------+------+
| id | name | num |
+----+------+------+
| 15 | e | NULL |
| 16 | e | NULL |
| 20 | e | NULL |
+----+------+------+
3 rows in set (0.00 sec)
是的。
看c能不能插入：
mysql> insert into my_test.test(name) values('c');
^CEnter password:
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
不能
看f能不能插入：
mysql> insert into my_test.test(name) values('f');
^CEnter password:
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
看g是不是闭区间
mysql> update my_test.test set num='1' where name='g';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
是的
结论
不是唯一索引也会使用间隙锁。
间隙锁可以共存吗
mysql> begin;select * from test where id<5 lock in share mode;
Query OK, 0 rows affected (0.00 sec)
+----+------+---------+
| id | name | num |
+----+------+---------+
| 1 | a | 1234567 |
| 2 | a | NULL |
| 3 | a | NULL |
| 4 | a | NULL |
+----+------+---------+
4 rows in set (0.00 sec)
另一个事务尝试在区间内进行
mysql> begin;select * from my_test.test where id<3 for update;
Query OK, 0 rows affected (0.00 sec)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
很明显不能共存，mysql官方文档说可以，笔者这里也觉得不太可能，读者如果证明了可以，欢迎进行评论。
InnoDB中的间隙锁是“存粹抑制性的”，这意味着它们的唯一目的是防止其他事务插入到间隙中。间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。共享和独占间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。
使用场景
这个其实是mysql的优化，不能使用行锁，锁间隙比直接锁表来的好，是性能和并发性之间权衡的一部分。同时需要注意的是如果使用READ COMMITTED，间隙锁会被禁用。在这种情况下，间隙锁对搜索和索引扫描禁用，仅用于外键约束检查和重复键检查。
为啥使用读已经提交隔离级别会禁用呢，因为间隙锁本质上是一种使用索引进行范围锁定的锁，所以可以防止幻读，当隔离级别为读已提交的时，不需要对范围进行锁定，所以相当于隐式禁用。
Next-Key锁
Next-Key锁是索引记录上的行锁和索引记录之前的间隙锁上的间隙的组合。
所以Next-Key锁是行锁+间隙锁。这里不做过多赘述。
插入意向锁
插入意向锁，官方文档描述的是，插入之间会先在尝试设置一种间隙锁。此锁表示插入的意项，即如果插入到同一索引间隙中的多个事务没有插入间隙中的同一位置，则它们不需要等待彼此。
下面我们来演示一下：
mysql> begin;insert into my_test.test(id,name) values(21,'好的');
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> begin;insert into my_test.test(id,name) values(22,'好的');
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> begin;insert into my_test.test(id,name) values(21,'好的');
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.data_locks;
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4831874968:1063:5266658936 | 14155 | 50 | 106 | my_test | test | NULL | NULL | NULL | 5266658936 | TABLE | IX | GRANTED | NULL |
| INNODB | 4831874968:2:4:18:5268880408 | 14155 | 50 | 106 | my_test | test | NULL | NULL | PRIMARY | 5268880408 | RECORD | S,REC_NOT_GAP | WAITING | 21 |
| INNODB | 4831874176:1063:5266656984 | 14153 | 49 | 107 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IX | GRANTED | NULL |
| INNODB | 4831874176:2:4:18:5268875800 | 14153 | 50 | 106 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | X,REC_NOT_GAP | GRANTED | 21 |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
当在另一个事务中插入id=22的记录时发现没啥特殊的地方，在重复插入id=21的时候出现了一个行锁，仔细点可以发现，后面的线程帮前面的事务加上的，这还是比较讨巧，等存放竞争时再加锁。但是也没看到插入吧间隙锁。感觉纯插入能确定id还用不上插入意向锁，我们尝试下先用查询锁定，再插入看看。
mysql> select * from test;
+----+--------+---------+
| id | name | num |
+----+--------+---------+
| 1 | a | 1234567 |
| 2 | a | NULL |
| 3 | a | NULL |
| 4 | a | NULL |
| 5 | a | NULL |
| 6 | b | 1 |
| 10 | b | 1 |
| 15 | e | NULL |
| 16 | e | NULL |
| 20 | e | NULL |
| 21 | 好的 | NULL |
| 25 | g | 1 |
+----+--------+---------+
12 rows in set (0.00 sec)
mysql> begin;select * from test where id>20 and id<25 for update;
Query OK, 0 rows affected (0.00 sec)
+----+--------+------+
| id | name | num |
+----+--------+------+
| 21 | 好的 | NULL |
+----+--------+------+
1 row in set (0.01 sec)
mysql> select * from performance_schema.data_locks;
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 4831874176:1063:5266656984 | 14169 | 49 | 144 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IX | GRANTED | NULL |
| INNODB | 4831874176:2:4:18:5268875800 | 14169 | 49 | 144 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | X | GRANTED | 21 |
| INNODB | 4831874176:2:4:19:5268876144 | 14169 | 49 | 144 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | X,GAP | GRANTED | 25 |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
3 rows in set (0.00 sec)
可以发现已经使用上了间隙锁。下面我们插入id=22看看
mysql> insert into my_test.test(id,name) values(22,'c');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from performance_schema.data_locks;
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+
| INNODB | 4831874968:1063:5266658936 | 14170 | 50 | 130 | my_test | test | NULL | NULL | NULL | 5266658936 | TABLE | IX | GRANTED | NULL |
| INNODB | 4831874968:2:4:19:5268880408 | 14170 | 50 | 130 | my_test | test | NULL | NULL | PRIMARY | 5268880408 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 25 |
| INNODB | 4831874176:1063:5266656984 | 14169 | 49 | 144 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IX | GRANTED | NULL |
| INNODB | 4831874176:2:4:18:5268875800 | 14169 | 49 | 144 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | X | GRANTED | 21 |
| INNODB | 4831874176:2:4:19:5268876144 | 14169 | 49 | 144 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | X,GAP | GRANTED | 25 |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+
5 rows in set (0.00 sec)
出现了插入意向锁：X,GAP,INSERT_INTENTION。
AUTO-INC锁
是一种特殊的表级锁，由插入到具有 AUTO_INCREMENT列的表中的事务使用。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务都必须等待自己向该表中插入，以便第一个事务插入的行接收连续的主键值。
innodb_autoinc_lock_mode （2表示交错、1表示连续，从8.0开始默认值为2，并发性高）变量控制用于自动增量锁定的算法。它允许您选择如何在可预测的自动递增值序列和插入操作的最大并发性之间进行权衡。
有关更多信息，请参阅 第 15.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理”。
转载自:https://juejin.cn/post/7070815911405420574