MySQL 8.x ，查詢performance_schema.data_locks造成整個例項 hang了？

問題描述

MySQL 8.0.25 在 2023/12/22  10:17:36 左右 出現執行緒堵塞，正常情況下原本執行很快的sql執行效率陡降（比如：原本執行0.001秒的sql執行7秒仍未結束），導致十幾筆交易超時。

問題分析

根據AWR處於非Sleep狀態的使用者執行緒，Time由高到低排序，執行耗時第一為Command處於Query執行耗時達30秒，是對 performance_schema.data_locks 的查詢，該查詢需要返回3百多萬資料。在查詢 performance_schema.data_locks 之後執行的簡單DQL&DML都沒有按照正常速度完成。

performance_schema.data_locks 在慢日誌的記錄

# Time: 2023-12-22T10:17:47.968405+08:00
# User@Host: xxx[xxx] @ [127.0.0.1] Id: 10181232
# Query_time: 41.615127 Lock_time: 0.000372 Rows_sent: 7855322 Rows_examined: 7855322
SET timestamp=1703211426;
select engine, engine_lock_id, engine_transaction_id, thread_id, event_id, object_schema, object_name, partition_name, subpartition_name, index_name, object_instance_begin, lock_type, lock_mode, lock_status, lock_data 
from performance_schema.data_locks;

疑問1 為什麼 performance_schema.data_locks 會有數百萬資料？

MySQL 8.0 中， performance_schema.data_locks 包含這些資料：

• InnoDB 事務已申請但未獲得的鎖。
• InnoDB 事務正在持有的鎖。

可能有事務，有大量 正在持有鎖 或者 已申請但未獲得鎖。

疑問2 什麼操作持有百萬資料量的行鎖？

根據異常期間AWR與慢日誌，發現問題sql UPDATE xxx_dtl xxx order by t.priority desc  執行過程累計掃描200多萬資料 且 在一個未提交事務累計持有 400 多萬 row locks，也就是造成performance_schema.data_locks 有百萬資料量的原因。

                                                                                            AWR截圖

                                                                                        慢日誌截圖

疑問3 為什麼 MySQL整個例項的SQL執行會變慢？

SQL執行緩慢猜測有3種原因：1、SQL執行效率低  2、資源異常/瓶頸  3、無效等待

1、SQL執行效率低 （排除），沒有表鎖的情況下，簡單 select & insert 執行很快。
2、資源異常/瓶頸（排除），檢視監控沒有硬體報錯與沒有資源使用率跑高。
3、無效等待（復現），根據AWR抓取的現場資訊，模擬後能100%復現。

問題復現

	session1	session2	session3
t1	mysql> use dbtest; mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update sbtest1 set c='d'; Query OK, 0 rows affected (13.13 sec) Rows matched: 3000000 Changed: 0 Warnings: 0
t2			mysql> insert into t1 values(1113,1113); Query OK, 1 row affected (0.01 sec)
t3		mysql> select count(*) from performance_schema.data_locks;+----------+*| count(*) |+----------+| 3000001 |+----------+1 row in set (27.34 sec)	mysql> insert into t1 values(1114,1114); Query OK, 1 row affected (18.58 sec)
t4			mysql> insert into t1 values(1115,1115); Query OK, 1 row affected (0.02 sec)
t5	commit
t6		mysql> select count(*) from performance_schema.data_locks;+----------+*| count(*) |+----------+| 0 |+----------+1 row in set (0.01 sec)	mysql> insert into t1 values(1116,1116);Query OK, 1 row affected (0.02 sec)

用vscode檢視mysqld的函式呼叫情況

正常執行dml語句的堆疊，可見執行iterator，代表dml操作正常執行

查詢data_locks後，再執行的 dml語句堆疊變更為條件等待，等待locksys::latches的釋放

測試結論： performance_schema.data_locks有大量資料時，查詢performance_schema.data_locks會嚴重影響其他併發SQL的執行效率，執行其他SQL需要等待查詢 performance_schema.data_locks 持有的locksys::latches的釋放，才能往下執行

問題總結

MySQL 8.x當performance_schema.data_locks有大量資料時，查詢performance_schema.data_locks會嚴重影響其他併發SQL的執行效率，執行其他SQL需要等待查詢 performance_schema.data_locks 持有的locksys::latches釋放後，才能往下執行，可能會造成MySQL大量阻塞。

最佳化建議

1、業務問題sql UPDATE xxx_dtl xxx order by t.priority desc ，去掉order by t.priority desc 減少資料掃描量縮短鎖佔用時間提升執行效率
2、避免大事務，避免長時間未提交事務
3、禁止出現訪問 performance_schema.data_locks/sys.innodb_lock_waits 的"慢查詢"，最好不要查詢performance_schema.data_locks/sys.innodb_lock_waits
4、MySQL 8.X sys.innodb_lock_waits檢視依賴performance_schema.data_locks，特殊場景下也會產生阻塞問題。MySQL 8.X 中，如果只想要獲取鎖的阻塞情況，可以查詢 performance_schema.data_lock_waits。而MySQL 5.7 查詢information_schema.INNODB_LOCKS不會造成阻塞。