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
jdz
V2EX  ›  MySQL

innodb 存储引擎。select * from test_table where id = 3 for update; 其中 id 为唯一索引,且不存在 id = 3 这一行, 此时为何要使用间隙锁呢?

  •  
  •   jdz · 2020-03-05 21:48:58 +08:00 · 4245 次点击
    这是一个创建于 1725 天前的主题,其中的信息可能已经有所发展或是发生改变。
    25 条回复    2022-03-24 17:52:53 +08:00
    RedisMasterNode
        1
    RedisMasterNode  
       2020-03-05 22:17:39 +08:00
    id 是唯一索引的时候没有使用 next_key locking,应该是 record lock
    jdz
        2
    jdz  
    OP
       2020-03-06 09:14:50 +08:00 via Android
    @RedisMasterNode 你可以试一下,此时插入 2 也会失败
    simonlu9
        3
    simonlu9  
       2020-03-06 09:18:32 +08:00
    很简单,正常会锁,因为在可重复读的情况下,在开启事务中,select * from test_table where id = 3 for update 这一句第一次查询是没有数据,第二次查询我也要没有数据,那么怎么保证,只能开间歇锁范围,范围是多少,决定在于你 id=3 这个记录的索引范围,假如 id 没有索引,肯定会锁全表
    jdz
        4
    jdz  
    OP
       2020-03-06 09:30:03 +08:00 via Android
    @simonlu9
    为什么不是只是锁 3 那一行呢,而是要锁一个范围呢?
    hhyvs111
        5
    hhyvs111  
       2020-03-06 09:33:18 +08:00
    第三行不存在,所以要锁一定的范围。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由 MySQL Server 层进行过滤,因此也就把所有的记录都锁上了。
    RedisMasterNode
        6
    RedisMasterNode  
       2020-03-06 09:42:07 +08:00 via Android
    @jdz 你的描述很模糊,id=3 这行存在?
    RedisMasterNode
        7
    RedisMasterNode  
       2020-03-06 09:43:51 +08:00 via Android
    @RedisMasterNode 抱歉是我眼睛很模糊(滑跪!)如果按照这个描述的话不存在 3 这行当然会进行范围锁 orz
    simonlu9
        8
    simonlu9  
       2020-03-06 09:56:33 +08:00
    @jdz 3 这一行根本不存在,在保持可重复读的情况下,是不允许其他事务插入 3 这一行的操作,由于你存在 3 这一行,所以要锁范围,至于范围是多少,是采取于你的记录
    simonlu9
        9
    simonlu9  
       2020-03-06 09:56:55 +08:00
    @simonlu9 不存在 3 这一行
    sanggao
        10
    sanggao  
       2020-03-06 10:01:22 +08:00
    怎么证明使用了间隙所
    zifangsky
        11
    zifangsky  
       2020-03-06 10:33:39 +08:00
    Next-Key 锁:当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB 会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙( GAP )”,InnoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的 Next-Key 锁。

    举例来说,假如 emp 表中只有 101 条记录,其 empid 的值分别是 1、2、…、100、101,下面的 SQL:
    Select * from emp where empid > 100 for update;

    是一个范围条件的检索,InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁,也会对 empid 大于 101 (这些记录并不存在)的“间隙”加锁。

    同样,InnoDB 除了通过范围条件加锁时使用 Next-Key 锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB 也会使用 Next-Key 锁!

    ——《深入浅出 MySQL 》

    至于为什么要设置这种这种机制,坐等其他人回答
    sujin190
        12
    sujin190  
       2020-03-06 10:53:51 +08:00
    既然锁是和记录关联的,你都说了这行不存在,那如何给这一行加锁呢,虽然我房子还没建但是你先给我上个锁的意思??
    BBCCBB
        13
    BBCCBB  
       2020-03-06 11:06:48 +08:00
    就是因为没有 id=3, 才会把比 3 小的第一个 id 和比 3 大的第 1 个 id 这个区间锁起来, 防止插入 id=3 的.
    fancy111
        14
    fancy111  
       2020-03-06 11:14:04 +08:00
    楼上正确
    gaius
        15
    gaius  
       2020-03-06 11:45:09 +08:00 via Android
    id 是主键,因为你 for update 了,所以用的 gap,否则是行。
    gaius
        16
    gaius  
       2020-03-06 11:52:33 +08:00 via Android
    我错了..上面我瞎说的 原来是不存在
    jdz
        17
    jdz  
    OP
       2020-03-06 12:58:28 +08:00 via Android
    @BBCCBB 为什么不是仅仅锁定 3, 而是要锁定一个区间呢
    chibupang
        18
    chibupang  
       2020-03-06 13:11:20 +08:00 via iPhone
    因为数据库引擎无法判断你当前的事务需要对 id=3 的字段做什么操作。即使这个记录不存在,但是当前事务的后续操作可能是插入一条 id=3 的记录,如果是这样,必然是介于 id=2 到 id=4 之间,如果不范围锁定的话会出现什么情况?可能另一事务也正在插入一条 id=3 的记录,这样就会出现两条不同的 id=3 记录。
    lhx2008
        19
    lhx2008  
       2020-03-06 14:06:57 +08:00 via Android
    楼主问的是为什么要多锁,那是因为锁只能挂在有数据的地方
    如果现在一个只有 1 4,那就是 1-4 之间间隙被锁,无法插任何数
    DonaldY
        20
    DonaldY  
       2020-03-06 14:15:49 +08:00
    @jdz 你 id = 2 的记录原本就没有吧,所以插入会失败。
    验证了下,当`SELECT FOR UPDATE` id 不存在的,会锁住其他未插入。
    BBCCBB
        21
    BBCCBB  
       2020-03-06 14:36:21 +08:00
    @jdz 因为他要锁索引, 而 id=3 的索引目前不存在, 不能直接锁 id=3 的索引, 所以他就锁区间来解决 这个问题...
    Aresxue
        22
    Aresxue  
       2020-03-07 14:26:17 +08:00
    间隙锁是为防止幻读,因为 id=3 不存在所以要锁定区间,如果存在就是行锁了
    jsdi
        23
    jsdi  
       2022-03-13 15:07:54 +08:00
    我的理解是因为这是当前读,当前读不仅要对当前记录加行锁,还要对邻近记录加间隙锁防止幻读

    当前读:读取最新版本数据,除了 for update ,还有 insert 、update 、delete 都是当前读
    cocong
        24
    cocong  
       2022-03-24 17:50:42 +08:00
    因为锁是加在索引上的,但是 id=3 这条记录不存在,当然也就不存在这个索引了,那你说怎么加?只能让未来可能插入索引的那个位置 的那个间隙的两边的索引来帮忙了。这样其它事务想新增 id=3 的记录时,就得先问问旁边那两位索引兄弟:“朋友,这里没人坐吧?”,答:“有人做了”。这不,就添加不了了。
    cocong
        25
    cocong  
       2022-03-24 17:52:53 +08:00
    前面的问法不合适,感觉这么问更准确些:“朋友,我能过来挤一挤吗?”,答:“不好意思,我俩被锁住了!”
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5920 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 06:08 · PVG 14:08 · LAX 22:08 · JFK 01:08
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.