MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
42957 人已学习
课程目录
已完结 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讲
登录|注册

30 | 答疑文章(二):用动态的观点看加锁

林晓斌 2019-01-21
在第2021篇文章中,我和你介绍了 InnoDB 的间隙锁、next-key lock,以及加锁规则。在这两篇文章的评论区,出现了很多高质量的留言。我觉得通过分析这些问题,可以帮助你加深对加锁规则的理解。
所以,我就从中挑选了几个有代表性的问题,构成了今天这篇答疑文章的主题,即:用动态的观点看加锁。
为了方便你理解,我们再一起复习一下加锁规则。这个规则中,包含了两个“原则”、两个“优化”和一个“bug”:
原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。
原则 2:查找过程中访问到的对象才会加锁。
优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
接下来,我们的讨论还是基于下面这个表 t:
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

不等号条件里的等值查询

有同学对“等值查询”提出了疑问:等值查询和“遍历”有什么区别?为什么我们文章的例子里面,where 条件是不等号,这个过程里也有等值查询?
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(40)

  • Ryoma 置顶
    删除数据,导致锁扩大的描述:“因此,我们就知道了,由于 delete 操作把 id=10 这一行删掉了,原来的两个间隙 (5,10)、(10,15)变成了一个 (5,15)。”
    我觉得这个提到的(5, 10) 和 (10, 15)两个间隙会让人有点误解,实际上在删除之前间隙锁只有一个(10, 15),删除了数据之后,导致间隙锁左侧扩张成了5,间隙锁成为了(5, 15)。

    作者回复: 嗯 所以我这里特别小心地没有写“锁“这个字。

    间隙 (5,10)、(10,15)是客观存在的。

    你提得也很对,“锁”是执行过程中才加的,是一个动态的概念。
    这个问题也能够让大家更了解我们标题的意思,置顶了哈 👍

    2019-01-22
    26
  • 令狐少侠 置顶
    有个问题想确认下,在死锁日志里,lock_mode X waiting是间隙锁+行锁,lock_mode X locks rec but not gap这种加but not gap才是行锁?
    老师你后面能说下group by的原理吗,我看目录里面没有

    作者回复: 对, 好问题
    lock_mode X waiting表示next-key lock;
    lock_mode X locks rec but not gap是只有行锁;
    还有一种 “locks gap before rec”,就是只有间隙锁;

    2019-01-22
    13
  • IceGeek17
    老师,新年好,有几个问题:
    问题一:
    对于文中的第一个例子(不等号条件里的等值查询),当试图去找 “第一个id<12的值"的时候,用的还是从左往右的遍历(因为用到了优化2),也就是说,当去找第一个等值的时候(通过树搜索去定位记录的时候),即使order by desc,但用的还是向右遍历,当找到了第一个等值的时候(例子中的id=15),然后根据order by desc,再向左遍历。
    是不是这么理解?

    问题二:
    对于第21讲的思考题, select * from t where c>=15 and c<=20 order by c desc lock in share mode, 老师已经给出了答案,我这里再详细理解一下:
    先定位索引c上最右边c=20的行,所以第一个等值查询会扫描到c=25,然后通过优化2,next-key lock退化为间隙锁,则会加上间隙锁(20,25),紧接着再向左遍历,会加 next-key lock (15, 20], (10, 15], 因为要扫描到c=10才停下来,所以也会加next-key lock (5,10]
    理解的是否正确?

    问题三:
    对于上面的问题二的sql,在索引c上,把(10,25)这段区间锁上就应该是完备的了,理论上(5,10]这段区间是否锁上对结果应该没有影响呀。
    是不是说MySQL就是这么实现的,next-key lock前开后闭,因为扫到了c=10,所以会加next-key lock (5,10],这里MySQL的实现扩大了锁的区间范围,其实没有这个必要?
    另外,如果不加next-key lock (5,10],是不是这里c=10还是应该要锁的,如果不锁可能被删除?

    作者回复: 1. 对的

    2. 对的

    3. “因为扫到了c=10,所以会加next-key lock (5,10]”, 对的。
        第二个“如果”,实现上并不是这样的,所以没法回答😆

    2019-02-11
    3
    10
  • Jason_鹏
    最后一个update的例子,为没有加(0,5)的间隙呢?我理解应该是先拿c=5去b+树搜索,按照间隙索最右原则,应该会加(0,5]的间隙,然后c=5不满足大于5条件,根据优化2原则退化成(0,5)的间隙索,我是这样理解的

    作者回复: 根据c>5查到的第一个记录是c=10,因此不会加(0,5]这个next-key lock。

    你提醒得对,我应该多说明这句, 我加到文稿中啦👍

    2019-01-22
    6
  • Long
    感觉这篇文章以及前面加锁的文章,提升了自己的认知。还有,谢谢老师讲解了日志的对应细节……还愿了

    作者回复: 😆 👍

    2019-01-28
    5
  • 老师好:
            select * from t where c>=15 and c<=20 order by c desc for update;
            为什么这种c=20就是用来查数据的就不是向右遍历
            select * from t where c>=15 and c<=20 这种就是向右遍历
            怎么去判断合适是查找数据,何时又是遍历呢,是因为第一个有order by desc,然后反向向左遍历了吗?所以只需要[20,25)来判断已经是最后一个20就可以了是吧

    作者回复: 索引搜索就是 “找到第一个值,然后向左或向右遍历”,
    order by desc 就是要用最大的值来找第一个;
    order by就是要用做小的值来找第一个;

    “所以只需要[20,25)来判断已经是最后一个20就可以了是吧”,
    你描述的意思是对的,但是在MySQL里面不建议写这样的前闭后开区间哈,容易造成误解。
    可以描述为:
    “取第一个id=20后,向右遍历(25,25)这个间隙”^_^

    2019-01-22
    5
  • 长杰
    老师,之前讲这个例子时,select * from t where c>=15 and c<=20 order by c desc in share mode;
    最右边加的是 (20, 25)的间隙锁,
    而这个例子select * from t where id>10 and id<=15 for update中,最右边加的是(15,20]的next-key锁,
    这两个查询为何最后边一个加的gap锁,一个加的next-key锁,他们都是<=的等值范围查询,区别在哪里?

    作者回复: select * from t where c>=15 and c<=20 order by c desc in share mode;

    这个语句是根据 c=20 来查数据的,所以加锁(20,25]的时候,可以使用优化2;

    select * from t where id>10 and id<=15 for update;
    这里的id=20,是用“向右遍历”的方式得到的,没有优化,按照“以next-key lock”为加锁单位来执行

    2019-01-22
    2
    4
  • 长杰
    老师,还是select * from t where c>=15 and c<=20 order by c desc in share mode与select * from t where id>10 and id<=15 for update的问题,为何select * from t where id>10 and id<=15 for update不能解释为:根据id=15来查数据,加锁(15, 20]的时候,可以使用优化2,
    这个等值查询是根据什么规则来定的? 如果select * from t where id>10 and id<=15 for update加上order by id desc是否可以按照id=15等值查询,利用优化2?多谢指教。

    作者回复: 1. 代码实现上,传入的就是id>10里面的这个10
    2. 可以的,不过因为id是主键,而且id=15这一行存在,我觉得用优化1解释更好哦

    2019-01-22
    3
  • Justin
    想咨询一下 普通索引 如果索引中包括的元素都相同 在索引中顺序是怎么排解的呢 是按主键排列的吗 比如(name ,age ) 索引 name age都一样 那索引中会按照主键排序吗?

    作者回复: 会的

    2019-01-22
    3
  • hal
    非常谢谢老师,专栏质量非常高,祝老师身体健康万事如意,因为内容写的太好了……很激动👍👍👍👍👍

    作者回复: 🤝🤝🤝🤝🤝

    2019-06-01
    2
  • Lane
    第一个例子
    begin; select * from t where id>9 and id<12 order by id desc for update;
    为什么会有(0,5],不理解,但是测试确实insert into t values (3,3,3);会卡主
    2019-05-21
    2
    2
  • 唯她命
    老师,update语句 mysql在执行过程中 ,都是先拆成 插入 和 删除的吗?不是直接修改?

    作者回复: 修改索引值都会修改位置的😆

    2019-04-03
    2
  • 库淘淘
    对于问题 我理解是这样
    session 1:
    delete from t;
    begin; select * from t for update;
    session 2:
    insert into t values(1,1,1);发生等待
    show engine innodb status\G;
    .....
    ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 75 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 752090 lock_mode X insert intention waiting
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    其中申请插入意向锁与间隙锁 冲突,supremum这个能否理解为 间隙右边的那个记录

    作者回复: 发现了👍🏿

    2019-01-21
    2
  • 老杨同志
    先说结论:空表锁 (-supernum,supernum],老师提到过mysql的正无穷是supernum,在没有数据的情况下,next-key lock 应该是supernum前面的间隙加 supernum的行锁。但是前开后闭的区间,前面的值是什么我也不知道,就写了一个-supernum。
    稍微验证一下
    session 1)
    begin;
    select * from t where id>9 for update;
    session 2)
    begin;
    insert into t values(0,0,0),(5,5,5);
    (block)

    作者回复: 赞
    show engine innodb status 有惊喜😆

    2019-01-21
    2
  • 锋芒
    老师, 在读提交 隔离级别 也会有 间隙锁next-key lock 么 ?next-key lock 在mysql中怎么启动呢 ?
    2019-07-28
    1
  • 往事随风,顺其自然
    这么多加锁规则记不住

    作者回复: 练着练着,规则就忘记了,然后直接脑补执行流程,上结论

    2019-06-19
    1
  • 还一棵树
    为什么我在5.6的版本上 测试 文中最后2个死锁的案例都没有复现 (rr,rc都测试了,没有复现死锁)
    CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `c` int(11) DEFAULT NULL,
      `d` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `i_c` (`c`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    gaotuan[test]11:17:59>select * from t;
    +----+------+------+
    | id | c | d |
    +----+------+------+
    | 0 | 0 | 0 |
    | 1 | 1 | 1 |
    | 5 | 5 | 5 |
    | 10 | 10 | 10 |
    | 15 | 15 | 15 |
    | 20 | 20 | 20 |
    | 25 | 25 | 25 |
    +----+------+------+
    session1:
    gaotuan[test]11:02:04>begin;
    Query OK, 0 rows affected (0.00 sec)

    gaotuan[test]11:02:07>select c from t where c>5 lock in share mode;
    +------+
    | c |
    +------+
    | 10 |
    | 15 |
    | 20 |
    | 25 |
    +------+
    4 rows in set (0.00 sec)

    session2:
    gaotuan[test]11:05:08>begin;
    Query OK, 0 rows affected (0.00 sec)

    gaotuan[test]11:05:11>
    gaotuan[test]11:05:13>delete from t where id=10;
    Query OK, 1 row affected (0.00 sec)

    gaotuan[test]11:05:19>insert into t values(10,10,10);
    Query OK, 1 row affected (0.00 sec)

    gaotuan[test]11:05:30>rollback;
    2019-05-29
    1
  • w
    有个很奇怪的现象
    session a: begin; select * from cd where c <= 10 for update;
    session b: select * from cd where c = 15 for update;
    此时session b会被block
    session a稍作修改
    session a: begin; select * from cd where c = 10 for update;
    session b: select * from cd where c = 15 for update;
    此时session b不会被阻塞住。我理解即使c<=10这种也不应该阻塞c=15。
    我理解的加锁过程是等值查找到c=10这一行,向右遍历 next-key lock为(10,15],根据优化2退化为(10,15) 。但是实验结果令人费解
    2019-03-15
    3
    1
  • PengfeiWang
    老师,您好:
             对文中以下语句感到有困惑:
    我们说加锁单位是 next-key lock,都是前开后闭区,但是这里用到了优化 2,即索引上的等值查询,向右遍历的时候id=15不满足条件,所以 next-key lock 退化为了间隙锁 (10, 15)。
    SQL语句中条件中使用的是id字段(唯一索引),那么根据加锁规则这里不应该用的是优化 2,而是优化 1,因为优化1中明确指出给唯一索引加锁,从而优化 2的字面意思来理解,它适用于普通索引。不知道是不是我理解的不到位?

    作者回复: 主要是这里这一行不存在。。
    如果能够明确找到一行锁住的话,使用优化1就更准确些

    2019-01-22
    1
  • 长杰
    老师,select * from t where id>10 and id<=15 for update;这个语句持有的锁不应该是(5,10)(10,15](15,20)吗?

    作者回复: 不是哦,这里第一个id>10找到的是(10,15)这个gap,并没有加(5,10),
    还有根据股则里面的“bug”,id=20也会被锁的,所以应该是(10,15](15,20]

    2019-01-21
    1
收起评论
40
返回
顶部