快捷導(dǎo)航

MySQL8.0 鎖等待排查的問(wèn)題解決

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

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

前言

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

1. data_locks

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

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

下面的 SQL 是精簡(jiǎn)過(guò)的，只保留了常用的字段：

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 表中每行都在主鍵上加寫(xiě)鎖，在 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 庫(kù)中的 data_lock_waits 表可以觀測(cè)鎖等待的情況，只有發(fā)生堵塞的時(shí)候才會(huì)記錄。如果你發(fā)現(xiàn)這張表的記錄很多，說(shuō)明目前數(shù)據(jù)庫(kù)有很多鎖等待的情況。

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

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

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：被堵塞的事務(wù) ID。
waiting_thread_id：被堵塞的線程 ID。
waiting_trx_state：被堵塞事務(wù)的狀態(tài)。
waiting_query：被堵塞事務(wù)的語(yǔ)句。
blocking_trx_id：堵塞該事務(wù)的事務(wù) ID。
blocking_thread_id：堵塞該事務(wù)的線程 ID，如果查詢(xún)返回很多行，且大部分該值都相同，說(shuō)明堵塞源都相同，可通過(guò)該 ID 查到會(huì)話 ID 并 kill 掉。
trx_wait_started：被堵塞事務(wù)的開(kāi)始時(shí)間。
wait_second：鎖堵塞的時(shí)間長(zhǎng)，單位為秒。

3. sys.innodb_lock_waits

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

*************************** 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 掉堵塞會(huì)話的 SQL，不過(guò)在云數(shù)據(jù)庫(kù)上面 sys 庫(kù)一般都沒(méi)有給用戶權(quán)限。

4. 狀態(tài)變量

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

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

下面我們來(lái)做一個(gè)實(shí)驗(yàn)：

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     |
+-------------------------------+-------+

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

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

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