1. 問題背景 2.27號凌晨生產環境MySQL備庫在執行備份期間出現因FLUSH TABLES WITH READ LOCK未釋放導致備庫複製延時拉大，慢日誌內看持鎖接近25分鐘未釋放。 版本： MySQL 5.7.21 PXB 2.4.18 慢查詢日誌： 備份腳本中的備份命令： mysql_ki ...
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
