云栖号资讯:【点击查看更多行业资讯】
在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来!
今天我们来讲讲MySQL的各种锁,这里存储引擎我们使用InnoDB。
准备工作
创建表 tb_innodb_lock
drop table if exists test_innodb_lock;
CREATE TABLE test_innodb_lock (
a INT (11)/ ` 3 P z i ` ! T,
b VAR$ : w Q x u y o `CHAR (20)
) ENGINEF I f l M INNODB DEFAULT charset = utf8;
insert into test_n s . Xinnodb_loc; # lk values (1,'a');
insert into test_inn* h b N 8 J 1odb_lock values (2,'b');
insert into test_innodb_lock values3 ( i | (3,'c');
insert into test_innodb_lock v& c Yalues (4,'d');
insert inh L U eto test_innodb_lock values (5,'e');
创建索引
create index idx_lock_a on test_innodb_lock(a);
create index idx_lock_b on test_innodR $ w _ #b_lock(b);
MySQL 各种锁演A R v @ (示
先将自动提交事务改成手动提交:set autocommit=0;
我们启动两个会话窗h 4 5 & v v口 A 和 B,模拟一个抢到锁,一个没抢到被阻塞住了。
行锁(写&读)
A 窗口执行
update test_innodb_lock set b=I ] H E , B O'a1' where a=1;
SELECT * from test_innodb_lock;
我们可以看到 A 窗口可以看到更新后的结果
B 窗口执行
SELECT * from test_innodb_lock;
我们可以看到 B 窗口不能F ~ d & I T O @看到更新后的结果,看到的还是老数据,这是因为 a = 1 的n k 1 S C & F C这行记录被 A 窗口执行的 SQL 语句抢到了锁,并且没有执行 commit 提交操作。所以窗口 B 看到的还是老数据。这就是 MySQL 隔离级别中的"读已提交"。
窗口 A 执行 commit 操作
COMMIT;
窗口 B 查询
SELECT *: 2 $ * from test_i2 & e lnnodb_lock;
这个时候我们发现窗口 B 已经读取到最新数据了
行锁(写&写)
窗口 A 执行更新 a = 1 的记录
update test_innodb_lock set b=s p e 5 A j m'a2' where a=1;
这时候并没有 com& 0 % # ~mit 提交,锁是窗口 A 持有。
窗口 B 也p @ X { / h c * [执行更新 a = 1 的记录
update test_innodb_lock set b='a3' where a=1;
可以看到,窗口 B 一直处于阻塞状态,因为窗口 A 还没有执行 commit,还持有锁。窗口 B 抢不到 a = 1 这行记录的锁,所以一直阻塞等待。
窗口 Al a i p 0 f B y 执行 commit 操作
COMMIT;
窗口 B 的变化
可以看到这个时候窗口 B 已经执行成功了
表锁
当索引失效的时候,行锁m K o会升级成表l e e - _锁,索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer,我们加上引号,就变成了 String,这个时候索引就会失效了。
窗口 A 更新 a = 1 的记录
update test_innodb_lock} m z 7 4 , ) ( @ set b='a4' whH , Kere a=1 or a=2;
窗口 B 更新 a = 2 的记录
update test_innodb_lock set b='e B xb1' where a=3;
这个时候发现,虽L Y , Z ? { # ^然窗口 A 和 B 更新的行不一样,但是窗口 BB ] l r j P A t 还是被阻塞住了,就是因为窗口 A 的索引失效,导致行锁升级成了表锁,把整个表锁住了,索引窗口 B 被阻塞了。
窗口 A 执行 commit 操作
COMMIT;
窗口 B 的变化
可以看到这个时候窗口 B 已经执行成功了
间隙M n i B锁
什么是间隙锁
当我们采用范围条d J r f 8 d V r件查询数据时,InnoDB 会对这个范围内的数据进行加4 b @ a V 8 I锁。比如有 id 为V e y H Q o h:1、3、5、7G - a 的 4 条数据,我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、m * ] H 1 +6 也在 1-7 的范围中,但是不存在这些数据记录,这些 2、4、6 就被称为间隙。
间隙锁的危害
范围查找时,会把整个范围! / ) M F . + _的数据全部锁定住,即便这个范围内不存在的一些数_ Q j E $ 2 R [ &据,也会被无辜的锁定住,比如我要在 1、N s # $ k Q z Y3、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 a@ V K . ]nd a<7;
窗口 B 在 a = 2 的位置插入数据
insert into test_innodb_lock values(9 H ] T o # 6 .2, "b6");
这个时候发现窗口 B 更新 a = 2 的操作一直在等待,因为 1~7 范围的数据被间隙锁,锁住了。只有等窗口 A 执行8 N e commit,窗口 B| s } u q = . 的 a = 2 才能更新成功
行锁分析
执行 SQL 分析命令
show status like 'innodb_row_lock%~ ~ O o X ?';
VarJ * ,iable_name 说明
Inno| , , 1 M M |db_row_lock_current_waits:当前正在等待锁~ g f定的数量。
Innodb_row_lock_time:从系统启动到现在锁定的时长。
Inn; u N c c Nodb_row_lock_time_avg:每次等待锁所x x n X I花平均时间。
Innodb_row: w H )_lock_time_max:从系统启动到现在锁等待l 1 W ! ; =最长的一次所花的@ * j G时间。
Innodb_row_lock_waits:系统启动后到现在总共等待锁的N f ~ d ] |次数。
结语
大家可以根据 Variable_name 这几个参数考虑是否要进行优化,如果锁定时间,锁定次数过大,那就该考虑优化了。优化手段可以参考之前索引优化的文章。
【云栖号在线课堂】每天都有产品技术专家分享!
课程地址:https://yqh.alW ? j C = Q {iyun.com/live
立即加入社群x | * A N u a -,与专家面对面,及时4 0 / Y了解课程最新动态!
【云栖号在i q T O q / q线课堂 社群】https://c.tb.cn/F3.Z8gC F N 3 9vnK
原文发布时间:2020-07-15
本文作者:IT老哥U 6 N
本文来自:“掘金”,了解相关信息h r Z , :可以关注“掘金”