首页   注册   登录
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
V2EX  ›  MySQL

MySQL next-key lock 请教

  •  
  •   silenceeeee · 76 天前 · 2795 次点击
    这是一个创建于 76 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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 条附言  ·  76 天前

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

    20 回复  |  直到 2019-09-20 17:35:07 +08:00
        1
    jimmzhou   76 天前 via iPhone
    锁住的区间是(4,10)
        2
    silenceeeee   76 天前
    @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,我就是想不明白这个。
        3
    junnplus   76 天前   ♥ 1
    查询辅助索引时,对存在的索引记录加 Record Lock,以及上一个键值和下一个键值开区间加 Gap Lock
        4
    sudden   76 天前
    哈哈,其实 mysql 文档没写错,next-key lock 就是 (4,7) ,这里 gap lock 是 7,至于为啥会锁住(7,10),因为 mysql 并不一定一次只会锁一个 next-key 啊!就比如你的例子,mysql 是锁了两个 next-key ((4,7) (7,10)) 加一个 gap ……
        5
    sudden   76 天前
    我上面的 gap lock 定义搞错了,重新表达一下哈,就是 锁了一个 next-key (4,7] 加一个 gap (7,10)
        6
    1424659514   75 天前
    记录 7 之前的区间(4, 7] 是 next key lock, 之后的区间 [7,10) 是 gap lock.
        7
    1424659514   75 天前
    "MySQL 啥时候会使用 gap 锁,啥时候使用 next-key 锁" 取决于 MySQL 还会不会继续往下查找满足条件的数据, MySQL 锁的记录或间隙的位置取决于它查询时判断到的最后一条记录.
    举个栗子, 比如楼主的这个查询语句 "SELECT * FROM tab WHERE a=7 FOR UPDATE;" where 条件后面加一个 limit 1, 那么就不会锁(7, 10) 这个间隙
        8
    silenceeeee   75 天前
    @sudden
    首先非常感谢你的回复,能否告知一下你说的这个锁定 (4,10) 的规则在官网中哪里有讲细节呢?我在网上大概了解到的也是这样,但是在官网并没有找到相关细节。
        9
    silenceeeee   75 天前
    @1424659514
    感谢回复,但是这些原理在哪里能找到对应的描述细节的文档呢?主要是问题总是会遇到的,我想了解下大家的学习方法。
        10
    maoyusu   75 天前 via Android
    很简单,我去公司了给你解释一下。
        11
    silenceeeee   75 天前
    @maoyusu 好的 期待
        12
    1424659514   75 天前
    @silenceeeee 跟朋友讨论的时候经过试验得出来的结果 =_=...
        13
    maoyusu   75 天前   ♥ 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 不在锁区间内。
        14
    silenceeeee   75 天前
    @1424659514 好的,老铁,网上都这么说,我试出来也是如此。
        15
    silenceeeee   75 天前
    @maoyusu 规则 2 在官网的文档中哪里有体现呢?老哥。我很疑惑这个。
        16
    simonlu9   75 天前
    看看极客时间的 mysql 锁环节,会有你想要的答案
        17
    silenceeeee   75 天前
    @simonlu9 好的,谢谢。
        18
    titan2007   75 天前
    你得看目前的隔离级别是什么 next-key locking 只在 repeatable read 情况下才有
        20
    silenceeeee   52 天前
    @cs8814336 还是没有人回答一个问题:官方说的 next-key lock  是  record-lock 加上  record  前面的 gap lock .可是实际测试的时候 record 后面的 gap 也被锁定了! 为什么会这样?
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   934 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 24ms · UTC 21:55 · PVG 05:55 · LAX 13:55 · JFK 16:55
    ♥ Do have faith in what you're doing.