当前位置 博文首页 > Ocean曈的博客：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），上锁情况是将查询范围内所有存在的行锁住，一般用记录锁即可。
可以做如下实验
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输出，因为个人知识的盲点，可能有些地方不是很准确，希望各位客官多多包涵，并且指出一起讨论。
原创不易，求点赞！！！
