辨識 MySQL 5.5 上的阻塞查詢

對於在 Debian 下使用 MySQL 5.5.60 的應用程序，最近我們遇到了數據庫寫入事務，這些事務在設置的 50s 時間限制內沒有成功innodb_lock_wait_timeout。

我需要確定持有這些鎖的事務/語句，從而開始解決問題的原因。

我的希望是從 MySQL 端獲取這些資訊，但到目前為止，辨識攔截器似乎非常困難。

最好的資訊是在一份聲明給出的報告中的“交易”部分show engine innodb status，例如：

---TRANSACTION 6FEE8DD5, ACTIVE 13021 sec
1314 lock struct(s), heap size 129464, 1599 row lock(s), undo log entries 1212
MySQL thread id 16165411, OS thread handle 0x7f1491f53700, query id 1056480972 host.domain.tld 217.69.64.139 username
Trx read view will not see trx with id >= 6FEE8DD6, sees < 6FEE0FE9

雖然我在這裡沒有看到任何 SQL 語句。

由於未知原因，此類事務未顯示在 MySQL 慢查詢日誌（或 processlist、innotop、mytop 等）中。如果它出現在那裡，它不會列出 MySQL 執行緒 id 或查詢 id。

執行緒 id 似乎列在 MySQL 全域日誌中，我不敢在工作時間啟動它，因為它必須儲存大量資訊。

有更好的方法嗎？

到目前為止，我對不可見性的唯一提示是多個語句可能會以不同的方式顯示。

即使連接處於“睡眠”階段，注意事務也可以處於活動狀態——如果它是多語句事務。

（來源）

會是這樣嗎？以及如何辨識/調試這樣的鎖定源？

此查詢可能有助於您搜尋鎖定和阻止 ID。

# IS-tx-wait-block-qrys.sql to help research BUSY systems Last Updated 20191227 wh
# if this helps you, please email SHAREABLE comments to info@mysqlservertuning.com
# From article at 
# https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-server-activity/
# 20191227 tested on MariaDB 10.3.10

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx加入資訊_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

• 不要用autocommit=0，你可能忘了做COMMIT。
• 再試SHOW PROCESSLIST;一次。但這一次尋找“時間”最高的非系統程序。它可能表示忘記COMMIT（或其他）的“使用者@主機”。
• 唉，slowlog 在完成之前不會顯示查詢。

“睡眠”可能發生在一個打開的事務中：

BEGIN;
SELECT ... FOR UPDATE; -- "SELECT" shows in processlist
do some client work    -- "Sleep" shows now
INSERT ...             -- "INSERT" shows
COMMIT                 -- "COMMIT" shows

引用自：https://dba.stackexchange.com/questions/260543