MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
42926 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

21 | 为什么我只改一行的语句,锁这么多?

林晓斌 2018-12-31
在上一篇文章中,我和你介绍了间隙锁和 next-key lock 的概念,但是并没有说明加锁规则。间隙锁的概念理解起来确实有点儿难,尤其在配合上行锁以后,很容易在判断是否会出现锁等待的问题上犯错。
所以今天,我们就先从这个加锁规则开始吧。
首先说明一下,这些加锁规则我没在别的地方看到过有类似的总结,以前我自己判断的时候都是想着代码里面的实现来脑补的。这次为了总结成不看代码的同学也能理解的规则,是我又重新刷了代码临时总结出来的。所以,这个规则有以下两条前提说明:
MySQL 后面的版本可能会改变加锁策略,所以这个规则只限于截止到现在的最新版本,即 5.x 系列 <=5.7.24,8.0 系列 <=8.0.13。
如果大家在验证中有发现 bad case 的话,请提出来,我会再补充进这篇文章,使得一起学习本专栏的所有同学都能受益。
因为间隙锁在可重复读隔离级别下才有效,所以本篇文章接下来的描述,若没有特殊说明,默认是可重复读隔离级别。
我总结的加锁规则里面,包含了两个“原则”、两个“优化”和一个“bug”。
原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。
原则 2:查找过程中访问到的对象才会加锁。
优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(176)

  • 堕落天使 置顶
    老师,您好。假期的没跟上,今天补到了这节课,看了之后有几点不是太明白。望能解答一下。
    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-03
    1
    16
  • 张三
    Happy New Year !这个专栏绝对是极客时间最好我买过最值的专栏。
    2018-12-31
    1
    72
  • 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-07
    6
    24
  • 约书亚
    早晨睡不着打开极客时间一看,竟然更新了。今天是周日而且在假期中哎...

    作者回复: 风雨无阻 节假日不休,包括元旦和春节😄

    2018-12-31
    1
    20
  • HuaMax
    首先老师新年快乐,学习专栏受益良多!
    上期问过老师的问题已了解答案,锁是加在索引上的。再尝试回答问题。c上是普通索引,根据原则2,访问到的都要加锁,在查询c>=15这个条件时,在查找到15后加锁(10,15],继续往右查找,按理说不会锁住6这个索引值,但查询语句中加了order by c desc,我猜想会优化为使用c<=20这条语句,查找到20后往左查找,这样会访问到15左边的值10,从而加锁(5,10],不知我理解对否?

    作者回复: 新年好

    对的👍🏿

    2019-01-01
    16
  • Leon📷
    老师,案例八session B的操作语句update t set d = d + 1 where c =10; 由于c是非唯一键索引,锁(5,10」可以理解
    ,为什么不锁(10,15} 呢,不是应该继续向后扫描直到第一个不满足条件的值为止吗

    作者回复: 好问题,新年快乐

    会锁的,只是因为在(5,10]就被锁住了,所以后面的锁加不上去了😆

    2019-01-29
    13
  • 时隐时现
    不好意思,这次又来晚了,看这种连载技术文章,和看小说一样,养肥了集中看~~
    这次的问题如下,希望丁老师有空解答一下。
    版本: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-30
    2
    12
  • 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-09
    11
  • 乾坤
    您好,关于"优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。",我觉得改为"从第一个满足等值条件的索引记录开始向右遍历到第一个不满足等值条件记录,并将第一个不满足等值条件记录上的next-key lock 退化为间隙锁"更明确些

    作者回复: 感觉没大差别,嗯嗯,理解就好😄

    2019-01-01
    7
  • 郭江伟
    老师这次的留下的问题,语句跟上次不一样,上期问题语句是select id from t where c>=15 and c<=20 order by c desc for update;;这次缺少了 order by c desc ,不加desc的话insert into t values(6,6,6);不会被堵塞;
    根据优化3:索引上的等值查询,在向右遍历时且最后一个值不满足等值条件的时候next-key lock退化为间隙锁;
    问题中的sql语句加了desc ,是向左扫描,该优化用不上,所以下限10是闭区间,为了防止c为10的行加入,需要锁定到索引c键(5,5)
    此例中insert into t values(6,5,6) 会堵塞,insert into t values(4,5,6) 不会堵塞,

    作者回复: 嗯你说的对

    不过是我少打一个词了,加上去了,要desc哦

    重新分析下😄

    2018-12-31
    7
  • 往事随风,顺其自然
    这和分两步有什么关系?
    (5,10]已经是被锁住,分不分两步来加锁,这个间隙和行锁都被锁住了,session b应该是拿不到锁才对。
    2019-01-01
    2
    6
  • 老师在案列一中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-11
    5
  • Chu~♡·の~
    是不是可以总结为:
    1、查询过程中访问到的对象才会加锁,而加锁的基本单位是next-key lock(前开后闭);
    2、等值查询上MySQL的优化:索引上的等值查询,如果是唯一索引,next-key lock会退化为行锁,如果不是唯一索引,需要访问到第一个不满足条件的值,此时next-key lock会退化为间隙锁;
    3、范围查询:无论是否是唯一索引,范围查询都需要访问到不满足条件的第一个值为止;

    作者回复: 嗯嗯,可以的👍🏿

    2019-01-02
    5
  • Geek_maxwell
    老师有个疑问,就是既然repeat read已经解决了幻读(利用next key lock),那下一个事务级别(序列化)主要是解决什么问题呢? 因为原本我以为repeat read 其实没有解决幻读
    2019-04-14
    1
    2
  • null
    @唯她命 的问题,案例一为啥是间隙锁,而不是行锁?

    我自己的理解:因为 id=7 的记录不存在,没有上锁的实体,所以无法使用优化 1。

    优化 1 是说索引上的等值查询,如果是唯一索引,且记录存在,就退化成行锁。
    优化 2 是说在索引上的等值查询,不管是唯一索引,还是非唯一索引,只有向右遍历时,最后一个值不等于等值查询的条件,就会退化成间隙锁。如 where id=7,扫描完 id=5,接着扫描 id=10,但是 7 不等于 10,所以会退化成间隙锁。

    如果理解有误,还望老师指正😁😁
    2019-04-13
    2
  • 王伯轩
    这两章今天来来回回啃了好几遍,终于感觉有关锁的知识成体系了。

    尤其是总结的加锁原则,简直太精辟了,感谢丁老师.

    原则 2:查找过程中访问到的对象才会加锁。

    对于原则2 我有个疑问,访问到的对象应该还应该包含其影响到的索引。

    老师的例子中某些隐含了这些东西,其他同学的留言中也表明了这点,望老师指点

    比如 一个表t(id ,c,d,e)
    id是主键 ,其他列都有非唯一索引。
    执行insert 需要获取所有索引上的锁;
    执行删除(即使根据id删除)也需要获取其他索引的锁;
    执行update(即使where条件使用id)如果更新的有索引列,也需要获取上面的锁

    因为更新操作肯定会导致索引树的修改,如果不更改,会导致索引搜索时返回的数据和实际不一样;如果更改的话,肯定需要防止并发就需要加锁

    不知道理解的是否正确

    作者回复: 对的,要update和delete的时候,要“先读后写”,这个读就开始加锁了。

    insert的时候要有插入意向锁(就是会跟gap lock冲突的那个)

    2019-04-06
    2
  • 鸠翱
    老师我今天在回顾的时候又想到一个新问题,为什么RR级别下要以next-key lock作为加锁的基本单位呢?
    比如说案例5中的那个例子,我将其修改成select * from t where id > 10 and id < 15 for upadate,按照加锁规则,需要对(10,15]上锁,那么按照逻辑来看,15的那个行锁其实没必要的(难道是有必要的嘛?),既然next-key lock本质也是先加间隙锁再加行锁,为什么非要把那个行锁也加上,就是因为next-key lock是加锁基本单位嘛?

    作者回复: 代码实现上是这样子的。。

    2019-01-10
    2
  • 张三丰
    请教老师一个问题,
    A:update t set = 2 where h = 5
    B:update t set = 2 where K = 5

    h,k都不是索引,如果执行语句A不提交,此时B会执行吗?
    因为锁是针对索引的,对不同的索引加锁彼此都不会影响,如果没有索引呢?两条语句都锁全表吗?
    2019-10-17
    1
  • 木子
    老师,你好,回头看第二遍的时候,突然发现一个地方没明白:在案例1中 id=7等值查询时,id是主键,也满足唯一索引,为什么没有根据优化1 next-key lock 退化为行锁而是加了(5,10)的间隙锁?而案例三中 主键索引范围锁案例中 第一步 找到id=10的记录 这时候是根据优化2 主键索引上的等值查询 退化为行锁?同样是主键索引的等值查询为什么一个加了间隙锁一个退化成了行锁?麻烦老师看到之后回复一下,非常感谢
    2019-05-14
    1
  • null
    @坠落天使 的问题,案例六的 session A 不变,session B insert (c=5, id=50) 的记录被 block,可以借用“线段”的概念来帮助理解,下面是我自己的理解:
    session A 我们只关注 a (c=5, id=5) 和 b (c=10, id=10) 这两个点 (为了方便说明,其他的锁我们选择忽略)。
    在平面上,a 和 b两点连成的线段,就是我们的加锁范围。
    现在有一个点 f(c=5, id=6) 需要插入线段中,因为 a 和 f 的 c 值都是 5,但是 f 的 id 比 a 的 id 要大,所以在线段上表示,f 在 a 的右边,落入了线段内,即落入了锁区间,所以被 block 了。
    有一个点 e(c=5, id=4) 需要在平面展示,同理 e 点是在 a 点的左边,没落到锁区间,所以 insert 成功。
    2019-04-14
    1
收起评论
99+
返回
顶部