Innodb中怎麼檢視鎖資訊

玉樹臨楓發表於2021-08-22

一、前言

上一篇說了下innodb中鎖的大概意思，這篇說說怎麼檢視加的哪些鎖。不然後續出現死鎖或者鎖等待都不知道為什麼。

二、底層基礎表資訊

在學會如何檢視有哪些鎖資訊時，需要了解一些基礎表資訊，這些能幫助我們快速排查。

從前兩篇文章可以瞭解到innodb中的鎖是在事務內執行的，所以我們先了解下底層的事務表看看從中可以看出哪些內容。

2.1 information_schema.INNODB_TRX

底層有兩個databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.01 sec)

可以選擇information_schema 檢視下面是否有事務相關的表。

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables like '%tr%';
+-------------------------------------+
| Tables_in_information_schema (%TR%) |
+-------------------------------------+
| CHECK_CONSTRAINTS                   |
| INNODB_METRICS                      |
| INNODB_TRX                          |
| OPTIMIZER_TRACE                     |
| REFERENTIAL_CONSTRAINTS             |
| ST_GEOMETRY_COLUMNS                 |
| TABLE_CONSTRAINTS                   |
| TRIGGERS                            |
+-------------------------------------+
8 rows in set (0.00 sec)

可見存在事務表INNODB_TRX，然後看看其表結構，然後針對每個欄位的解釋加進去

mysql> show create table INNODB_TRX;
....

 INNODB_TRX | CREATE TEMPORARY TABLE `INNODB_TRX` (
   # 事務ID
  `trx_id` varchar(18) NOT NULL DEFAULT '',  
  
  # 事務狀態， 允許值是 RUNNING，LOCK WAIT， ROLLING BACK，和 COMMITTING。
  `trx_state` varchar(13) NOT NULL DEFAULT '', 
  
  # 事務開始時間
  `trx_started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  
  # 事務當前等待的鎖的ID，如果TRX_STATE是LOCK WAIT；否則NULL。
  `trx_requested_lock_id` varchar(105) DEFAULT NULL, 
  
   # 事務開始等待鎖的時間
  `trx_wait_started` datetime DEFAULT NULL,
  
  # 事務權重， 反映（但不一定是準確計數）更改的行數和事務鎖定的行數。為了解決死鎖， InnoDB選擇權重最小的事務作為“受害者”進行回滾。無論更改和鎖定行的數量如何，更改非事務表的事務都被認為比其他事務更重。
  `trx_weight` bigint(21) unsigned NOT NULL DEFAULT '0', 
  
  # MySQL 執行緒 ID。 這個id很重要，如果發現某個事務一直在等待無法結束的話，可以通過這個ID kill掉。
  `trx_mysql_thread_id` bigint(21) unsigned NOT NULL DEFAULT '0', 
  
  # 事務正在執行的 SQL 語句。
  `trx_query` varchar(1024) DEFAULT NULL,
  
  # 交易的當前操作，如果有的話；否則 NULL。
  `trx_operation_state` varchar(64) DEFAULT NULL,
  
  # InnoDB處理此事務的當前 SQL 語句時使用 的表數。
  `trx_tables_in_use` bigint(21) unsigned NOT NULL DEFAULT '0',
  
  # InnoDB當前 SQL 語句具有行鎖 的表數。（因為這些是行鎖，而不是表鎖，儘管某些行被鎖定，但通常仍可以由多個事務讀取和寫入表。）
  `trx_tables_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  
  # 事務保留的鎖數。
  `trx_lock_structs` bigint(21) unsigned NOT NULL DEFAULT '0',
  
  # 此事務的鎖結構在記憶體中佔用的總大小。
  `trx_lock_memory_bytes` bigint(21) unsigned NOT NULL DEFAULT '0',
  
  # 此事務鎖定的大致數量或行數。該值可能包括物理上存在但對事務不可見的刪除標記行。
  `trx_rows_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  
  # 此事務中修改和插入的行數。
  `trx_rows_modified` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_concurrency_tickets` bigint(21) unsigned NOT NULL DEFAULT '0',
  
  # 當前事務的隔離級別。
  `trx_isolation_level` varchar(16) NOT NULL DEFAULT '',
  `trx_unique_checks` int(1) NOT NULL DEFAULT '0',
  `trx_foreign_key_checks` int(1) NOT NULL DEFAULT '0',
  `trx_last_foreign_key_error` varchar(256) DEFAULT NULL,
  `trx_adaptive_hash_latched` int(1) NOT NULL DEFAULT '0',
  `trx_adaptive_hash_timeout` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_is_read_only` int(1) NOT NULL DEFAULT '0',
  `trx_autocommit_non_locking` int(1) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

上述已經針對重要欄位進行了註釋說明，該表主要是記錄事務中的一些資訊，非常有用，其中就會記錄等錢等待鎖的ID。

詳細請看官方文件：https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-trx-table.html

其中以下幾個欄位需要特別留意下，

TRX_ID                  事務ID， 
TRX_REQUESTED_LOCK_ID   事務當前等待的鎖的ID。 如果當前事務阻塞就可以看出之前的鎖
TRX_MYSQL_THREAD_ID     MySQL 執行緒 ID

2.2 performance_schema.data_locks

上述事務表中有記錄當前等待鎖的ID，那麼這個id來源哪呢？
可以在information_schema performance_schema中搜尋show tables like '%lock%';, 後面發現在performance_schema下

mysql> show tables like '%lock%';
+---------------------------------------+
| Tables_in_performance_schema (%lock%) |
+---------------------------------------+
| data_lock_waits                       |
| data_locks                            |
| metadata_locks                        |
| rwlock_instances                      |
| table_lock_waits_summary_by_table     |
+---------------------------------------+
5 rows in set (0.00 sec)

先看看data_locks的表結構：

CREATE TABLE `data_locks` (
  # 持有或請求鎖的儲存引擎。
  `ENGINE` varchar(32) NOT NULL,
  
  # 儲存引擎持有或請求的鎖的 ID。( ENGINE_LOCK_ID, ENGINE) 值的元組是唯一的。
  # information_schema.INNODB_TRX.trx_requested_lock_id 就來源於這
  `ENGINE_LOCK_ID` varchar(128) NOT NULL,
  
  # 請求鎖定的事務的儲存引擎內部 ID 
  # 來源information_schema.INNODB_TRX.TRX_ID
  `ENGINE_TRANSACTION_ID` bigint(20) unsigned DEFAULT NULL,
  
  # 建立鎖的會話的執行緒 ID
  `THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  
  `EVENT_ID` bigint(20) unsigned DEFAULT NULL,
  `OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  `OBJECT_NAME` varchar(64) DEFAULT NULL,
  `PARTITION_NAME` varchar(64) DEFAULT NULL,
  `SUBPARTITION_NAME` varchar(64) DEFAULT NULL,
  
  # 鎖定索引的名稱
  `INDEX_NAME` varchar(64) DEFAULT NULL,
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  
  # 鎖的型別。該值取決於儲存引擎。對於 InnoDB，允許的值為 RECORD行級鎖和 TABLE表級鎖。
  `LOCK_TYPE` varchar(32) NOT NULL,
  
  # 如何請求鎖定。
  # 該值取決於儲存引擎。為 InnoDB，允許值是 S[,GAP]，X[,GAP]， IS[,GAP]，IX[,GAP]， AUTO_INC，和 UNKNOWN。AUTO_INC和UNKNOWN 指示間隙鎖定以外的鎖定模式 （如果存在）
  `LOCK_MODE` varchar(32) NOT NULL,
  
  # 鎖定請求的狀態。
  # 該值取決於儲存引擎。對於 InnoDB，允許的值為 GRANTED（鎖定已持有）和 WAITING（正在等待鎖定）。
  `LOCK_STATUS` varchar(32) NOT NULL,
  `LOCK_DATA` varchar(8192) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`ENGINE_LOCK_ID`,`ENGINE`),
  KEY `ENGINE_TRANSACTION_ID` (`ENGINE_TRANSACTION_ID`,`ENGINE`),
  KEY `THREAD_ID` (`THREAD_ID`,`EVENT_ID`),
  KEY `OBJECT_SCHEMA` (`OBJECT_SCHEMA`,`OBJECT_NAME`,`PARTITION_NAME`,`SUBPARTITION_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

詳細引數解釋請參考： https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html

從上面可以知道當前事務如果持有鎖的就看出它持有的什麼型別的鎖、鎖狀態。

三、實踐得真知

1、開始一個事務1，對某條記錄加排他鎖：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id = 3 for update;
+----+------+
| id | name |
+----+------+
|  3 | 3    |
+----+------+
1 row in set (0.00 sec)

然後根據當前執行緒id查詢事務資訊:

mysql> select * from information_schema.INNODB_TRX where TRX_MYSQL_THREAD_ID = CONNECTION_ID() \G
*************************** 1. row ***************************
                    trx_id: 38441
                 trx_state: RUNNING
               trx_started: 2021-08-22 09:26:56
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 32
                 trx_query: select * from information_schema.INNODB_TRX where TRX_MYSQL_THREAD_ID = CONNECTION_ID()
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         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
1 row in set (0.00 sec)

可以看出當前事務ID38441, 鎖定行數為1行，符合預期。

然後再根據事務ID檢視鎖資訊:

mysql> select * from performance_schema.data_locks where ENGINE_TRANSACTION_ID = 38441 \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4720840032:1068:140354321295272
ENGINE_TRANSACTION_ID: 38441
            THREAD_ID: 72
             EVENT_ID: 246
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140354321295272
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4720840032:11:4:2:140354330466328
ENGINE_TRANSACTION_ID: 38441
            THREAD_ID: 72
             EVENT_ID: 246
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140354330466328
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
2 rows in set (0.00 sec)

可以看出當前事務對應兩個鎖資訊，第一個是表鎖：意向排他鎖，第二個是行鎖：排他鎖且非間隙鎖，都是持有鎖的狀態，而且鎖的記錄也是primarKey = 3的那條記錄。符合預期。

這裡可能會有好奇，為啥會有表鎖呢？不熟悉的可以再看看之前的文章：https://www.cnblogs.com/yuanfy008/p/14993366.html

2、開始一個事務2，先檢視當前執行緒id, 然後對id=3的那條記錄加排他鎖。

mysql> begin;
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              33 |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from t where id = 3 for update;

這是會鎖等待，因為事務1佔有著呢。

然後再去另外一個視窗根據mysql執行緒id檢視事務情況：

mysql> select * from information_schema.INNODB_TRX where TRX_MYSQL_THREAD_ID = 33 \G
*************************** 1. row ***************************
                    trx_id: 38445
                 trx_state: LOCK WAIT
               trx_started: 2021-08-22 09:52:40
     trx_requested_lock_id: 4720840880:11:4:2:140354330471280
          trx_wait_started: 2021-08-22 09:55:56
                trx_weight: 2
       trx_mysql_thread_id: 33
                 trx_query: select * from t where id = 3 for update
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         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
1 row in set (0.00 sec)

可以看出當前事務還在等待另一個鎖（ID：4720840880:11:4:2:140354330471280）的釋放，而這個鎖的持有這正好是事務1。符合預期

這個時候我們再去看這個事務對應鎖的資訊，那這個時候有幾把鎖呢？應該只有一把：表鎖 - 意向排他鎖

mysql> select * from performance_schema.data_locks where ENGINE_TRANSACTION_ID = 38445 \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4720840880:1068:140354321297272
ENGINE_TRANSACTION_ID: 38445
            THREAD_ID: 73
             EVENT_ID: 31
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140354321297272
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
1 row in set (0.00 sec)

華納雲：linux怎麼檢視nvme硬碟資訊？
2024-01-31
Linux硬碟
postgresql中怎麼檢視函式
2021-09-11
SQL函式
在django中怎麼檢視建立的資料表
2021-09-11
Django
檢視ORACLE中鎖定物件
2019-05-07
Oracle物件
InnoDB LOCK檢視變化
2021-12-01
win10系統怎麼檢視磁碟狀態_win10如何檢視硬碟資訊
2019-11-20
Win10硬碟
MySQL InnoDB設定死鎖檢測的方法
2018-05-10
MySql
MySQL：如何快速的檢視Innodb資料檔案
2019-10-28
MySql
怎樣清除v$archived_log檢視中的過期資訊
2018-06-27
Hive
檢視鎖表
2020-04-06
Innodb：為什麼lock in share mode在show engine看不到行鎖資訊
2019-03-20
win10系統怎麼檢視電腦配置檢視電腦配置資訊的詳細方法
2021-03-15
Win10
iOS除錯小技巧，在殺死程式怎麼檢視NSLog資訊
2018-09-12
iOS除錯
MySQL InnoDB 中的鎖機制
2019-05-07
MySql
怎麼檢視php版本
2024-10-07
PHP
Logcat怎麼設定filter,檢視當前執行app的log資訊
2021-09-09
GCFilterAPP
Linux 安全資訊檢視
2018-10-17
Linux
檢視weblogic版本資訊
2020-11-09
Web
win10系統jpg圖片詳細資訊怎麼檢視_win10系統jpg圖片詳細資訊如何檢視
2020-03-21
Win10
React Native Debugger在Chrome中檢視network資訊
2018-12-06
React NativeChrome
安卓怎麼檢視heic
2019-04-24
安卓
安卓中怎麼檢視heic格式的檔案
2019-08-12
安卓
win10檢視主機板型號怎麼操作 win10檢視電腦主機板資訊方法
2020-09-11
Win10
Win10系統下怎麼通過事件檢視器中檢視硬碟檢查結果
2019-05-26
Win10事件硬碟
Win10系統下怎麼透過事件檢視器中檢視硬碟檢查結果
2019-05-26
Win10事件硬碟
Git檢視指定commit資訊
2018-07-25
GitMIT
kafka 檢視佇列資訊
2018-07-25
Kafka佇列
檢視 Linux 系統資訊
2024-05-04
Linux
ExifTool for Macexif資訊檢視工具
2020-11-01
Mac
git檢視config配置資訊
2022-08-27
Git
檢視Docker容器的資訊
2022-05-25
Docker
Linux系統中如何檢視塊裝置資訊?
2023-05-18
Linux
oracle檢視被鎖的表和解鎖
2019-02-11
Oracle
mac怎麼檢視顯示隱藏資料夾？
2021-02-21
Mac
win10怎麼檢視硬碟容量_win10怎麼檢視電腦硬碟大小
2020-07-11
Win10硬碟
Innodb中有哪些鎖？
2021-07-10
MySQL什麼是InnoDB檢查點？
2023-10-30
MySql
Jtti：ubuntu系統中，怎麼檢視掛載的硬碟
2024-01-24
JttiUbuntu硬碟