前言
我们前几篇讲了索引是什么,如何使用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 各种锁演示
行锁(写&读)
- update test_innodb_lock set b='a1' where a=1;
- SELECT * from test_innodb_lock;
我们可以看到 A 窗口可以看到更新后的结果
- SELECT * from test_innodb_lock;
我们可以看到 B 窗口不能看到更新后的结果,看到的还是老数据,这是因为 a = 1 的这行记录被 A 窗口执行的 SQL 语句抢到了锁,并且没有执行 commit 提交操作。所以窗口 B 看到的还是老数据。这就是 MySQL 隔离级别中的"读已提交"。
- COMMIT;
- SELECT * from test_innodb_lock;
这个时候我们发现窗口 B 已经读取到最新数据了
行锁(写&写)
- update test_innodb_lock set b='a2' where a=1;
这时候并没有 commit 提交,锁是窗口 A 持有。
- update test_innodb_lock set b='a3' where a=1;
可以看到,窗口 B 一直处于阻塞状态,因为窗口 A 还没有执行 commit,还持有锁。窗口 B 抢不到 a = 1 这行记录的锁,所以一直阻塞等待。
- COMMIT;
窗口 B 的变化
可以看到这个时候窗口 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;
这个时候发现,虽然窗口 A 和 B 更新的行不一样,但是窗口 B 还是被阻塞住了,就是因为窗口 A 的索引失效,导致行锁升级成了表锁,把整个表锁住了,索引窗口 B 被阻塞了。
- COMMIT;
可以看到这个时候窗口 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");
这个时候发现窗口 B 更新 a = 2 的操作一直在等待,因为 1~7 范围的数据被间隙锁,锁住了。只有等窗口 A 执行 commit,窗口 B 的 a = 2 才能更新成功
行锁分析
- show status like 'innodb_row_lock%';
结语
大家可以根据 Variable_name 这几个参数考虑是否要进行优化,如果锁定时间,锁定次数过大,那就该考虑优化了。优化手段可以参考之前索引优化的文章。
在新的MySQL 8.0.23中,引入了新的有趣功能:不可见列。 这是第一篇关于这个新功...
需要注意的是,调用的封装的数据库,和jQuery的保存地址 一、注册 (1)写文本框...
详解Spring Controller autowired Request变量 spring的DI大家比较熟悉了,对于...
多年以后,面对台下五彩斑斓的Jetbrain和Vscode用户,这位曾经的资深的vim追随者...
git clone支持https和git(即ssh)两种方式下载源码: 当使用git方式下载时,如...
大家好我是爱景甜的网工我是一个思科出身专注于华为的网工 好了话不多说进入正题...
最近在学习jQuery时接触到了show()、hide()、toggle()函数,于是利用这几个函数...
在Asp.net Core之前所有的Action返回值都是ActionResult,Json(),File()等方法返...
1 概述 在接下来的时间里,将会入手ASP.NET MVC这一专题,尽量用最快的时间,最...
一个常见的场景,获取:标签背景图片链接: 如字符串:var bgImg = "url (\" htt...