MySQL普通索引加锁分析

最新推荐文章于 2023-01-21 13:04:02 发布

梦江河

最新推荐文章于 2023-01-21 13:04:02 发布

阅读量373

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_42002747/article/details/128621921

版权

Mysql 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

MySQL版本：8.0.29
测试表：
在这里插入图片描述
测试数据：

开始实验：
1）普通索引等值查询不存在的值
在这里插入图片描述

mysql>  SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328944544:1066:140346432670032
ENGINE_TRANSACTION_ID: 3927
            THREAD_ID: 68
             EVENT_ID: 123
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140346432670032
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328944544:5:5:2:140346462116384
ENGINE_TRANSACTION_ID: 3927
            THREAD_ID: 68
             EVENT_ID: 123
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140346462116384
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 99, 10
2 rows in set (0.00 sec)

加锁分析：
表意向写锁，
age索引间隙锁(89,99)

2)普通索引等值查询存在的值
在这里插入图片描述

mysql>  SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328944544:1066:140346432670032
ENGINE_TRANSACTION_ID: 3928
            THREAD_ID: 68
             EVENT_ID: 128
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140346432670032
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328944544:5:5:3:140346462116384
ENGINE_TRANSACTION_ID: 3928
            THREAD_ID: 68
             EVENT_ID: 128
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140346462116384
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 89, 11
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328944544:5:4:3:140346462116728
ENGINE_TRANSACTION_ID: 3928
            THREAD_ID: 68
             EVENT_ID: 128
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140346462116728
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 11
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328944544:5:5:2:140346462117072
ENGINE_TRANSACTION_ID: 3928
            THREAD_ID: 68
             EVENT_ID: 128
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140346462117072
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 99, 10
4 rows in set (0.00 sec)