› 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 · Aug 27, 2019 · 6513 views
    This topic created in 2590 days ago, the information mentioned may be changed or developed.

    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. 
    
    Supplement 1  ·  Aug 27, 2019

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

    23 replies  •  2021-02-17 18:36:08 +08:00
    jimmzhou
        1
    jimmzhou  
       Aug 27, 2019 via iPhone
    锁住的区间是(4,10)
    silenceeeee
        2
    silenceeeee  
    OP
       Aug 27, 2019
    @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  
       Aug 27, 2019   ❤️ 1
    查询辅助索引时,对存在的索引记录加 Record Lock,以及上一个键值和下一个键值开区间加 Gap Lock
    sudden
        4
    sudden  
       Aug 28, 2019
    哈哈,其实 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  
       Aug 28, 2019
    我上面的 gap lock 定义搞错了,重新表达一下哈,就是 锁了一个 next-key (4,7] 加一个 gap (7,10)
    1424659514
        6
    1424659514  
       Aug 28, 2019
    记录 7 之前的区间(4, 7] 是 next key lock, 之后的区间 [7,10) 是 gap lock.
    1424659514
        7
    1424659514  
       Aug 28, 2019
    "MySQL 啥时候会使用 gap 锁,啥时候使用 next-key 锁" 取决于 MySQL 还会不会继续往下查找满足条件的数据, MySQL 锁的记录或间隙的位置取决于它查询时判断到的最后一条记录.
    举个栗子, 比如楼主的这个查询语句 "SELECT * FROM tab WHERE a=7 FOR UPDATE;" where 条件后面加一个 limit 1, 那么就不会锁(7, 10) 这个间隙
    silenceeeee
        8
    silenceeeee  
    OP
       Aug 28, 2019
    @sudden
    首先非常感谢你的回复,能否告知一下你说的这个锁定 (4,10) 的规则在官网中哪里有讲细节呢?我在网上大概了解到的也是这样,但是在官网并没有找到相关细节。
    silenceeeee
        9
    silenceeeee  
    OP
       Aug 28, 2019
    @1424659514
    感谢回复,但是这些原理在哪里能找到对应的描述细节的文档呢?主要是问题总是会遇到的,我想了解下大家的学习方法。
    maoyusu
        10
    maoyusu  
       Aug 28, 2019 via Android
    很简单,我去公司了给你解释一下。
    silenceeeee
        11
    silenceeeee  
    OP
       Aug 28, 2019
    @maoyusu 好的 期待
    1424659514
        12
    1424659514  
       Aug 28, 2019
    @silenceeeee 跟朋友讨论的时候经过试验得出来的结果 =_=...
    maoyusu
        13
    maoyusu  
       Aug 28, 2019   ❤️ 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
       Aug 28, 2019
    @1424659514 好的,老铁,网上都这么说,我试出来也是如此。
    silenceeeee
        15
    silenceeeee  
    OP
       Aug 28, 2019
    @maoyusu 规则 2 在官网的文档中哪里有体现呢?老哥。我很疑惑这个。
    simonlu9
        16
    simonlu9  
       Aug 28, 2019
    看看极客时间的 mysql 锁环节,会有你想要的答案
    silenceeeee
        17
    silenceeeee  
    OP
       Aug 28, 2019
    @simonlu9 好的,谢谢。
    titan2007
        18
    titan2007  
       Aug 28, 2019
    你得看目前的隔离级别是什么 next-key locking 只在 repeatable read 情况下才有
    cs8814336
        19
    cs8814336  
       Sep 20, 2019
    silenceeeee
        20
    silenceeeee  
    OP
       Sep 20, 2019
    @cs8814336 还是没有人回答一个问题:官方说的 next-key lock  是  record-lock 加上  record  前面的 gap lock .可是实际测试的时候 record 后面的 gap 也被锁定了! 为什么会这样?
    louettagfh
        21
    louettagfh  
       Jan 19, 2021
    @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  
       Feb 2, 2021 via iPhone
    @simonlu9 你好,课程名什么呢?是丁奇的 mysql 实战吗?
    RedBlackTree
        23
    RedBlackTree  
       Feb 17, 2021
    @silenceeeee 主键的话锁住的是前一区间的 gap lock 加上 record lock ;普通索引的话除了这两个锁,还会锁住后一个区间的 gap lock 。你可以试试。
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Privacy   ·   Solana   ·   3301 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 39ms · UTC 04:20 · PVG 12:20 · LAX 21:20 · JFK 00:20
    ♥ Do have faith in what you're doing.