MySQL鎖情況檢視命令

2023-09-06 14:10:55

本文介紹如何在MySQL資料庫中分析鎖的情況及處理思路。

MySQL版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.38-log |
+------------+
1 row in set (0.01 sec)

模擬鎖產生

A對談加鎖

mysql> show create table tG;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | s    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

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

mysql> select * from t where id<5 for update;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | s    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)

B對談插入資料，造成鎖等待現象

mysql> insert into t values(0,'null');

這裡介紹MySQL檢視鎖的3個資料字典表，分別是位於information_schema資料庫下的innodb_trx、innodb_lock_waits、innodb_locks三張表，檢視步驟如下：

先看innodb_trx表

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> select * from innodb_trxG;
*************************** 1. row ***************************
                    trx_id: 8553
                 trx_state: LOCK WAIT
               trx_started: 2022-12-14 16:52:29
     trx_requested_lock_id: 8553:45:3:2
          trx_wait_started: 2022-12-14 16:52:29
                trx_weight: 2
       trx_mysql_thread_id: 22
                 trx_query: insert into t values(0,'null')
       trx_operation_state: inserting
         trx_tables_in_use: 1
         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
*************************** 2. row ***************************
                    trx_id: 8552
                 trx_state: RUNNING
               trx_started: 2022-12-14 16:51:39
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 20
                 trx_query: NULL
       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: 5
         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
2 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> show full processlist;
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
| Id | User   | Host      | db                 | Command | Time | State    | Info                           |
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
| 20 | root   | localhost | ray                | Sleep   |  132 |          | NULL                           |
| 22 | raybak | localhost | ray                | Query   |   82 | update   | insert into t values(0,'null') |
| 24 | root   | localhost | information_schema | Query   |    0 | starting | show full processlist          |
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
3 rows in set (0.00 sec)

trx_id：唯一事務id號，本次測試中是8552和8553
trx_state：當前事務的狀態，本次測試中8553是LOCK WAIT 鎖等待狀態
trx_wait_started：事務開始等待時間，本次測試中為2022-12-14 16:52:29
trx_mysql_thread_id：執行緒id，與show full processlist中的id對應，本次測試中為22
trx_query：事務執行的SQL語句，本次測試為insert into t values(0,‘null’)
trx_operation_state：事務執行的狀態，本次測試為inserting

再看innodb_lock_waits表

mysql> select * from innodb_lock_waitsG;
*************************** 1. row ***************************
requesting_trx_id: 8553
requested_lock_id: 8553:45:3:2
  blocking_trx_id: 8552
 blocking_lock_id: 8552:45:3:2
1 row in set, 1 warning (0.00 sec)

requesting_trx_id：請求鎖的事務id，本次測試為8553
blocking_trx_id：持有鎖的事務id，也就是造成鎖等待的事務id，本次測試為8552

再看innodb_locks表

mysql> select * from innodb_locksG;
*************************** 1. row ***************************
    lock_id: 8553:45:3:2
lock_trx_id: 8553
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `ray`.`t`
 lock_index: PRIMARY
 lock_space: 45
  lock_page: 3
   lock_rec: 2
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 8552:45:3:2
lock_trx_id: 8552
  lock_mode: X
  lock_type: RECORD
 lock_table: `ray`.`t`
 lock_index: PRIMARY
 lock_space: 45
  lock_page: 3
   lock_rec: 2
  lock_data: 1
2 rows in set, 1 warning (0.00 sec)

ERROR: 
No query specified

綜合三張表查詢和show prcess fulllist得知，對談id 20（事務id 8552），鎖住了ray.t表，鎖模式是行級鎖，對談id 22（事務id 8553）的insert操作需要等待對談20釋放鎖後才能執行，因此出現了對談id 22（事務id 8553）hang住現象。

解決方法，殺對談

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

當然，殺對談也可以通過pt-kill工具更方便，在後續文章會對pt-kill工具做詳細介紹

到此這篇關於MySQL鎖情況檢視命令的文章就介紹到這了,更多相關MySQL鎖情況檢視內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL鎖情況檢視命令

熱門文章