[面试避险] MySQL for update 到底是 row lock / table lock
作者
: _沸羊羊_
来源: juejin.cn/post/7006522876207562759
Part1前言
MySQL 在使用 for update 进行查询操作时,锁的是 row 还是 table 呢?答案见文末。
本文测试的环境为 MySQL 8.0.21
Part2验证
MySQL for update 时使用索引 检索数据的情况下,使用的是 row lock,而不使用索引检索数据的话,是 table lock,下面我们先来通过实验验证这个说法。
打开两个MySQL
连接,将其中一个连接关闭自动提交事务。
-- 查询事务提交方式
select @@autocommit;
-- 关闭自动提交事务
set autocommit = 0;
现在有一个 user
表,表中存储数据如下:
表中索引结构是只有主键为聚集索引。
1主键索引检索数据
连接1
begin;
select * from user where id = "1" for update;
连接2
update `user` set `name` = "feiyangyang" where id = "1";
由于连接1未提交事务,所以 id="1" 的行记录被加了锁,导致连接2写数据失败。
update `user` set `name` = "feiyangyang" where id = "1"
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.403s
再对 id="2" 的数据进行 update 操作
update `user` set `name` = "feiyangyang" where id = "2";
update `user` set `name` = "feiyangyang" where id = "2"
> Affected rows: 1
> 时间: 0.002s
上述实验测试的有数据的情况,如果无数据的情况呢?
连接1
begin;
select * from user where id = "4" for update;
连接2
update `user` set `name` = "feiyangyang" where id = "4";
发现没有数据的情况下没有锁
update `user` set `name` = "feiyangyang" where id = "4"
> Affected rows: 0
> 时间: 0s
现在确定了 id="1"的记录是被加了行锁(row lock),得出结论:
结论:根据主键索引检索数据时,row lock(有数据),no lock(无数据)
2根据主键索引和非索引字段检索数据
连接1
begin;
select * from `user` where id = "1" and `name` = "test" for update;
连接2
UPDATE `user`
SET `name` = "feiyangyang"
WHERE id = "1";
由于连接1未提交事务,数据行被锁,导致连接2 update 失败
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 51.451s
此时,不提交连接1的事务,使用连接2查询其他行记录,执行成功。
UPDATE `user`
SET `name` = "feiyangyang"
WHERE id = "2";
再来看看无数据的情况
UPDATE `user`
SET `name` = "feiyangyang"
WHERE id = "4";
> Affected rows: 0
> 时间: 0s
结论:根据主键索引和普通字段检索数据,row lock(有数据), no lock(无数据)
3根据非索引字段检索数据
连接1
begin;
select * from `user` where `name` = "test" for update;
连接2
UPDATE `user`
SET pwd = "feiyangyang"
WHERE `name`= "test";
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.385s
UPDATE `user`
SET pwd = "feiyangyang"
WHERE `name`= "feiyangyang";
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.385s
连接2 中执行的第一个 sql 语句与连接1中操作中的是同一个记录行,由于连接1未提交事务,所以连接2 更新失败; 但第二个 sql 语句与连接1并不是同一个记录行,依然更新失败。
再来看看无数据的情况:
连接2
UPDATE `user` SET pwd = "feiyangyang" WHERE `name`= "xiyangyang"
> Affected rows: 0
> 时间: 0.001s
结论:根据非索引字段检索数据,table lock(有数据) no lock(无数据)
4根据普通索引检索数据
为 1.3 中的 name 字段添加索引
create index idx_name on `user`(`name`);
连接1
begin;
select * from `user` where `name` = "test" for update;
连接2
UPDATE `user`
SET pwd = "feiyangyang"
WHERE `name`= "test";
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.385s
UPDATE `user`
SET pwd = "feiyangyang"
WHERE `name`= "feiyangyang";
> Affected rows: 1
> 时间: 0.013s
无数据的情况同上,略。
结论:根据普通索引检索,row lock(有数据),no lock(无数据)
5根据 unique 索引检索数据
将 name 字段的索引修改为 唯一索引
drop index idx_name on `user`;
create unique index idx_name on `user`(name);
连接1
begin;
select * from `user` where `name` = "test" for update;
连接2
UPDATE `user`
SET pwd = "feiyangyang"
WHERE `name`= "test";
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.374s
UPDATE `user`
SET pwd = "feiyangyang"
WHERE `name`= "feiyangyang";
> Affected rows: 1
> 时间: 0.05s
无数据的情况同上,略。
结论:根据唯一索引检索数据时,row lock (有数据),no lock(无数据)
Part3总结
当对索引列检索数据时,有数据的情况下,锁的级别是记录行;而根据非索引字段检索数据时,有数据的情况下,锁的级别是整个表。
MySQL 进行 row lock 还是 table lock 只取决于是否使用了索引,当进行一些让索引失效的操作时,自然进行的是 table lock 了。
那问题来了,为什么对索引字段加排他锁,锁的是行记录,对非索引字段加锁,锁的是整个表?
排他锁锁的是索引项,个人理解就是B+树的叶子节点,当对同一棵B+树的其他叶子节点进行写操作时,是互不影响的。而如果是非索引字段,是没有索引树的结构的,只能锁整个表。
-End-
最近有一些小伙伴,让我帮忙找一些 面试题 资料,于是我翻遍了收藏的 5T 资料后,汇总整理出来,可以说是程序员面试必备!所有资料都整理到网盘了,欢迎下载!
面试题
】即可获取