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

MySQL InnoDB 是否在成功插入数据之后释放了插入意向锁?

  •  
  •   JasonLaw · 2020-07-10 23:08:03 +08:00 · 1507 次点击
    这是一个创建于 1357 天前的主题,其中的信息可能已经有所发展或是发生改变。

    执行以下代码后,

    create table t(id int primary key);
    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(6); -- is blocked
    

    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  |                  2132 |        49 |       56 | test          | t           | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX                     | GRANTED     | NULL                   |
    | INNODB | 140043377180872:2:4:3:140043381457776 |                  2132 |        49 |       56 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381457776 | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 10                     |
    | INNODB | 140043377180024:1063:140043381454544  |       421518353890680 |        48 |      105 | test          | t           | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IS                     | GRANTED     | NULL                   |
    | INNODB | 140043377180024:2:4:1:140043381451552 |       421518353890680 |        48 |      105 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | supremum pseudo-record |
    | INNODB | 140043377180024:2:4:3:140043381451552 |       421518353890680 |        48 |      105 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | 10                     |
    +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+
    

    我们可以看到 session 2 正在等待插入意向锁,因为这个插入意向锁跟 session 1 正在拥有的“下一键锁(5, 10]”冲突。这跟我预想的一样。但是如果我将 schedule 修改为

    create table t(id int primary key);
    insert into t values (5),(10);
    
    -- session 1
    start transaction;
    insert into t values(6);
    
    -- session 2
    start transaction;
    select * from t where id > 8 for share; -- is not blocked
    

    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 | 140043377180024:1063:140043381454544  |                  2147 |        48 |      125 | test          | t           | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IX        | GRANTED     | NULL                   |
    | INNODB | 140043377180872:1063:140043381460688  |       421518353891528 |        49 |       86 | test          | t           | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IS        | GRANTED     | NULL                   |
    | INNODB | 140043377180872:2:4:1:140043381457776 |       421518353891528 |        49 |       86 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381457776 | RECORD    | S         | GRANTED     | supremum pseudo-record |
    | INNODB | 140043377180872:2:4:3:140043381457776 |       421518353891528 |        49 |       86 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381457776 | RECORD    | S         | GRANTED     | 10                     |
    +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
    

    session 1 并没有拥有插入意向锁。那么,事务是在成功插入数据之后释放了插入意向锁吗?

    4 条回复    2020-07-11 13:02:41 +08:00
    izgnod
        1
    izgnod  
       2020-07-11 09:04:20 +08:00 via iPhone
    有主键索引和辅助索引的插入,插入前获取辅助索引的插入意向锁,插入后获取主键的记录锁。你这个事只有主键所以只需要记录锁。
    JasonLaw
        2
    JasonLaw  
    OP
       2020-07-11 10:01:09 +08:00
    @izgnod #1 我做了以下实验,事实并不是你所说的那样,并不是“插入前获取辅助索引的插入意向锁,插入后获取主键的记录锁”。而且问题中的两个 schedules 也能证明你所说的是不正确的。

    create table t2(id int primary key, value int, index ix_t2_value(value));
    insert into t2 values (5,10),(10,5);

    -- session 1
    start transaction;
    select * from t2 where id = 3 for share;

    -- session 2
    start transaction;
    insert into t2 values (2, 8); -- 等待获取 PRIMARY 索引上的插入意向锁(-∞ , 5)

    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 | 140311361761480:1063:140311280045776 | 2072 | 50 | 14 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL |
    | INNODB | 140311361761480:2:4:2:140311280042864 | 2072 | 50 | 14 | test | t2 | NULL | NULL | PRIMARY | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5 |
    | INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL |
    | INNODB | 140311361760632:2:4:2:140311280036640 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | PRIMARY | 140311280036640 | RECORD | S,GAP | GRANTED | 5 |
    +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+



    ---



    create table t2(id int primary key, value int, index ix_t2_value(value));
    insert into t2 values (5,10),(10,5);

    -- session 1
    start transaction;
    select * from t2 where value = 3 for share;

    -- session 2
    start transaction;
    insert into t2 values (8, 2); -- 等待获取 ix_t2_value 索引上的插入意向锁(-∞, (5, 10))

    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 | 140311361761480:1063:140311280045776 | 2073 | 50 | 18 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL |
    | INNODB | 140311361761480:2:5:3:140311280042864 | 2073 | 50 | 18 | test | t2 | NULL | NULL | ix_t2_value | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5, 10 |
    | INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL |
    | INNODB | 140311361760632:2:5:3:140311280036640 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | ix_t2_value | 140311280036640 | RECORD | S,GAP | GRANTED | 5, 10 |
    +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+------------------------+-------------+-----------+
    izgnod
        3
    izgnod  
       2020-07-11 12:57:37 +08:00 via iPhone
    @JasonLaw 之前遇到过类似问题,我回答的也有些模糊,晚点我试一下,希望帮到你
    JasonLaw
        4
    JasonLaw  
    OP
       2020-07-11 13:02:41 +08:00
    @izgnod #3 好的,期待你的回复。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1121 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 22:51 · PVG 06:51 · LAX 15:51 · JFK 18:51
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.