【MySQL】MySQL的锁机制 - 记录锁、间隙锁、临键锁

猩火燎原

已于 2023-06-01 17:09:36 修改

阅读量5.2k

点赞数 7

分类专栏： MySql 面试相关文章标签： mysql 数据库 database

于 2021-10-28 16:01:59 首次发布

本文链接：https://blog.csdn.net/sfh2018/article/details/121016466

版权

面试相关同时被 2 个专栏收录

12 篇文章 1 订阅

订阅专栏

MySql

3 篇文章 0 订阅

订阅专栏

一、参考链接

二、总结

1、间隙锁的目的

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据

2、什么时候产生间隙锁？

在 RR 级别下，Gap 锁是默认开启的，而在 RC 级别下，Gap 锁是关闭的。

（1）字段是唯一索引时，只有查询某个范围记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；

（2）字段是普通索引时，不管是锁住单条，还是多条记录，都会产生间隙锁；

3、间隙锁锁定的范围

根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。

4、需要注意的点（重要）

普通索引的间隙，优先以普通索引排序，然后再根据主键索引排序（多普通索引情况还未研究）；

三、案例

通过几个案例来验证总结里的结论，加深印象。

建表

CREATE TABLE `test_gap_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `number` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_number` (`number`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


INSERT INTO test_gap_table ( id, number ) VALUE ( 1, 2 );
INSERT INTO test_gap_table ( id, number ) VALUE ( 3, 4 );
INSERT INTO test_gap_table ( id, number ) VALUE ( 6, 5 );
INSERT INTO test_gap_table ( id, number ) VALUE ( 8, 5 );
INSERT INTO test_gap_table ( id, number ) VALUE ( 10, 5 );
INSERT INTO test_gap_table ( id, number ) VALUE ( 13, 11 );

number为普通索引。

number索引的结构基本与下图类似。
在这里插入图片描述

接下来开始验证，注意，验证前，需要先关闭mysql的自动提交

show variables like '%autocommit%';

验证过程中可以通过show processlist命令查看数据库连接信息。

`SHOW PROCESSLIST` 是一个MySQL命令，用于显示当前数据库连接的进程列表。
它可以显示当前正在执行的所有查询和连接到数据库服务器的客户端信息。

该命令返回一个结果集，其中包含每个连接的详细信息，如连接ID、用户、主机、数据库、当前状态、执行的语句等。
通过执行 `SHOW PROCESSLIST`，你可以查看当前数据库服务器上的活动连接和执行的查询，以便进行性能监控、诊断和调优。

这个命令对于管理员和开发人员来说非常有用，可以帮助他们了解数据库服务器的负载情况，发现潜在的问题和优化机会，以及终止或杀死
执行时间过长或占用过多资源的查询。

需要注意的是，`SHOW PROCESSLIST` 命令的执行本身也会占用一定的系统资源，因此在生产环境中使用时要谨慎，避免过度频繁地执行该命令。

在这里插入图片描述

案例1:查询已存在的单条记录

session 1:
start transaction;//也可以是begin
select * from test_gap_table where number=4 for update;

session 2:
start transaction;
insert into test_gap_table value(2,4);#（阻塞）
insert into test_gap_table value(2,2);#（阻塞）
insert into test_gap_table value(4,4);#（阻塞）
insert into test_gap_table value(7,4);#（阻塞）
insert into test_gap_table value(4,5);#（阻塞）
insert into test_gap_table value(7,5);#（执行成功）
insert into test_gap_table value(9,5);#（执行成功）
insert into test_gap_table value(11,5);#（执行成功）

检索条件number=4，向左取得最靠近的值2作为左区间，向右取得最靠近的5作为右区间，因此，session 1的间隙锁的范围（2，4），（4，5），如下图所示：
在这里插入图片描述

这里（2，4）插入的时候阻塞，原因可以看上边的总结-4，当number相同时，会根据id来排序，所以根据索引的数据结构特征，如果（2，4）要插入的话将被放在间隙1中，但是间隙1被锁定，所以发生阻塞。（2，2）、（4，4）、（4，5）都是同理。（7，5）能执行成功是因为它会被放在（id=6，number=5）和（id=8，number=5）中间，不属于被锁定的间隙所以能成功。

在这里插入图片描述
从图中可以看出，当 number 相同时，会根据主键 id 来排序，所以（2，4）、（4，4）等情况才会阻塞。

如果能理解上边锁定和阻塞的情况，其余情况也就都能掌握了。

案例2:查询不存在的记录

session 1:
start transaction;
select * from test_gap_table where number=13 for update ;

session 2:
start transaction;
insert into test_gap_table value(11,5);#(执行成功)
insert into test_gap_table value(12,11);#(执行成功)
insert into test_gap_table value(14,11);#(阻塞)
insert into test_gap_table value(15,12);#(阻塞)
update test_gap_table set id=14 where number=11;#(阻塞)
update test_gap_table set id=11 where number=11;#(执行成功)

检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因此，session 1的间隙锁的范围（11，无穷大）。阻塞情况与案例1 原理相同，不再赘述。

案例3:查询多条记录（范围查询）

session 1:
start transaction;
select * from test_gap_table where number>4 for update;

session 2:
start transaction;
update test_gap_table set id=2 where number=4 ;#(执行成功)
update test_gap_table set id=4 where number=4 ;#(阻塞)
update test_gap_table set id=5 where number=5 ;#(阻塞)
insert into test_gap_table value(2,3);#(执行成功)
insert into test_gap_table value(null,13);#(阻塞)