由FTWRL導致的MySQL從庫死鎖分析及參數深究
最近線上執行備份的從庫時出現復制卡死現象，分析以后發現是兩個死鎖，show full processlist的狀態如圖1所示，其中，數據庫版本是官方5.7.18版本，我們內部做了些許修改，但與此次死鎖無關。
圖一
先說一下結論，圖一中：
- 162線程是執行innobackup執行的flush tables with read lock;
- 144是SQL線程，并行復制中的Coordinator線程；
- 145/146是并行復制的worker線程，145/146worker線程隊列中的事務可以并行執行。
144Coordinator線程分發relaylog中事務時發現這個事務不能執行，要等待前面的事務完成提交，所以處于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的死循環，形成死鎖。
同樣的，圖二中：
- 183是備份程序執行的flush tables with read lock;
- 165是SQL線程，并行復制的Coordinator線程；
- 166/167是并行復制的worker線程。
圖二
165Coordinator線程分發的事務還不能執行，進入waiting for dependent transaction to commit的狀態，183、166、167三個線程形成死鎖，183占有全局讀鎖，獲取全局commit鎖的時候進入阻塞，等待167釋放事務涉及到表的commit鎖；166，167的事務可以并行復制，167占有表級commit鎖，但是事務對應的binlog在后面，阻塞等待166先提交進入waiting for preceding transaction to commit的狀態；166線程事務執行時提交要獲得表級commit鎖，但已經被183占有，所以阻塞等待。這樣形成了183->167->166->183的死鎖。
三個線程相互形成死鎖，在我的經驗中還是很少見的，又因為涉及的MDL鎖是服務層的鎖，死鎖檢測也不會起作用。
死鎖原因分析
1、MDL鎖
參考：http://mysql.taobao.org/monthly/2015/11/04/
2、flush tables with read lock獲取兩個鎖
MDL::global read lock 和MDL::global commit lock，而且是顯示的MDL_SHARED鎖。
- //Global_read_lock::lock_global_read_lock
- MDL_REQUEST_INIT(&mdl_request,MDL_key::GLOBAL, "", "", MDL_SHARED, MDL_EXPLICIT);
- //Global_read_lock::make_global_read_lock_block_commit
- MDL_REQUEST_INIT(&mdl_request,MDL_key::COMMIT, "", "", MDL_SHARED, MDL_EXPLICIT);
3、事務執行中涉及兩個鎖
在所有更新數據的代碼路徑里，除了必須的鎖外，還會額外請求MDL_key::GLOBAL鎖的MDL_INTENTION_EXCLUSIVE鎖；在事務提交前，會先請求MDL_key::COMMIT鎖的MDL_INTENTION_EXCLUSIVE鎖。對于scope鎖來說，IX鎖和S鎖是不兼容的。
4、--slave_preserve_commit_order
- For multi-threaded slaves, enabling this variable ensures that
- transactions are externalized on theslave in the same order as they appear
- in the slave's relay log.
slave_preserve_commit_order=1時，relay-log中事務的提交順序會嚴格按照在relay-log中出現的順序提交。
所以，事務的執行和flush tables with read lock語句獲得兩個鎖都不是原子的，并行復制時模式下按以下的順序就會出現死鎖。
- 事務A、B可以并行復制，relay-log中A在前，slave_preserve_commit_order=1
- 從庫回放時B事務執行較快，先執行到commit，獲得commit鎖，并進入waiting for preceding transaction to commit的狀態
- 執行flush tables with read lock,進入waiting for commit的狀態
- 事務A執行。事務A如果在FTWRL語句獲得global read lock鎖之后執行，那么事務A就進入waiting for global read lock的狀態，即第一種死鎖；如果事務A在FTWRL獲得global read lock之前執行，同時FTWRL獲得global commit鎖之后應用Xid_event提交事務，則進入 waiting for the commit lock的狀態，即第二種死鎖。
復現
理解了死鎖出現的原因后，重現就簡單多了。重現這個死鎖步驟主要是2步：
1、在主庫構造并行復制的事務，利用debug_sync
- session 1
- SET DEBUG_SYNC='waiting_in_the_middle_of_flush_stage SIGNAL s1 WAIT_FOR f';
- insert into test.test values(13);//事務A
- //session 2
- SET DEBUG_SYNC= 'now WAIT_FOR s1';
- SET DEBUG_SYNC= 'bgc_after_enrolling_for_flush_stage SIGNAL f';
- insert into test.test values(16);//事務B
2、從庫執行，修改源代碼，在關鍵地方sleep若干時間，控制并行復制的worker的執行并留出足夠時間執行flush tables with read lock
修改點如下：
- //Xid_apply_log_event::do_apply_event_worker
- if(w->id==0)
- {
- std::cout<<"before commit"<<std::endl;
- sleep(20);
- }
- //pop_jobs_item
- if(worker->id==0)
- sleep(20);
開啟slave以后，觀察show full processlist和輸出日志，在其中一個worker出現wait for preceding transaction to commit以后，執行 ftwrl，出現圖1的死鎖；wait for preceding transaction to commit以后，出現日志before commit之后，執行 ftwrl，出現圖2的死鎖。
如何解決？
出現死鎖以后如果不人工干預，IO線程正常，但是SQL線程一直卡住，一般需要等待lock-wait-timeout時間，這個值我們線上設置1800秒，所以這個死鎖會產生很大影響。
那么如何解決呢？kill ！kill哪個線程呢？
- 對圖1的死鎖，146處于wait for preceding transaction狀態的worker線程實際處于mysql_cond_wait的狀態，kill不起作用，所以只能kill 145線程或者備份線程，如果kill145worker線程，整個并行復制就報錯結束，show slave status顯示SQL異常退出，之后需要手動重新開啟sql線程，所以最好的辦法就是kill執行flush tables with read lock的線程，代價最小。
- 至于圖2的死鎖，則只能kill掉執行flush tables with read lock的線程。所以出現上述死鎖時，kill執行flush tables with read lock的備份線程就恢復正常，之后擇機重新執行備份即可。
如何避免？
設置xtrabackup的kill-long-queries-timeout參數可以避免第一種死鎖的出現，其實不算避免，只是出現以后xtrabackup會殺掉阻塞的執行語句的線程；但是這個參數對第二種死鎖狀態則無能為力了，因為xtrabackup選擇殺掉的線程時，會過濾Info！=NULL。
另外還有個參數safe-slave-backup，執行備份的時候加上這個參數會停掉SQL線程，這樣也肯定不會出現這個死鎖，只是停掉SQL未免太暴力了，個人不提倡這樣做。
可以設置slave_preserve_commit_order=0關閉從庫binlog的順序提交，關閉這個參數只是影響并行復制的事務在從庫的提交順序，對最終的數據一致性并無影響，所以如果無特別要求從庫的binlog順序必須與主庫保持一致，可以設置slave_preserve_commit_order=0避免這個死鎖的出現。
關于xtrabackup kill-long-query-type參數
首先說下```kill-long-queries-timeout,kill-long-query-type```參數，文檔介紹如下
- --KILL-LONG-QUERY-TYPE=ALL|SELECT
- This option specifies which types of queries should be killed to
- unblock the global lock. Default is “all”.
- --KILL-LONG-QUERIES-TIMEOUT=SECONDS**
- This option specifies the number of seconds innobackupex waits
- between starting FLUSH TABLES WITH READ LOCK and killing those queries
- that block it. Default is 0 seconds, which means innobackupex will not
- attempt to kill any queries. In order to use this option xtrabackup
- user should have PROCESS and SUPER privileges.Where supported (Percona
- Server 5.6+) xtrabackup will automatically use Backup Locks as a
- lightweight alternative to FLUSH TABLES WITH READ LOCK to copy non-
- InnoDB data to avoid blocking DML queries that modify InnoDB tables.
參數的作用的就是在Xtrabackup執行FLUSH TABLES WITH READ LOCK以后，獲得全局讀鎖時，如果有正在執行的事務會阻塞等待，kill-long-queries-timeout參數不為0時，xtrabackup內部創建一個線程，連接到數據庫執行show full processlist，如果TIME超過kill-long-queries-timeout，會kill掉線程，kill-long-query-type設置可以kill掉的SQL類型。
官方文檔介紹kill-long-query-type默認值時all，也就是所有語句都會kill掉。但在使用中發現，只設置kill-long-queries-timeout，未設置kill-long-query-type時，參數沒起作用！最后查閱xtrabackup代碼，如下：
- {"kill-long-query-type", OPT_KILL_LONG_QUERY_TYPE,
- "This option specifies which types of queries should be killed to "
- "unblock the global lock. Default is \"all\".",
- (uchar*) &opt_ibx_kill_long_query_type,
- (uchar*) &opt_ibx_kill_long_query_type, &query_type_typelib,
- GET_ENUM, REQUIRED_ARG, QUERY_TYPE_SELECT, 0, 0, 0, 0, 0}
心中一萬頭草泥馬，也許只是筆誤，但也太坑爹了！所以使用kill-long-query-type時一定要自己指定好類型！
總結
回顧這次執行備份的從庫復制卡死故障，根本原因在于flush tables with read lock語句和事務執行的過程都涉及到連個鎖，而且不是原子的，再加上并行復制以及設置了從庫binlog的順序提交，最終導致三個線程形成死鎖。在尋找問題的解決方案中，意外發現了Xtrabackup kill-long-query-type的“秘密”，告誡我們在使用中盡量顯示指定參數，一方面更準確，另一方面也便于查看。
另外，我們知道set global read_only=1語句執行中涉及到的鎖和flush tables with read lock涉及的鎖時一樣的，也是兩個MDL鎖，所以理論上在并行復制的從庫執行set global read_only=1語句也可能會出現上述的兩個死鎖，有興趣的可以驗證下。
