MySQL行锁、表锁、间隙锁，你都了解吗

不念不忘少年蓝@ 2023-10-06 21:13 65阅读 0赞

### 准备工作 ###

#### 创建表 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;

![图片][ac3eec3565a8cab2905ff9b36973f17f.png]

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

*  B 窗口执行

SELECT * from test_innodb_lock;

![图片][d245b468b38f7686bf9073545f02da27.png]

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

*  窗口 A 执行 commit 操作

COMMIT;

*  窗口 B 查询

SELECT * from test_innodb_lock;

![a0fe6aa445f643318c72ea878b1a475e.gif][]这个时候我们发现窗口 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;

![图片][24124071fb6fba0925ff8e161812ce7f.png]

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

*  窗口 A 执行 commit 操作

COMMIT;

*  窗口 B 的变化

![图片][e7202ad5deaeb6288b1c232ea0075ebc.png]

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

![图片][09509e84f7771f3f6612a3fce2b2e521.png]

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

*  窗口 A 执行 commit 操作

COMMIT;

*  窗口 B 的变化

![图片][a3bdc0a06b172ab8f442af0e0bc304dc.png]

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

![图片][fd959c98c0e14f1607535872b8903367.png]

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

#### 行锁分析 ####

*  执行 SQL 分析命令

show status like 'innodb_row_lock%';

![图片][cdff19be5c42cb3d96ab41c549bdcc43.png]

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

**文末福利**  
 **可以加小新老师vx免费获取【Java高清路线图】和【全套学习视频和配套资料】**  
  
![bb64c42ddf2645459cc526e6bd61f64b.png][]

[ac3eec3565a8cab2905ff9b36973f17f.png]: https://img-blog.csdnimg.cn/img_convert/ac3eec3565a8cab2905ff9b36973f17f.png
[d245b468b38f7686bf9073545f02da27.png]: https://img-blog.csdnimg.cn/img_convert/d245b468b38f7686bf9073545f02da27.png
[a0fe6aa445f643318c72ea878b1a475e.gif]: https://img-blog.csdnimg.cn/a0fe6aa445f643318c72ea878b1a475e.gif
[24124071fb6fba0925ff8e161812ce7f.png]: https://img-blog.csdnimg.cn/img_convert/24124071fb6fba0925ff8e161812ce7f.png
[e7202ad5deaeb6288b1c232ea0075ebc.png]: https://img-blog.csdnimg.cn/img_convert/e7202ad5deaeb6288b1c232ea0075ebc.png
[09509e84f7771f3f6612a3fce2b2e521.png]: https://img-blog.csdnimg.cn/img_convert/09509e84f7771f3f6612a3fce2b2e521.png
[a3bdc0a06b172ab8f442af0e0bc304dc.png]: https://img-blog.csdnimg.cn/img_convert/a3bdc0a06b172ab8f442af0e0bc304dc.png
[fd959c98c0e14f1607535872b8903367.png]: https://img-blog.csdnimg.cn/img_convert/fd959c98c0e14f1607535872b8903367.png
[cdff19be5c42cb3d96ab41c549bdcc43.png]: https://img-blog.csdnimg.cn/img_convert/cdff19be5c42cb3d96ab41c549bdcc43.png
[bb64c42ddf2645459cc526e6bd61f64b.png]: https://img-blog.csdnimg.cn/bb64c42ddf2645459cc526e6bd61f64b.png