一文搞懂MySQL行锁、表锁、间隙锁详解

一文搞懂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 各种锁演示

行锁（写&读）

A 窗口执行

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

SELECT * from test_innodb_lock;

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

B 窗口执行

SELECT * from test_innodb_lock;

COMMIT;

窗口 B 查询

SELECT * from test_innodb_lock;

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

行锁（写&写）

窗口 A 执行更新 a = 1 的记录

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

窗口 B 也执行更新 a = 1 的记录

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

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 = 2 的记录

update test_innodb_lock set b='b1' where a=3;

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

COMMIT;

窗口 B 的变化

可以看到这个时候窗口 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");

行锁分析

执行 SQL 分析命令

show status like 'innodb_row_lock%';

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 这几个参数考虑是否要进行优化，如果锁定时间，锁定次数过大，那就该考虑优化了。优化手段可以参考之前索引优化的文章。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。