Mysql的间隙锁，记录锁，临键锁

聊到mysql的锁，好像就有了一个聊不完的话题。网上一搜感觉各种答案都有，各种方式的描述也有，种类繁多，描述繁琐，反正看来看去，基本最后看多少忘记多少。

mysql 分储存引擎来聊锁。myisam 表锁，innodb行锁、表锁。

行锁：

共享锁（S锁：share）：当读取一行记录的时候，为了防止其他人修改，则需要加S锁。

排它锁（X锁：exclusive）：当修改一行的记录的时候，为了防止其他人同时修改，则需要添加X锁。

	X	S
X	不兼容	不兼容
S	不兼容	兼容

记录锁：添加在行索引上的锁

间隙锁：锁定范围是索引记录之间的间隙，针对可重复读以上隔离级别

临键锁：记录锁+间隙锁

表锁：

意向锁：在获取某行的锁之前，必须要获取表的锁，分为意向共享锁，意向排它锁

自增锁：对自增字段所采用的特殊表级锁

锁模式的含义：

IX：意向排它锁，表示是将要在表上加排他锁。

X：锁定记录本身和记录之前的间隙

S：锁定记录本身和记录之前的间隙

X,REC_NOT_GAP：只锁定记录本身

S，REC_NOT_GAP：只锁定记录本身

X，GAP：间隙锁，不锁定记录本身

S，GAP：间隙锁，不锁定记录本身

X，GAP,INSERT_INTENTION：插入意向锁

锁一般在什么情况下会出现？

不同的引擎出现不同的锁，MyIasm 只有表锁，Innodb 就表锁行锁都会有。一般锁的出现会在使用事务的时候为了保证当前事务的一致性，用到了 for update语句，或者update xxx where xxx 、等语句的时候出现。

我们如何查看到这些锁？

首先设置开启锁信息输出：

在这里插入图片描述

然后：

begin; 
select * from t for update; 
show engine innodb status\G;

执行结果如下：

Query OK, 0 rows affected (0.00 sec)

+------+------+
| id   | name |
+------+------+
|   10 | 10   |
|   20 | 20   |
|   30 | 30   |
+------+------+
3 rows in set (0.00 sec)

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2021-05-08 09:58:23 0x7fde205d2700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5 srv_active, 0 srv_shutdown, 2447 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 6
OS WAIT ARRAY INFO: signal count 5
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2076
Purge done for trx's n:o < 2074 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422067116498328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422067116499184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422067116497472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422067116496616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2075, ACTIVE 0 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 11, OS thread handle 140592002443008, query id 53 localhost root starting
show engine innodb status
TABLE LOCK table `lockdb`.`t` trx id 2075 lock mode IX
RECORD LOCKS space id 2 page no 4 n bits 72 index GEN_CLUST_INDEX of table `lockdb`.`t` trx id 2075 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000000815; asc       ;;
 2: len 7; hex 81000001090110; asc        ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 20; hex 3130202020202020202020202020202020202020; asc 10                  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000201; asc       ;;
 1: len 6; hex 000000000815; asc       ;;
 2: len 7; hex 8100000109011f; asc        ;;
 3: len 4; hex 80000014; asc     ;;
 4: len 20; hex 3230202020202020202020202020202020202020; asc 20                  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000202; asc       ;;
 1: len 6; hex 000000000815; asc       ;;
 2: len 7; hex 8100000109012e; asc       .;;
 3: len 4; hex 8000001e; asc     ;;
 4: len 20; hex 3330202020202020202020202020202020202020; asc 30                  ;;

--------
…………

1 row in set (0.00 sec)

主要内容在：

在这里插入图片描述

这里显示了锁的类型（lock mode）： IX（意向排它锁），还有四个record lock（记录锁），其中第一个记录锁表明 asc supremum;锁定表的上边界，其原因是：出现supremum的原因是：每次插入记录时所需要生成的聚簇索引Row_id还是自增的，每次都会在表的最后插入，所以就有可能插入id=10这条记录，因此要添加一个supremum防止数据插入。

我们可以新开一个窗口实验一下上述情况：

sql语句如下：

begin;
insert into t values(40,'40');

实验结果如下：

在这里插入图片描述

可以得出，supremun锁定表的上下边界，因为这个表是没有主键的，其实supremun 锁定的是隐藏主键row-id 的上边界，导致我们不论插入任何一条数据都不能正常插入。

实验：在事务中不同隔离级别下，对于索引的查询有不同的上锁情况：

在可重复读隔离级别（REPEATABLE-READ），上锁情况是将查询范围的全部锁住，包括范围内的所有间隙也上锁，一般是使用记录锁+间隙锁

实验一：REPEATABLE-READ 级别，有主键，使用了索引indexA。

sql代码如下：

---建表sql
 create table t1(id int not null,name char(20) default null,primary key(id),key idx_name(name));
 insert into t1 values(10,'10'),(20,'20'),(30,'30');
---查询上锁状态
begin; 
select * from t1 where name='10' for update;
show engine innodb status\G;

查询结果如下：

在这里插入图片描述

这里的三种锁可以对应前文的： X：锁定记录本身和记录之前的间隙 || X,REC_NOT_GAP：只锁定记录本身 || X，GAP：间隙锁，不锁定记录本身。可以看出通过主键索引锁住了记录本身，然后通过了索引inx_name 中的临建锁（X）以及间隙锁（X,GAP）锁定了name 范围[负无穷，20）之间的区域。

有兴趣的同学可以试试以下sql：

insert into  t1 values(1,'1');
update t1 set name = '222' where name = '10';

这类两行语句，都是不能执行，会被堵塞住等，上述的事务完成之后才能运行，或者直接超时。

如果说我们执行语句：

begin;
select * from t1 where name = '10' and id = 10 for update;

这种情况会使用什么上锁范围呢？

执行show engine innodb status \G;

在这里插入图片描述

我们可以看到只用了一个记录锁，并且是锁住了主键的id=10 的这行记录，为什么呢？

我们可以执行：

可以看到，执行计划里面，最终使用到的索引是主键索引，没有使用我们建的索引inx_name。

得出结论，当上锁的where语句中涉及到多了索引列的时候，最终的上锁条件，按照执行计划里面最终使用索引主。

当然不同的索引情况，不同的建表情况会有不同的加锁情况，不过基本上的规则可言归纳为：

1.使用主键索引的情况，只锁当前查询命中存在行记录的，X,REC_NOT_GAP，只锁当前记录

2.使用普通索引，只锁当前查询命中存在行记录的，X,REC_NOT_GAP，并且会锁住普通索引上下两个范围内的间隙即使用临建锁（X）以及间隙锁（X,GAP）；

READ-COMMIT 级别下的上锁情况

在读已提交隔离级别（READ-COMMIT），上锁情况是将查询范围内所有存在的行锁住，一般用记录锁即可。

可以做如下实验

1.先将当前事务级别修改为 read commit，执行以下语句：

set session transaction isolation level read committed;

2.建表语句如下：

 create table t2(id int not null,name char(20) default null,primary key(id),key idx_name(name));
 insert into t2 values(10,'10'),(20,'20'),(30,'30');

3.事务语句如下：

begin;
select * from t2 where name = '10' for update;
show engine innodb status \G;

加锁结果如下：

在这里插入图片描述

结论：

可以看到两行记录都是 X,REC_NOT_GAP 的这样的记录锁，只锁住一行记录。

同样的如果执行事务语句为查询所有行呢？

执行语句如下：

begin;
select * from t2 for update;
show engine innodb status \G;

执行结果

3 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 14, OS thread handle 140592000804608, query id 213 localhost root starting
show engine innodb status
TABLE LOCK table `lockdb`.`t2` trx id 2185 lock mode IX
RECORD LOCKS space id 6 page no 5 n bits 72 index idx_name of table `lockdb`.`t2` trx id 2185 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3130202020202020202020202020202020202020; asc 10                  ;;
1: len 4; hex 8000000a; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3230202020202020202020202020202020202020; asc 20                  ;;
1: len 4; hex 80000014; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3330202020202020202020202020202020202020; asc 30                  ;;
1: len 4; hex 8000001e; asc     ;;

RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `lockdb`.`t2` trx id 2185 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 000000000883; asc       ;;
2: len 7; hex 82000000a50110; asc        ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10                  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000014; asc     ;;
1: len 6; hex 000000000883; asc       ;;
2: len 7; hex 82000000a5011d; asc        ;;
3: len 20; hex 3230202020202020202020202020202020202020; asc 20                  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001e; asc     ;;
1: len 6; hex 000000000883; asc       ;;
2: len 7; hex 82000000a5012a; asc       *;;
3: len 20; hex 3330202020202020202020202020202020202020; asc 30                  ;;

同样的如我们之前的结论那样，只是用了记录锁，分别锁住了 inx_name 这个索引，以及主键索引的三列。

所以结论就是：

1.在read commit 隔离级别下，使用了普通索引查询，上锁范围为，使用记录锁为表中存在行上锁，并不会使用间隙锁或者临建锁。

最后一个实验

不使用for update 语句直接使用update 语句，会有上锁情况出现么？

1.执行建表语句：

 create table t6(id int not null,name char(20) default null,primary key(id),key idx_name(name));
 insert into 6 values(10,'10'),(20,'20'),(30,'30');

2.事务语句如下：

begin;
update t6 set name = '11' where name = '10';

3.查询结果语句

show engine innodb status\G;

结果如下：

在这里插入图片描述

这个实验是基于事务级别为read-commit 之下做的，可以看到，已经上锁了；但是上锁的结果与我们for update上锁情况在主键上的上锁情况不一样，用了一个记录锁（x lock rec but not gap）锁住了id==11的记录，但是这行记录不存在啊。这样能锁住 id ==11 这行么？

继续试验：

新开一个窗口并新起一个事务，执行以下语句：

begin;
insert into t6 values(11,'11');

结果如下：

在这里插入图片描述

数据直接插入成功了，证明id==11 这行主键索引没有被锁住。

哪我们修改where id = 10；通过主键索引修改这一行，查看 id=10的索引有没有上锁成功。

执行语句如下：

begin;
update t6 set name='10000' where id=10;

其结果如下：

在这里插入图片描述

数据不能用过id=10 的这个主键去修改了，id=10 的行已经加锁。

所以这里就有个很奇怪的点主键的记录锁显示锁住的是 id=11这一行，但是实际上还是锁的id=10这一行。这里的上锁情况与我们for update 语句的上锁情况一致，即锁住where 条件命中存在的的行数。

唯一有个坑的地方就是，显示主键行锁住的是id=11，而实际锁住的是id = 10。

最后的最后

之后我恢复表t6的数据，我又用了事务级别为 reapable-read 去重复上述实验。

1.执行sql如下：

begin；
update t6 set name = '11' where name = '10';

2.执行查询上锁情况语句

show engine innodb status\G;

其结果如下：

在这里插入图片描述

分析结果如下：

第一条锁记录：x锁，锁定索引inx_name (负无穷，‘10’]

第二条锁记录：X,REC_NOT_GAP：只锁定记录本身，将主键 id=10 的记录锁住（ps，虽然显示的锁住id=11的数据但是实际是锁住10）

第三条锁记录：x，gap，间隙锁，不锁定本身，锁定索引idx_name[11,20)

第四条锁记录：x，gap，间隙锁，不锁定本身，锁定索引idx_name[10,11)

分析完毕，最终结论，基本上得出的结论就是repeatabel-read 级别下，使用事务更新update 语句，上锁情况为：

1.主键索引，记录锁锁定where语句命中的所有存在的语句

2.普通索引，间隙锁+临建锁，首先锁定变化前后的所有记录，以及变化前，所有排序附近的记录。（ps：这里其实有点不确定，到底是怎么样一个锁定逻辑，还需要查阅资料，以及验证才行）

写了很久，总算把这篇blog输出，因为个人知识的盲点，可能有些地方不是很准确，希望各位客官多多包涵，并且指出一起讨论。

原创不易，求点赞！！！

原文链接：https://blog.csdn.net/qq_15965621/article/details/115461894

实验：在事务中不同隔离级别下，对于索引的查询有不同的上锁情况：

READ-COMMIT 级别下的上锁情况

所以结论就是：

最后一个实验

最后的最后

你可能也喜欢