JasonLaw
V2EX  ›  数据库

InnoDB LOCK_MODE X,GAP,INSERT_INTENTION 到底是什么?

  •  
  •   JasonLaw · Jul 4, 2020 · 1594 views
    This topic created in 2165 days ago, the information mentioned may be changed or developed.

    首先执行以下代码,

    CREATE TABLE `t` (
      `id` int NOT NULL,
      PRIMARY KEY (`id`)
    );
    
    insert into t values (5), (10);
    
    -- session 1
    start transaction;
    select * from t where id > 8 for share;
    
    -- session 2
    start transaction;
    insert into t values (9);
    

    此时,select * from performance_schema.data_locks的输出为:

    +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+
    | ENGINE | ENGINE_LOCK_ID                        | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE              | LOCK_STATUS | LOCK_DATA              |
    +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+
    | INNODB | 140043377180872:1063:140043381460688  |                  2084 |        49 |       23 | test          | t           | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX                     | GRANTED     | NULL                   |
    | INNODB | 140043377180872:2:4:3:140043381458464 |                  2084 |        49 |       25 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381458464 | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 10                     |
    | INNODB | 140043377180024:1063:140043381454544  |       421518353890680 |        48 |       52 | test          | t           | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IS                     | GRANTED     | NULL                   |
    | INNODB | 140043377180024:2:4:1:140043381451552 |       421518353890680 |        48 |       52 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | supremum pseudo-record |
    | INNODB | 140043377180024:2:4:3:140043381451552 |       421518353890680 |        48 |       52 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | 10                     |
    +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+
    

    X,GAP,INSERT_INTENTION这个LOCK_MODE到底是什么呢?在文档没有找到相关的描述,Google 也没有搜索到相关内容。

    Supplement 1  ·  Jul 4, 2020

    Google搜索“InnoDB LOCK_MODE X,GAP,INSERT_INTENTION”时,的确没有找到相关的资料,但是搜索“InnoDB LOCK_MODE X,GAP,INSERT_INTENTION锁”时,让我找到了MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁 - 51CTO.COM,里面有讲到X,GAP,INSERT_INTENTION

    5 replies    2020-07-05 13:23:17 +08:00
    F281M6Dh8DXpD1g2
        1
    F281M6Dh8DXpD1g2  
       Jul 4, 2020
    关键词意向锁
    b+ 树存储并发症
    limuyan44
        2
    limuyan44  
       Jul 4, 2020
    2 条都说错了,官方文档有,google 也有,甚至只要把你的标题拿到 Google 搜一下也有。
    JasonLaw
        3
    JasonLaw  
    OP
       Jul 4, 2020
    @limuyan44 你说官方文档有,可以发一下相关的链接吗?我实在是找不到有官方文档说这个的。
    JasonLaw
        5
    JasonLaw  
    OP
       Jul 5, 2020
    @limuyan44 我不太明白你是怎么从 https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html 得知 X,GAP,INSERT_INTENTION 是什么类型的锁的,难道是从`trx id 8731 lock_mode X locks gap before rec insert intention waiting`得知?
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Solana   ·   2988 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 14:36 · PVG 22:36 · LAX 07:36 · JFK 10:36
    ♥ Do have faith in what you're doing.