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

前言

我们前几篇讲了索引是什么,如何使用 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,模拟一个抢到锁,一个没抢到被阻塞住了。

行锁(写&读)

  • A 窗口执行

update test_innodb_lock set b='a1' where a=1;
复制代码
SELECT * from test_innodb_lock;
复制代码

MySQL行锁、表锁、间隙锁详解
我们可以看到 A 窗口可以看到更新后的结果

  • B 窗口执行

SELECT * from test_innodb_lock;
复制代码

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

  • 窗口 A 执行 commit 操作

COMMIT;
复制代码
  • 窗口 B 查询

SELECT * from test_innodb_lock;
复制代码

MySQL行锁、表锁、间隙锁详解
这个时候我们发现窗口 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;
复制代码

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

  • 窗口 A 执行 commit 操作

COMMIT;
复制代码
  • 窗口 B 的变化

MySQL行锁、表锁、间隙锁详解
可以看到这个时候窗口 B 已经执行成功了

表锁

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

  • 窗口 A 更新 a = 1 的记录

update test_innodb_lock set b='a4' where a=1 or a=2;
复制代码
  • 窗口 B 更新 a = 2 的记录

update test_innodb_lock set b='b1' where a=3;
复制代码

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

  • 窗口 A 执行 commit 操作

COMMIT;
复制代码
  • 窗口 B 的变化

MySQL行锁、表锁、间隙锁详解
可以看到这个时候窗口 B 已经执行成功了

间隙锁

  • 什么是间隙锁

当我们采用范围条件查询数据时,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");
复制代码

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

行锁分析

  • 执行 SQL 分析命令

show status like 'innodb_row_lock%';
复制代码
MySQL行锁、表锁、间隙锁详解
  • Variable_name 说明

    • Innodb_row_lock_current_waits:当前正在等待锁定的数量。

    • Innodb_row_lock_time:从系统启动到现在锁定的时长。

    • Innodb_row_lock_time_avg:每次等待锁所花平均时间

    • Innodb_row_lock_time_max:从系统启动到现在锁等待最长的一次所花的时间。

    • Innodb_row_lock_waits:系统启动后到现在总共等待锁的次数。

结语

大家可以根据 Variable_name 这几个参数考虑是否要进行优化,如果锁定时间,锁定次数过大,那就该考虑优化了。优化手段可以参考之前索引优化的文章

IT 老哥


一个在大厂做高级Java开发程序猿

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

关注微信公众号:IT 老哥

回复:Java 全套教程,即可领取:Java 基础、Java web、JavaEE 全部的教程,包括 spring boot 等

回复:简历模板,即可获取 100 份精美简历

回复:Java 学习路线,即可获取最新最全的一份学习路线图

回复:Java 电子书,即可领取 13 本顶级程序员必读书籍

原文 

https://juejin.im/post/5f0e67015188252e65442795

本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » MySQL行锁、表锁、间隙锁详解

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址