21 | 为什么我只改一行的语句,锁这么多?
![](https://static001.geekbang.org/resource/image/be/04/bef7889aea4e542f24747e0aba819e04.jpg)
该思维导图由 AI 生成,仅供参考
- 深入了解
- 翻译
- 解释
- 总结
![](https://static001.geekbang.org/resource/image/d0/e4/d0880927e2a522a038be2d04bf19dfe4.png)
本文深入探讨了MySQL加锁规则,通过详细解释加锁规则的原则、优化和bug,以及介绍了间隙锁和next-key lock的概念。通过案例分析,读者可以了解MySQL在不同情况下的加锁方式,以及加锁规则对并发操作的影响。文章通过图示和实例分析,使读者更容易理解MySQL加锁规则的复杂性和实际应用。此外,还提到了在不同查询条件下加锁的具体规则,并对可重复读隔离级别和读提交隔离级别下的加锁规则进行了比较。通过本文,读者可以快速了解MySQL加锁规则的基本原则和优化策略,为实际应用中的数据库操作提供了有益的参考。
《MySQL 实战 45 讲》,新⼈⾸单¥68
全部留言(355)
- 最新
- 精选
- 古娜拉黑暗之神·巴啦啦能量·堕...置顶老师,您好。假期的没跟上,今天补到了这节课,看了之后有几点不是太明白。望能解答一下。 1. 索引c上的锁算不算是行锁。假如索引c上的next-key lock为(0,5] (5,10],那么5算不算是c上的行锁? 2. 在案例六中,执行 “delete from t where c=10;” 语句,索引c上的next-key lock是(5,10],(10,10],(10,15)。那么主键索引上的锁是什么呢?是只有行锁,锁住的是 (10,10,10) 和 (30,10,30) 两行吗? 3. 也是在案例六中,session A不变,在session B中执行 “update t_20 set d=50 where c=5;”、“update t_20 set d=50 where c=15;”、“insert into t_20 values(40,15,40);”均执行成功,但执行“insert into t_20 values(50,5,50);” 时,却被阻塞。为什么呢?具体执行语句如下 session A mysql> begin; mysql> explain delete from t_20 where c=10; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 DELETE t_20 range c c 5 const 2 100 Using where mysql> delete from t_20 where c=10; session B mysql> update t_20 set d=50 where c=5; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update t_20 set d=50 where c=15; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into t_20 values(40,15,40); Query OK, 1 row affected (0.00 sec) mysql> explain insert into t_20 values(50,5,50); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | t_20 | NULL | ALL | c | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set (0.00 sec) mysql> insert into t_20 values(50,5,50); (block) 我使用的mysql版本是:5.7.23-0ubuntu0.16.04.1 show variables的结果太多,我截取了一部分,或许对您分析有帮助: innodb_version 5.7.23 protocol_version 10 slave_type_conversions tls_version TLSv1,TLSv1.1 version 5.7.23-0ubuntu0.16.04.1 version_comment (Ubuntu) version_compile_machine x86_64 version_compile_os Linux
作者回复: 1. Next-key lock 就是间隙锁➕行锁,所以包含=5这一行 2. 对 3. (c=5,id=50)是在这个gap里哦,你试试插入(1,5,50)对比一下。好问题
2019-01-033273 - Chu~♡·の~是不是可以总结为: 1、查询过程中访问到的对象才会加锁,而加锁的基本单位是next-key lock(前开后闭); 2、等值查询上MySQL的优化:索引上的等值查询,如果是唯一索引,next-key lock会退化为行锁,如果不是唯一索引,需要访问到第一个不满足条件的值,此时next-key lock会退化为间隙锁; 3、范围查询:无论是否是唯一索引,范围查询都需要访问到不满足条件的第一个值为止;
作者回复: 嗯嗯,可以的👍🏿
2019-01-029171 - undifined遇到一个有趣的问题,在老师的解答下终于弄明白了: CREATE TABLE z ( id INT PRIMARY KEY AUTO_INCREMENT, b INT, KEY b(b) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO z (id, b) VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10); session A BEGIN; SELECT * FROM z WHERE b = 6 FOR UPDATE; session B INSERT INTO z VALUES (0, 4); 这里为什么会被锁住 答案比较长,写在我自己的笔记里了,地址是 https://helloworlde.github.io/blog/blog/MySQL/MySQL-%E4%B8%AD%E5%85%B3%E4%BA%8Egap-lock-next-key-lock-%E7%9A%84%E4%B8%80%E4%B8%AA%E9%97%AE%E9%A2%98.html 大家可以看看
作者回复: 好问题,质量很高的笔记
2019-01-0753123 - Geek_9ca34e老师,你好: 我练习实例的时候发现一个问题:如 案例五:唯一索引范围锁 bug begin; select * from t where id>10 and id<=15 for update; 1、执行如上语句加锁范围(10,15]和(15,20]; 2、因为10未加锁,所以我单独再开一个连接,执行delete from t where id=10;不会锁等待,能正常删除; 3、但是我再执行insert into t values(10,10,10); 语句会等待,无法正常执行; 4、经过分析我发现第一个连接执行的语句的加锁范围已经变成(5,15]和(15,20],代表锁蔓延了;这是什么原因呢?
作者回复: 好问题,我会加到答疑文章中, Gap是一个动态的概念
2019-01-09981 - HuaMax首先老师新年快乐,学习专栏受益良多! 上期问过老师的问题已了解答案,锁是加在索引上的。再尝试回答问题。c上是普通索引,根据原则2,访问到的都要加锁,在查询c>=15这个条件时,在查找到15后加锁(10,15],继续往右查找,按理说不会锁住6这个索引值,但查询语句中加了order by c desc,我猜想会优化为使用c<=20这条语句,查找到20后往左查找,这样会访问到15左边的值10,从而加锁(5,10],不知我理解对否?
作者回复: 新年好 对的👍🏿
2019-01-011558 - 时隐时现不好意思,这次又来晚了,看这种连载技术文章,和看小说一样,养肥了集中看~~ 这次的问题如下,希望丁老师有空解答一下。 版本:mysql 5.6.39 CREATE TABLE `t` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into t values(1,1),(2,2),(3,3),(4,4),(5,5); 采用READ-COMMITTED隔离级别 案例1、 session A: begin; update t set a=6 where b=1; session B: begin; update t set a=7 where b=2; A和B均能执行成功 问题1:官档上说对于RC且全表扫描的update,先逐行添加行锁然后释放掉不符合where条件的,那么session A成功对(1,1)加锁,理论上session B在扫描(1,1)并尝试加锁时会被阻塞,为何还能执行成功?官档链接:https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html 案例2: session A: begin; update t set a=6 where b=1; session B: begin; delete from t where b=2; -- 被阻塞 问题2:为何案例1 中的session B不会被阻塞,而案例2的却被session A的行数阻塞,update和delete都是全部扫描,难道加锁机制不一样?
作者回复: 好问题,在read-commited隔离级别下,update语句 有一个“semi-consistent” read优化, 意思是,如果update语句碰到一个已经被锁了的行,会读入最新的版本,然后判断一下是不是满足查询条件, a)如果不满足,就直接跳过; b) 如果满足,才进入锁等待 你的第二个问题:这个策略,只对update有效,delete无效 新春快乐~
2019-01-301145 - geraltlaush老师,案例八session B的操作语句update t set d = d + 1 where c =10; 由于c是非唯一键索引,锁(5,10」可以理解 ,为什么不锁(10,15} 呢,不是应该继续向后扫描直到第一个不满足条件的值为止吗
作者回复: 好问题,新年快乐 会锁的,只是因为在(5,10]就被锁住了,所以后面的锁加不上去了😆
2019-01-291036 - 陈老师在案列一中update t set d=d+1 where id=7 中id是主键也是唯一索引,按优化1应该退化成行锁才对,为什么insert into t values(8,8,8)会被锁住,我是那儿理解错了?
作者回复: 这一行存在的时候是行锁,这一行不存在,那就是间隙锁啦。 insert into t values(8,8,8)是被主键上(5,10)的间隙锁锁住的
2019-01-11634 - 看不到de颜色老师您好。在自己跑案例四的时候,回想起之前MVCC的内容就对案例做了些调整,此时产生一些有关一致性试图的疑惑,还想请老师排疑解惑。 MySQL8.0.13,RR隔离级别下,表结构及数据按照例子中不变,执行语句如下: session A:begin; select * from t where c>=10 and c<11 for update; --锁住(5,15] session B:update t set d=d+1 where c=5; --成功,changed:1 session A:select * from t; --读到d=6 此时,session A居然读到了session B update后的结果。语句调整后,把session A中的 for update去掉,依据RR级别下一致性视图原理不会读到session B更新后的结果。 那么,是否可以理解begin后执行的select语句使用for update/lock in share mode并不会生成一致性视图呢? 还请老师指点迷津,感激不尽(❁´ω`❁)
作者回复: 确实是这样的, 一致性视图的生成时间是 第一次执行一致性读的时候
2019-02-251130 - 约书亚早晨睡不着打开极客时间一看,竟然更新了。今天是周日而且在假期中哎...
作者回复: 风雨无阻 节假日不休,包括元旦和春节😄
2018-12-31229