MySQL~锁内存结构与监控策略

Salute-Y

已于 2022-05-13 16:39:37 修改

阅读量631

点赞数

分类专栏： MySQL 文章标签： mysql java 数据库 database 数据结构

于 2022-05-13 16:04:34 首次发布

本文链接：https://blog.youkuaiyun.com/Merciful_Lion/article/details/124752019

版权

MySQL 专栏收录该内容

21 篇文章

订阅专栏

本文深入探讨了MySQL中的锁结构，包括事务信息、索引信息和锁的模式、类型等细节，并介绍了如何通过Innodb_row_lock状态变量监控行锁竞争。同时，通过information_schema库的Innodb_TRX、LOCKS和Lock_WAITS表，展示了如何查看和分析事务持有的锁以及锁等待情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

锁结构

对一条数据加锁的本质就是创建一个锁结构与其关联

如果一个事务要获取10000条记录的锁，不会生成10000个锁结构，而是会将相同类型记录的锁放到一个锁结构中，只要符合以下条件：

加锁记录在同一个事务
加锁记录在同一个页面中
加锁类型和等待状态相同

具体结构
在这里插入图片描述
每一层是干啥的

锁所在事务信息：指针，指向事务更多信息
索引信息：指针，加锁记录属于哪个索引
表锁/行锁信息：
如果是表锁，记录对哪个表
如果是行锁，记录Spring ID表空间、PageNumber 所在页号、n_bits哪个比特位
type_mode:32位的二进制数，划分成以下三个部分
锁的模式：表示锁是什么锁，S锁还是X锁，IS还是IX锁，通过十进制的0,1，2,3,4表示
锁的类型：表锁还是行锁
记录锁的类型：进一步划分，临键锁还是gap锁？记录锁？
这个32位数的第9位表示is_waiting，数值为1，表示为true。数值为0，表示为false；
其他信息：放了一些哈希表和链表

锁的监控

最常用的就是状态变量Innodb_row_lock，通过查看状态变量，我们可以分析行锁的竞争情况.

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.04 sec)

MySQL中事务和锁的信息都记录在了information_schema库中,最常用的三张表如下
Innodb_TRX，lOCKS，Lock_WAITS

查看正在被阻塞的SQL语句
mysql> select *from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 93673
                 trx_state: RUNNING
               trx_started: 2022-05-13 15:44:20
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 74
                 trx_query: select *from information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 6
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.04 sec)


可以查看阻碍事务的锁和该事务持有的锁

mysql> select*from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2879223041216:1158:2879228480536
ENGINE_TRANSACTION_ID: 93673
            THREAD_ID: 157
             EVENT_ID: 20
        OBJECT_SCHEMA: db2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2879228480536
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2879223041216:97:4:1:2879212424216
ENGINE_TRANSACTION_ID: 93673
            THREAD_ID: 157
             EVENT_ID: 20
        OBJECT_SCHEMA: db2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2879223041216:97:4:2:2879212424216
ENGINE_TRANSACTION_ID: 93673
            THREAD_ID: 157
             EVENT_ID: 20
        OBJECT_SCHEMA: db2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2879223041216:97:4:3:2879212424216
ENGINE_TRANSACTION_ID: 93673
            THREAD_ID: 157
             EVENT_ID: 20
        OBJECT_SCHEMA: db2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2879223041216:97:4:4:2879212424216
ENGINE_TRANSACTION_ID: 93673
            THREAD_ID: 157
             EVENT_ID: 20
        OBJECT_SCHEMA: db2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 8
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2879223041216:97:4:5:2879212424216
ENGINE_TRANSACTION_ID: 93673
            THREAD_ID: 157
             EVENT_ID: 20
        OBJECT_SCHEMA: db2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2879223041216:97:4:6:2879212424216
ENGINE_TRANSACTION_ID: 93673
            THREAD_ID: 157
             EVENT_ID: 20
        OBJECT_SCHEMA: db2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20
7 rows in set (0.00 sec)





mysql> select*from performance_schema.data_lock_waits\G
Empty set (0.00 sec)