MySQL8.0 鎖等待排查的問題解決

更新時間：2024年10月28日 11:29:48 作者：Bing@DBA

MySQL 5.7 一些鎖監(jiān)控表,在 8.0 都發(fā)生了變化,本文主要介紹了MySQL8.0如何排查鎖等待問題,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

前言

MySQL 5.7 版本的時候鎖等待排查用的元數(shù)據(jù)，主要存儲在 information_schema 庫下的 INNODB_LOCKS 和 INNODB_LOCK_WAITS 表，8.0 版本這兩張表刪除了，在 performance_schema 提供新的鎖相關的表，本篇文章將結(jié)合這些改動，介紹 MySQL 8.0 版本如何排查鎖等待問題。

1. data_locks

performance_schema 庫中的 data_locks 可以觀測 MySQL 中的鎖，對于 InnoDB 引擎可以觀測到表鎖、行鎖、Gap 鎖、Next-key 鎖。值得注意的是 data_locks 表無論鎖是否處理等待狀態(tài)，都會記錄，所以有利于用戶通過該表測試 MySQL 的加鎖邏輯。

ENGINE：持有鎖的存儲引擎。
ENGINE_LOCK_ID：內(nèi)部格式，用戶可忽略。
ENGINE_TRANSACTION_ID：事務 ID 可以與 INFORMATION_SCHEMA INNODB_TRX 表的 trx_id 字段關聯(lián)起來。
THREAD_ID：創(chuàng)建鎖的線程 ID，一般用不著，通過事務 ID 就可以定位到會話連接。
EVENT_ID：與 THREAD_ID 組合使用，可以從 events 表中查到 SQL 語句。
OBJECT_SCHEMA：鎖定的數(shù)據(jù)庫名稱。
OBJECT_NAME：鎖定表的名稱。
PARTITION_NAME：鎖定分區(qū)的名稱，如果不是分區(qū)表為 NULL。
SUBPARTITION_NAME：鎖定子分區(qū)的名稱，如果不是分區(qū)表為 NULL。
INDEX_NAME：索引的名稱。
OBJECT_INSTANCE_BEGIN：鎖在內(nèi)存中的地址。
LOCK_TYPE：鎖的類型，對于 InnoDB，允許的值是 RECORD 行級鎖， TABLE 對于表級鎖。
LOCK_MODE：鎖的行為，用來標記是意向鎖、寫鎖、讀鎖、間隙鎖、Next-key 鎖。
LOCK_STATUS：鎖請求的狀態(tài)，對于 InnoDB 引擎有 GRANTED 已持有和 WAITING 正在等待鎖，兩種狀態(tài)。
LOCK_DATA：如果是在主鍵加鎖，顯示主鍵值，如果是二級索引加鎖顯示二級索引的值和對應主鍵的值。

下面的 SQL 是精簡過的，只保留了常用的字段：

select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;

Session 1：鎖定 test_semi 全表。

begin;
select * from test_semi for update;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 10 |    1 |  123 |
| 11 |    2 |  123 |
| 12 |    1 |  123 |
| 13 |    2 |  123 |
| 14 |    1 |  123 |
+----+------+------+

Session 2：由下表可以看出 test_semi 表中每行都在主鍵上加寫鎖，在 test_semi 表上加入 IX 意向鎖。

ENGINE_TRANSACTION_ID	OBJECT_SCHEMA	OBJECT_NAME	INDEX_NAME	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
6711	test	test_semi	NULL	TABLE	IX	GRANTED	NULL
6711	test	test_semi	PRIMARY	RECORD	X,REC_NOT_GAP	GRANTED	10
6711	test	test_semi	PRIMARY	RECORD	X,REC_NOT_GAP	GRANTED	11
6711	test	test_semi	PRIMARY	RECORD	X,REC_NOT_GAP	GRANTED	12
6711	test	test_semi	PRIMARY	RECORD	X,REC_NOT_GAP	GRANTED	13
6711	test	test_semi	PRIMARY	RECORD	X,REC_NOT_GAP	GRANTED	14

2. data_lock_waits

performance_schema 庫中的 data_lock_waits 表可以觀測鎖等待的情況，只有發(fā)生堵塞的時候才會記錄。如果你發(fā)現(xiàn)這張表的記錄很多，說明目前數(shù)據(jù)庫有很多鎖等待的情況。

ENGINE：存儲引擎。
REQUESTING_ENGINE_LOCK_ID：存儲引擎請求的鎖的 ID。
REQUESTING_ENGINE_TRANSACTION_ID：被堵塞的事務 ID 可以與 INFORMATION_SCHEMA INNODB_TRX 表的 trx_id 字段關聯(lián)起來。
REQUESTING_THREAD_ID：請求鎖的會話的線程 ID。
REQUESTING_EVENT_ID：在請求鎖的會話中導致鎖請求的性能模式事件。
REQUESTING_OBJECT_INSTANCE_BEGIN：請求的鎖在內(nèi)存中的地址。
BLOCKING_ENGINE_LOCK_ID：阻塞鎖的 ID，可以與 data_locks 表的 ENGINE_LOCK_ID 字段進行關聯(lián)。
BLOCKING_ENGINE_TRANSACTION_ID：持有鎖的事務 ID 可以與 INFORMATION_SCHEMA INNODB_TRX 表的 trx_id 字段關聯(lián)起來。
BLOCKING_THREAD_ID：持有阻塞鎖的會話的線程 ID。
BLOCKING_EVENT_ID：導致持有該鎖的會話中出現(xiàn)阻塞鎖的性能模式事件。
BLOCKING_OBJECT_INSTANCE_BEGIN：阻塞鎖在內(nèi)存中的地址。

基于該表與事務表的關聯(lián)可以獲得當前堵塞的事務信息：

select 
   trx.trx_id as waiting_trx_id,
   trx.trx_mysql_thread_id as waiting_thread_id,
   trx.trx_state as waiting_trx_state,
   trx.trx_query as waiting_query,
   lk.BLOCKING_ENGINE_TRANSACTION_ID as blocking_trx_id,
   lk.BLOCKING_THREAD_ID as blocking_thread_id,
   trx.trx_wait_started as trx_wait_started,
   TIMESTAMPDIFF(SECOND, trx.trx_wait_started, CURRENT_TIMESTAMP) as wait_second
from 
  performance_schema.data_lock_waits as lk 
  join information_schema.INNODB_TRX as trx on lk.REQUESTING_ENGINE_TRANSACTION_ID = trx.trx_id;

waiting_trx_id：被堵塞的事務 ID。
waiting_thread_id：被堵塞的線程 ID。
waiting_trx_state：被堵塞事務的狀態(tài)。
waiting_query：被堵塞事務的語句。
blocking_trx_id：堵塞該事務的事務 ID。
blocking_thread_id：堵塞該事務的線程 ID，如果查詢返回很多行，且大部分該值都相同，說明堵塞源都相同，可通過該 ID 查到會話 ID 并 kill 掉。
trx_wait_started：被堵塞事務的開始時間。
wait_second：鎖堵塞的時間長，單位為秒。

3. sys.innodb_lock_waits

sys 庫里面大部分表都是視圖，MySQL 創(chuàng)建該庫的原因是為了簡化 performance_schema 表的使用難度，該庫里面提供一個視圖，可以查到非常詳細的鎖堵塞信息。

*************************** 1. row ***************************
                wait_started: 2024-08-06 15:37:36
                    wait_age: 00:00:11
               wait_age_secs: 11
                locked_table: `test`.`test_semi`
         locked_table_schema: test
           locked_table_name: test_semi
      locked_table_partition: NULL
   locked_table_subpartition: NULL
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 421847145074688
         waiting_trx_started: 2024-08-06 15:37:36
             waiting_trx_age: 00:00:11
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 818473
               waiting_query: select * from test_semi for share
             waiting_lock_id: 140372168364032:10:4:2:140372080646752
           waiting_lock_mode: S,REC_NOT_GAP
             blocking_trx_id: 6711
                blocking_pid: 819104
              blocking_query: NULL
            blocking_lock_id: 140372168364840:10:4:2:140372080652768
          blocking_lock_mode: X,REC_NOT_GAP
        blocking_trx_started: 2024-08-06 14:35:20
            blocking_trx_age: 01:02:27
    blocking_trx_rows_locked: 5
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 819104
sql_kill_blocking_connection: KILL 819104

結(jié)果集合中還給出 kill 掉堵塞會話的 SQL，不過在云數(shù)據(jù)庫上面 sys 庫一般都沒有給用戶權(quán)限。

4. 狀態(tài)變量

可通過下方狀態(tài)變量了解數(shù)據(jù)庫中的行鎖信息：

Innodb_row_lock_current_waits：當前正在等待行鎖的操作數(shù)。
Innodb_row_lock_time：獲取行鎖花費的總時間，單位毫秒。
Innodb_row_lock_time_avg：獲取行鎖花費的平均時間，單位毫秒。
Innodb_row_lock_time_max：獲取行鎖花費的最大時間，單位毫秒。

下面我們來做一個實驗：

root@mysql 14:38:  [(none)]>show status like '%Innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 33165 |
| Innodb_row_lock_time_avg      | 16582 |
| Innodb_row_lock_time_max      | 28845 |
| Innodb_row_lock_waits         | 2     |
+-------------------------------+-------+

Session 1	Session 2
Begin;
delete from score where id = 5;
	update score set number = 66 where id = 5; – 等待行鎖

root@mysql 14:41:  [test]>show status like '%Innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1     |
| Innodb_row_lock_time          | 33165 |
| Innodb_row_lock_time_avg      | 11055 |
| Innodb_row_lock_time_max      | 28845 |
| Innodb_row_lock_waits         | 3     |
+-------------------------------+-------+

此時可以發(fā)現(xiàn) Innodb_row_lock_waits 和 Innodb_row_lock_current_waits 都增長了，time 相關的變量需要等事務結(jié)束后才會進行計算。

5. 狀態(tài)變量 bug

Innodb_row_lock_current_waits 從文檔描述來看，反映的是當前數(shù)據(jù)庫行鎖的操作數(shù)，不過該值有時會出現(xiàn)不準的情況。有位研發(fā)問我某云的監(jiān)控上顯示當前數(shù)據(jù)庫的行鎖有 20 億個，當前數(shù)據(jù)庫還正常嗎？當時嚇了一跳，會話沒有任何異常，而且使用剛才介紹的鎖排查方法，都沒有異常。最后發(fā)現(xiàn)監(jiān)控采集的是 Innodb_row_lock_current_waits 的值，最后發(fā)現(xiàn)該值非常不準，有 Bug，所以大家如果遇到此類問題，可以先忽略，自制鎖等待監(jiān)控可以查 data_lock_waits 表，但是頻次不建議太高。