V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
JasonLaw
V2EX  ›  数据库

为什么插入时索引记录独占锁只有在冲突的时候才存在?

  •  
  •   JasonLaw · 2020-07-04 13:02:50 +08:00 · 757 次点击
    这是一个创建于 1602 天前的主题,其中的信息可能已经有所发展或是发生改变。

    执行以下代码

    create table t(id int primary key);
    insert into t values (5),(10);
    
    -- session 1
    start transaction;
    insert into t values (8);
    

    此时,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 | 139956701343096:1063:139956609693392 |                  2070 |        48 |       18 | test          | t           | NULL           | NULL              | NULL       |       139956609693392 | TABLE     | IX        | GRANTED     | NULL      |
    +--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
    

    session 1 的那个事务只有一个表级别的独占意向锁。

    但是执行以下代码之后,

    -- session 2
    start transaction;
    select * from t where id = 8 for share;
    

    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 | 139956701343096:1063:139956609693392  |                  2070 |        48 |       18 | test          | t           | NULL           | NULL              | NULL       |       139956609693392 | TABLE     | IX            | GRANTED     | NULL      |
    | INNODB | 139956701343096:2:4:4:139956609690400 |                  2070 |        49 |       14 | test          | t           | NULL           | NULL              | PRIMARY    |       139956609690400 | RECORD    | X,REC_NOT_GAP | GRANTED     | 8         |
    | INNODB | 139956701343944:1063:139956609699536  |       421431678054600 |        49 |       14 | test          | t           | NULL           | NULL              | NULL       |       139956609699536 | TABLE     | IS            | GRANTED     | NULL      |
    | INNODB | 139956701343944:2:4:4:139956609696624 |       421431678054600 |        49 |       14 | test          | t           | NULL           | NULL              | PRIMARY    |       139956609696624 | RECORD    | S,REC_NOT_GAP | WAITING     | 8         |
    +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
    

    此时,显示 session 1 的那个事务持有 id 为 8 的那个索引记录的独占锁。但是为什么在第一次输出时没有显示呢?

    第 1 条附言  ·  2020-07-04 23:20:08 +08:00

    MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁 - 51CTO.COM中的“五、performance_schema.data_locks中能看到全部的锁吗?”说明了performance_schema.data_locks并未显示全部的锁。

    所以只是没有显示出来而已,但是事实上是存在的。

    目前尚无回复
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2760 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 19ms · UTC 12:09 · PVG 20:09 · LAX 04:09 · JFK 07:09
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.