V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
silenceeeee
V2EX  ›  MySQL

MySQL next-key lock 请教

  •  
  •   silenceeeee · 2019-08-27 18:28:10 +08:00 · 4977 次点击
    这是一个创建于 1675 天前的主题,其中的信息可能已经有所发展或是发生改变。

    MySQL 版本为 5.6.42 。有如下表:

    CREATE TABLE tab(
    	`id` INT PRIMARY KEY AUTO_INCREMENT,
    	`a` INT,
    	KEY `a`(`a`)
    );
    INSERT INTO `tt` (`id`, `a`)
    VALUES
    	(1, 1),
    	(2, 4),
    	(3, 7),
    	(4, 10),
    	(5, 11);
    
    

    SESSION 1:

    START TRANSACTION;
    SELECT * FROM tab WHERE a=7 FOR UPDATE;
    
    SESSION 2:
    INSERT INTO tab(a) VALUES(4); // blocking
    INSERT INTO tab(a) VALUES(5); // blocking
    INSERT INTO tab(a) VALUES(6); // blocking
    INSERT INTO tab(a) VALUES(7); // blocking
    INSERT INTO tab(a) VALUES(8); // blocking
    INSERT INTO tab(a) VALUES(9); // blocking
    INSERT INTO tab(a) VALUES(10); // success
    

    我大概知道这里用到了 next-key lock,但是我从官方了解到的是 next-key lock 是 gap lock 加上 record lock,而 gap lock 定义的范围是索引前的间隙,在这里即 (4,7) 这个区间。

    问题:为啥 INSERT INTO tab(a) VALUES(8); 这里的 8>7 在 (4,7) 区间之外也 block 了

    参考 MySQL 官方文档:https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-next-key-locks

    其中有一段关于 next-key lock 的描述:

    A next-key lock on an index record also affects the “ gap ” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. 
    
    第 1 条附言  ·  2019-08-27 21:32:44 +08:00

    补充一个新的问题:MySQL 啥时候会使用 gap 锁,啥时候使用 next-key 锁呢?

    23 条回复    2021-02-17 18:36:08 +08:00
    jimmzhou
        1
    jimmzhou  
       2019-08-27 21:12:33 +08:00 via iPhone
    锁住的区间是(4,10)
    silenceeeee
        2
    silenceeeee  
    OP
       2019-08-27 21:31:32 +08:00
    @jimmzhou 老哥,其实我从网上很多文章中已经了解到锁住的区间好像是(4,10) 但是你看我描述的最后一段引用,明明写的是 ```That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record```
    a gap lock on the gap preceding the index record,我就是想不明白这个。
    junnplus
        3
    junnplus  
       2019-08-27 21:40:02 +08:00   ❤️ 1
    查询辅助索引时,对存在的索引记录加 Record Lock,以及上一个键值和下一个键值开区间加 Gap Lock
    sudden
        4
    sudden  
       2019-08-28 00:23:50 +08:00
    哈哈,其实 mysql 文档没写错,next-key lock 就是 (4,7) ,这里 gap lock 是 7,至于为啥会锁住(7,10),因为 mysql 并不一定一次只会锁一个 next-key 啊!就比如你的例子,mysql 是锁了两个 next-key ((4,7) (7,10)) 加一个 gap ……
    sudden
        5
    sudden  
       2019-08-28 00:40:29 +08:00
    我上面的 gap lock 定义搞错了,重新表达一下哈,就是 锁了一个 next-key (4,7] 加一个 gap (7,10)
    1424659514
        6
    1424659514  
       2019-08-28 08:37:09 +08:00
    记录 7 之前的区间(4, 7] 是 next key lock, 之后的区间 [7,10) 是 gap lock.
    1424659514
        7
    1424659514  
       2019-08-28 08:42:08 +08:00
    "MySQL 啥时候会使用 gap 锁,啥时候使用 next-key 锁" 取决于 MySQL 还会不会继续往下查找满足条件的数据, MySQL 锁的记录或间隙的位置取决于它查询时判断到的最后一条记录.
    举个栗子, 比如楼主的这个查询语句 "SELECT * FROM tab WHERE a=7 FOR UPDATE;" where 条件后面加一个 limit 1, 那么就不会锁(7, 10) 这个间隙
    silenceeeee
        8
    silenceeeee  
    OP
       2019-08-28 09:02:48 +08:00
    @sudden
    首先非常感谢你的回复,能否告知一下你说的这个锁定 (4,10) 的规则在官网中哪里有讲细节呢?我在网上大概了解到的也是这样,但是在官网并没有找到相关细节。
    silenceeeee
        9
    silenceeeee  
    OP
       2019-08-28 09:04:56 +08:00
    @1424659514
    感谢回复,但是这些原理在哪里能找到对应的描述细节的文档呢?主要是问题总是会遇到的,我想了解下大家的学习方法。
    maoyusu
        10
    maoyusu  
       2019-08-28 09:10:55 +08:00 via Android
    很简单,我去公司了给你解释一下。
    silenceeeee
        11
    silenceeeee  
    OP
       2019-08-28 09:12:27 +08:00
    @maoyusu 好的 期待
    1424659514
        12
    1424659514  
       2019-08-28 09:38:16 +08:00
    @silenceeeee 跟朋友讨论的时候经过试验得出来的结果 =_=...
    maoyusu
        13
    maoyusu  
       2019-08-28 09:47:05 +08:00   ❤️ 2
    首先记住下面三个规则:
    1.当查询的索引仅含有唯一索引的时候,Next-Key Lock 会降级为 Record Lock。(联合唯一索引,每一个索引列都要查)
    2.InnoDB 还会对锁住的辅助索引加 Next-Key Lock,并且会给下一个键值加 Gap Lock。
    3.插入某记录时候,会检查插入记录的下一条记录是否被锁住了,如果锁住了,则不允许插入(阻塞)。

    然后来分析你的 SQL:
    SELECT * FROM tab WHERE a=7 FOR UPDATE;
    会加如下几种锁:
    1. id=3 的 Record Lock (规则 1)
    2. a 属于 (4,7] 的 Next-Key Lock (规则 2 )
    3. a 属于 (7,10) 的 Gap Lock (规则 2 )

    根据规则 3,INSERT INTO tab(a) VALUES(4); 会阻塞
    插入 5,6,7,8,9 被阻塞是因为 (4, 10)都上锁了。
    10 会成功,因为 10 不在锁区间内。
    silenceeeee
        14
    silenceeeee  
    OP
       2019-08-28 10:55:03 +08:00
    @1424659514 好的,老铁,网上都这么说,我试出来也是如此。
    silenceeeee
        15
    silenceeeee  
    OP
       2019-08-28 11:17:58 +08:00
    @maoyusu 规则 2 在官网的文档中哪里有体现呢?老哥。我很疑惑这个。
    simonlu9
        16
    simonlu9  
       2019-08-28 11:31:25 +08:00
    看看极客时间的 mysql 锁环节,会有你想要的答案
    silenceeeee
        17
    silenceeeee  
    OP
       2019-08-28 11:32:14 +08:00
    @simonlu9 好的,谢谢。
    titan2007
        18
    titan2007  
       2019-08-28 18:26:57 +08:00
    你得看目前的隔离级别是什么 next-key locking 只在 repeatable read 情况下才有
    cs8814336
        19
    cs8814336  
       2019-09-20 15:30:07 +08:00
    silenceeeee
        20
    silenceeeee  
    OP
       2019-09-20 17:35:07 +08:00
    @cs8814336 还是没有人回答一个问题:官方说的 next-key lock  是  record-lock 加上  record  前面的 gap lock .可是实际测试的时候 record 后面的 gap 也被锁定了! 为什么会这样?
    louettagfh
        21
    louettagfh  
       2021-01-19 11:11:14 +08:00
    @silenceeeee 你理解的没有错 next-key lock 是 record lock 加 record 前面的 GAP lock. 但为什么你这个场景后面的 GAP 也锁住了呢?

    因为表中的 `a` 字段不是二级唯一索引, 所以你执行 session 1 的时候会有 3 个锁:

    1. 索引 `a` 上的 '7' 的 next-key lock.
    2. 主键索引上 '3' 的 record lock.
    3. 索引 `a` 上的 '10' 的 GAP lock. 即 [7, 10)

    InnoDB 的加锁逻辑是遍历到下一个 record 然后添加 GAP LOCK
    RedBlackTree
        22
    RedBlackTree  
       2021-02-02 17:26:04 +08:00 via iPhone
    @simonlu9 你好,课程名什么呢?是丁奇的 mysql 实战吗?
    RedBlackTree
        23
    RedBlackTree  
       2021-02-17 18:36:08 +08:00
    @silenceeeee 主键的话锁住的是前一区间的 gap lock 加上 record lock ;普通索引的话除了这两个锁,还会锁住后一个区间的 gap lock 。你可以试试。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3229 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 11:40 · PVG 19:40 · LAX 04:40 · JFK 07:40
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.