如何理解MySQL行锁、表锁、间隙锁

发布时间:2021-10-22 15:51:05 作者:iii
来源:亿速云 阅读:179

本篇内容介绍了“如何理解MySQL行锁、表锁、间隙锁”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

准备工作

创建表 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 各种锁演示

行锁(写&读)

update test_innodb_lock set b='a1' where a=1;
SELECT * from test_innodb_lock;

如何理解MySQL行锁、表锁、间隙锁

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

SELECT * from test_innodb_lock;

如何理解MySQL行锁、表锁、间隙锁

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

COMMIT;
SELECT * from test_innodb_lock;

如何理解MySQL行锁、表锁、间隙锁

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

行锁(写&写)

update test_innodb_lock set b='a2' where a=1;

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

update test_innodb_lock set b='a3' where a=1;

如何理解MySQL行锁、表锁、间隙锁

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

COMMIT;

  窗口 B 的变化

如何理解MySQL行锁、表锁、间隙锁

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

表锁

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

update test_innodb_lock set b='a4' where a=1 or a=2;
update test_innodb_lock set b='b1' where a=3;

如何理解MySQL行锁、表锁、间隙锁

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

COMMIT;

如何理解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

update test_innodb_lock set b='b5' where a>1 and a<7;
insert into test_innodb_lock values(2, "b6");

如何理解MySQL行锁、表锁、间隙锁

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

行锁分析

show status like 'innodb_row_lock%';

如何理解MySQL行锁、表锁、间隙锁

“如何理解MySQL行锁、表锁、间隙锁”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!

推荐阅读:
  1. mysql 间隙锁 Gap Lock
  2. mysql表锁和行锁有哪些区别

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:怎么检查Linux中的可用磁盘空间

下一篇:怎么简化Windows 10的启动速度

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》