MySQL行锁、表锁、间隙锁详解

article/2025/9/15 23:37:05

前言

我们前几篇讲了索引是什么，如何使用explain分析索引使用情况，如何去优化索引，以及show profiles分析SQL语句执行资源消耗的学习。今天我们来讲讲MySQL的各种锁，这里存储引擎我们使用InnoDB；

准备工作

创建表 tb_innodb_lock

drop table if exists test_innodb_lock;
CREATE TABLE test_innodb_lock (a INT (11),b VARCHAR (20)
) ENGINE INNODB DEFAULT charset = utf8;
insert into test_innodb_lock values (1,'a');
insert into test_innodb_lock values (2,'b');
insert into test_innodb_lock values (3,'c');
insert into test_innodb_lock values (4,'d');
insert into test_innodb_lock values (5,'e');

创建索引

create index idx_lock_a on test_innodb_lock(a);
create index idx_lock_b on test_innodb_lock(b);

MySQL 各种锁演示

  • 先将自动提交事务改成手动提交：set autocommit=0;
  • 我们启动两个会话窗口 A 和 B，模拟一个抢到锁，一个没抢到被阻塞住了。

行锁：

在MySQL的InnoDB引擎支持行锁，与Oracle不同，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。

for update

如果在一条select语句后加上for update，则查询到的数据会被加上一条排它锁，其它事务可以读取，但不能进行更新和插入操作。

-- A用户对id=1的记录进行加锁
select * from user where id=1 for update;-- B用户无法对该记录进行操作
update user set count=10 where id=1;-- A用户commit以后则B用户可以对该记录进行操作

行锁的实现需要注意：

  1. 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
  2. 两个事务不能锁同一个索引。
  3. insert，delete，update在事务中都会自动默认加上排它锁。

行锁（写&读）

  • A 窗口执行
update test_innodb_lock set b='a1' where a=1;

SELECT * from test_innodb_lock;

我们可以看到 A 窗口可以看到更新后的结果；

  • B 窗口执行
SELECT * from test_innodb_lock;

 

 我们可以看到 B 窗口不能看到更新后的结果，看到的还是老数据，这是因为 a = 1 的这行记录被 A 窗口执行的 SQL 语句抢到了锁，并且没有执行 commit 提交操作。所以窗口 B 看到的还是老数据。这就是 MySQL 隔离级别中的"读已提交"。

  • 窗口 A 执行 commit 操作
COMMIT;
  • 窗口 B 查询
SELECT * from test_innodb_lock;

 这个时候我们发现窗口 B 已经读取到最新数据了。

行锁（写&写）

  • 窗口 A 执行更新 a = 1 的记录
update test_innodb_lock set b='a2' where a=1;

这时候并没有 commit 提交，锁是窗口 A 持有。

  • 窗口 B 也执行更新 a = 1 的记录
update test_innodb_lock set b='a3' where a=1;

可以看到，窗口 B 一直处于阻塞状态，因为窗口 A 还没有执行 commit，还持有锁。窗口 B 抢不到 a = 1 这行记录的锁，所以一直阻塞等待。

  • 窗口 A 执行 commit 操作
COMMIT;
  • 窗口 B 的变化

可以看到这个时候窗口 B 已经执行成功了。

表锁

当索引失效的时候，行锁会升级成表锁，索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer，我们加上引号，就变成了 String，这个时候索引就会失效了。

  • 窗口 A 更新 a = 1 的记录
update test_innodb_lock set b='a4' where a=1 or a=2;

  • 窗口 B 更新 a = 3 的记录
update test_innodb_lock set b='b1' where a=3;

这个时候发现，虽然窗口 A 和 B 更新的行不一样，但是窗口 B 还是被阻塞住了，就是因为窗口 A 的索引失效，导致行锁升级成了表锁，把整个表锁住了，索引窗口 B 被阻塞了。

  • 窗口 A 执行 commit 操作

可以看到这个时候窗口 B 已经执行成功了。

间隙锁

间隙锁是innodb中行锁的一种， 但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，有时候也会给我们带来麻烦，我们就遇到了。 在数据库参数中， 控制间隙锁的参数是：innodb_locks_unsafe_for_binlog， 这个参数默认值是OFF， 也就是启用间隙锁， 他是一个bool值， 当值为true时表示disable间隙锁。那为了防止间隙锁是不是直接将innodb_locaks_unsafe_for_binlog设置为true就可以了呢？ 不一定！而且这个参数会影响到主从复制及灾难恢复， 这个方法还尚待商量。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

  • 什么是间隙锁

当我们采用范围条件查询数据时，InnoDB 会对这个范围内的数据进行加锁。比如有 id 为：1、3、5、7 的 4 条数据，我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中，但是不存在这些数据记录，这些 2、4、6 就被称为间隙。

  • 间隙锁的危害

范围查找时，会把整个范围的数据全部锁定住，即便这个范围内不存在的一些数据，也会被无辜的锁定住，比如我要在 1、3、5、7 中插入 2，这个时候 1-7 都被锁定住了，根本无法插入 2。在某些场景下会对性能产生很大的影响

  • 间隙锁演示

我们先把字段 a 的值修改成 1、3、5、7、9

  • 窗口 A 更新 a = 1~7 范围的数据
update test_innodb_lock set b='b5' where a>1 and a<7;

  • 窗口 B 在 a = 2 的位置插入数据
insert into test_innodb_lock values(2, "b6");

 这个时候发现窗口 B 更新 a = 2 的操作一直在等待，因为 1~7 范围的数据被间隙锁，锁住了。只有等窗口 A 执行 commit，窗口 B 的 a = 2 才能更新成功。

 参考资料：https://segmentfault.com/a/1190000037534778

建议：

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少索引条件，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度

http://chatgpt.dhexx.cn/article/o0XKefgY.shtml

相关文章

Mysql_行锁、临键锁、间隙锁的理解

Mysql_行锁、临键锁、间隙锁的理解

目录 行锁间隙锁临键锁总结 行锁 行锁&#xff0c;也称为记录锁。 当我们针对主键或者唯一索引加锁的时候&#xff0c;Mysql默认会对查询的这一行数据加行锁&#xff0c;避免其他事务对这一行数据进行修改。 间隙锁 间隙锁&#xff0c;顾名思义&#xff0c;就是锁定一个索引…
阅读更多...
【MySQL】MySQL的锁机制 - 记录锁、间隙锁、临键锁

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

目录 一、参考链接二、总结1、间隙锁的目的2、什么时候产生间隙锁&#xff1f;3、间隙锁锁定的范围4、需要注意的点&#xff08;重要&#xff09; 三、案例建表案例1:查询已存在的单条记录案例2:查询不存在的记录案例3:查询多条记录&#xff08;范围查询&#xff09; 四、其余验…
阅读更多...
MySQL的锁机制 - 记录锁、间隙锁、临键锁

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

记录锁(Record Locks) 记录锁是 封锁记录&#xff0c;记录锁也叫行锁&#xff0c;例如&#xff1a; SELECT * FROM test WHERE id1 FOR UPDATE; 它会在 id1 的记录上加上记录锁&#xff0c;以阻止其他事务插入&#xff0c;更新&#xff0c;删除 id1 这一行。 记录锁、间隙锁…
阅读更多...
行锁、间隙锁、next-key锁

行锁、间隙锁、next-key锁

参考博客&#xff1a;cmysql锁&#xff08;九&#xff09;innodb下的记录锁&#xff0c;间隙锁&#xff0c;next-key锁 - 简书 Mysql 行锁、间隙锁和next-key锁详解_程序员掉头发的博客-CSDN博客_行锁 间隙锁 行锁(Record Lock)&#xff1a; 对索引记录加锁。间隙锁(Gap Lock…
阅读更多...
mysql记录锁、间隙锁、临键锁

mysql记录锁、间隙锁、临键锁

名词解释 记录锁&#xff1a;record lock,即锁住一条记录 间隙锁&#xff1a;gap lock,即锁定一个区间&#xff0c;左开又开 临键锁&#xff1a;记录锁间隙锁锁定的区间&#xff0c;左开右闭 mysql如何加锁 假设有如下数据 其中&#xff0c;id 是主键索引&#xff08;唯一索…
阅读更多...
【MySQL】记录锁？间隙锁？临键锁？到底锁了些什么？这一篇帮你捋清楚(￣∇￣)/

【MySQL】记录锁？间隙锁？临键锁？到底锁了些什么？这一篇帮你捋清楚(￣∇￣)/

特别强调&#xff5e; 本测试使用的是MySQL 8.0.27&#xff5e; 8.0.27&#xff5e; 8.0.27&#xff08;因为不同版本命令可能会有差异哈&#xff09; 打开两个终端&#xff0c;分别连接上MySQL&#xff0c;使用select global.transaction_isolation;查看隔离级别&#xff08…
阅读更多...
MySQL的锁机制——记录锁、间隙锁、临键锁

MySQL的锁机制——记录锁、间隙锁、临键锁

记录锁(Record Locks) 记录锁锁住的是索引记录&#xff0c;记录锁也叫行锁。如果使用索引作为条件命中了记录&#xff0c;那么就是记录锁&#xff0c;被锁住的记录不能被别的事务插入相同的索引键值&#xff0c;修改和删除。 例如&#xff1a; select * from test_table whe…
阅读更多...
mysql 间隙锁

mysql 间隙锁

一、什么是间隙锁&#xff1f; 间隙锁&#xff08;Gap Lock&#xff09;&#xff1a;锁加在不存在的空闲空间&#xff0c;可以是两个索引记录之间&#xff0c;也可能是第一个索引记录之前或最后一个索引之后的空间。 当我们用范围条件而不是相等条件索引数据&#xff0c;并请求…
阅读更多...
什么是间隙锁

什么是间隙锁

中心思想 间隙锁锁的是索引叶子节点的next指针。 意义 解决了mysql RR级别下是幻读的问题。 快照读 在RR隔离级别下&#xff1a;快照读有可能读到数据的历史版本&#xff0c;也有可能读到数据的当前版本。所以快照读无需用锁也不会发生幻读的情况。 当前读 当前读&#…
阅读更多...
MySql进阶-间隙锁（gap-key）

MySql进阶-间隙锁（gap-key）

文章目录 Innodb锁算法关闭Gap LockGap-key 解决的问题间隙锁影响MVCC 核心原理ReadView 可参考 快照读&#xff0c;当前读可参考 参考《InnoDB存储引擎》 注意&#xff1a;gap-key是innodb存储引擎来解决当前读的幻读问题的。对于隔离级别下的可重复读只能解决快照读的幻读问题…
阅读更多...
dubbo接口调试工具

dubbo接口调试工具

最近的项目使用来dubbo进行开发&#xff0c;虽然可以使用telne客户端t进行dubbo的接口的调试&#xff0c;但总感觉调试起来不太方便&#xff0c;并且限制太多&#xff0c;于是抽了点时间出来编写了一个dubbo的客户端可视化调试工具&#xff0c;功能虽简单但可以快速的调试dubbo…
阅读更多...
Android Studio调试工具总结

Android Studio调试工具总结

前言&#xff1a;写代码不可避免有Bug&#xff0c;通常情况下除了日志最直接的调试手段就是debug&#xff1b;当我们的程序出现bug时&#xff0c;调试可以快速的找到bug。进入调试状态&#xff0c;我们可以清楚的了解程序的整个执行过程&#xff0c;可以对内存的数据进行监视。…
阅读更多...
串口调试、udp 调试、tcp 调试，websocket 调试，通讯调试工具

串口调试、udp 调试、tcp 调试，websocket 调试，通讯调试工具

简介 一个通讯测试工具&#xff0c;QSAK&#xff08;Qt Swiss Army Knife&#xff09;是一款基于Qt开源框架打造的多功能、跨平台调试工具。目前支持串口调试、udp 调试、tcp 调试及 websocket 调试等。支持 Windows、Linux、raspberry pi 等平台。 免费、开源、绿色、免安装…
阅读更多...
chrome--浏览器调试工具详解

chrome--浏览器调试工具详解

chrome浏览器开发调试工具打开方式&#xff1a;F12键 一&#xff0c;常用面板介绍 1.定位小箭头按钮(左边第一个)&#xff1a; 选中Elements面板&#xff0c;并启动该按钮&#xff0c;可以在页面中定位相应元素的源代码位置&#xff0c;或者选择源代码位置可定位到页面相应的元…
阅读更多...
通讯调试工具推荐

通讯调试工具推荐

CommBox通讯调试工具-简单说明 工具支持&#xff1a;串口通讯、串口代理、TCP、UDP、Telnet、Ping、TFtp、串口监视、以太网嗅探等通讯测试 1、本工具支持预定义命令&#xff0c;命令可以进行分组&#xff0c;由树形控件管理。点击“命令编辑”即可编辑预…
阅读更多...
modbus调试工具的使用

modbus调试工具的使用

modbus调试工具的使用分为2部分&#xff0c;2者互相不影响&#xff0c;可以单独使用。 一、根据IP进行模拟通信 二、根据COM进行模拟通信 一、根据IP进行模拟通信 1.安装完成ModbusPollSetup64Bit后&#xff0c;输入注册码进行注册&#xff1b;安装完成ModbusSlaveSetup64Bi…
阅读更多...
两款常用的 MQTT 调试工具

两款常用的 MQTT 调试工具

文章目录 一、前言二、预备知识三、MQTTBox1、介绍2、下载3、入门使用4、负载测试 四、MQTT.fx1、介绍2、下载3、入门使用4、JS 脚本 一、前言 我们可以使用 MQTT 客户端来测试 MQTT 的通讯功能&#xff0c;这里介绍常用的两款工具 MQTTBox 和 MQTT.fx 。 二、预备知识 MQTT…
阅读更多...
简易Window BLE调试工具

简易Window BLE调试工具

简易Windows BLE调试工具 一、简介 Windows BLE调试工具是一款运行在Windows下的BLE调试软件&#xff0c;实现了扫描、连接、获取BLE设备上的服务以及向服务写入和读取数据的功能。 二、运行要求 系统需要使用Windows 10及以上的版本;需要电脑带有蓝牙功能或者使用外接蓝牙…
阅读更多...
wsdl在线调试工具

wsdl在线调试工具

1.下载soapui 2.进行调试 注:1.wsdl地址在测试服务器上一定要能联通 2.测试时如果是xml标签,需要进行转义 http://www.ku51.net/rehtml/ 3.在wsdl地址后面拼接?wsdl能够看到具体的接口信息 3.使用post进行请求示例
阅读更多...
【sscom】 串口调试工具

【sscom】 串口调试工具

这款串口调试工具我是经常使用的&#xff0c;这里推荐给大家&#xff0c;功能比较全面。 1、正常的串口调试功能。 2、可以保存一些经常发送的数据内容。 3、可以作为网络调试工具使用。 关注公众号&#xff1a;逸趣汇 微信号&#xff1a;yiquhui666 发送&#xff1a;010201 …
阅读更多...
推荐文章

Copyright @ 2022~2023