1. 問題背景
2.27號凌晨生產環境MySQL備庫在執行備份期間出現因FLUSH TABLES WITH READ LOCK未釋放導致備庫複製延時拉大，慢日誌內看持鎖接近25分鐘未釋放。
版本：
- MySQL 5.7.21
- PXB 2.4.18
慢查詢日誌：
備份指令碼中的備份命令：
mysql_kill.sh的主要邏輯內容：
備份引數：
2. 問題復現及分析
2.1 問題分析
- 144是SQL執行緒，並行複製中的Coordinator執行緒；
- 145/146是並行複製的worker執行緒，145/146worker執行緒佇列中的事務可以並行執行。
- 162執行緒是執行innobackup執行的flush tables with read lock;
144 Coordinator執行緒分發relay log中事務時發現這個事務不能執行，要等待前面的事務完成提交，所以處於waiting for dependent transaction to commit的狀態。145/146執行緒和備份執行緒162形成死鎖，145執行緒等待162執行緒 global read lock 釋放，162執行緒佔有MDL::global read lock 全域性讀鎖，申請全域性commit lock的時候阻塞等待146執行緒，146執行緒佔有MDL:: commit lock，因為從庫設定slave_preserve_commit_order=1，保證從庫binlog提交順序，而146執行緒執行事務對應的binlog靠後面，所以等待145的事務提交。最終形成了145->162->146->145的死迴圈，形成死鎖。
三個執行緒相互形成死鎖，還是很少見的。
2.2 相關引數為何未生效
--ftwrl-wait-timeout=60 指的是執行FTWRL之前，如果檢測到存在長SQL，先等待指定時間（秒），如果超時後還存在長SQL，則備份報錯退出。預設為0則表示立即執行。
--ftwrl-wait-threshold=5 指的是執行FTWRL之前，檢測長SQL的方法，如果在執行flush前存在已經執行了超過指定時間（秒）的SQL，則將該SQL定義為長SQL，預設60s。
--kill-long-queries_timeout=0 在執行FTWRL後，如果flush操作被阻塞了N秒，則kill掉阻塞它的執行緒，預設0的情況就是不kill任何阻塞flush的SQL，直到該SQL執行完成。
從上面各個引數的解釋，不難看出，--ftwrl-wait-*引數是針對執行FTWRL之前的長SQL檢測機制，對於已執行FTWRL時無濟於事，--kill-long-*引數則是設定預設值0，不起任何作用。
3. 結論與建議
- PXB備份中執行FTWRL加全域性讀鎖與SQL執行緒形成死鎖是導致本次從庫延遲過高的原因。
- 啟用
--kill-long-queries\_type和--kill-long-queries\_timeout引數，在檢測到flush被阻塞後執行kill掉相關執行緒的操作。比較暴力，存在較大的風險，若備庫無業務訪問則可考慮。 - 啟用
--safe-slave-backup引數，執行備份時該引數會停掉SQL執行緒，從而避免死鎖的產生。僅建議在無業務訪問的備庫上執行。 - 設定MySQL引數
slave\_preserve\_commit\_order=0，關閉從庫binlog的順序提交，關閉該引數只是影響並行複製的事務在從庫的提交順序，對最終的資料一致性並無影響，所以如果無特別要求從庫的binlog順序必須與主庫保持一致，可以考慮設定slave\_preserve\_commit\_order=0避免死鎖的產生。
Enjoy GreatSQL 😃
關於 GreatSQL
GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。
相關連結： GreatSQL社群 Gitee GitHub Bilibili
GreatSQL社群：
社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html
技術交流群：
微信：掃碼新增
GreatSQL社群助手微信好友，傳送驗證資訊加群。
