由FTWRL導(dǎo)致的MySQL從庫死鎖分析及參數(shù)深究
最近線上執(zhí)行備份的從庫時出現(xiàn)復(fù)制卡死現(xiàn)象，分析以后發(fā)現(xiàn)是兩個死鎖，show full processlist的狀態(tài)如圖1所示，其中，數(shù)據(jù)庫版本是官方5.7.18版本，我們內(nèi)部做了些許修改，但與此次死鎖無關(guān)。
圖一
先說一下結(jié)論，圖一中：
- 162線程是執(zhí)行innobackup執(zhí)行的flush tables with read lock;
- 144是SQL線程，并行復(fù)制中的Coordinator線程；
- 145/146是并行復(fù)制的worker線程，145/146worker線程隊列中的事務(wù)可以并行執(zhí)行。
144Coordinator線程分發(fā)relaylog中事務(wù)時發(fā)現(xiàn)這個事務(wù)不能執(zhí)行，要等待前面的事務(wù)完成提交，所以處于waiting for dependent transaction to commit的狀態(tài)。145/146線程和備份線程162形成死鎖，145線程等待162線程 global read lock 釋放，162線程占有MDL::global read lock 全局讀鎖，申請全局commit lock的時候阻塞等待146線程，146線程占有MDL:: commit lock，因為從庫設(shè)置slave_preserve_commit_order=1，保證從庫binlog提交順序，而146線程執(zhí)行事務(wù)對應(yīng)的binlog靠后面，所以等待145的事務(wù)提交。最終形成了145->162->146->145的死循環(huán)，形成死鎖。
同樣的，圖二中：
- 183是備份程序執(zhí)行的flush tables with read lock;
- 165是SQL線程，并行復(fù)制的Coordinator線程；
- 166/167是并行復(fù)制的worker線程。
圖二
165Coordinator線程分發(fā)的事務(wù)還不能執(zhí)行，進入waiting for dependent transaction to commit的狀態(tài)，183、166、167三個線程形成死鎖，183占有全局讀鎖，獲取全局commit鎖的時候進入阻塞，等待167釋放事務(wù)涉及到表的commit鎖；166，167的事務(wù)可以并行復(fù)制，167占有表級commit鎖，但是事務(wù)對應(yīng)的binlog在后面，阻塞等待166先提交進入waiting for preceding transaction to commit的狀態(tài)；166線程事務(wù)執(zhí)行時提交要獲得表級commit鎖，但已經(jīng)被183占有，所以阻塞等待。這樣形成了183->167->166->183的死鎖。
三個線程相互形成死鎖，在我的經(jīng)驗中還是很少見的，又因為涉及的MDL鎖是服務(wù)層的鎖，死鎖檢測也不會起作用。
死鎖原因分析
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、事務(wù)執(zhí)行中涉及兩個鎖
在所有更新數(shù)據(jù)的代碼路徑里，除了必須的鎖外，還會額外請求MDL_key::GLOBAL鎖的MDL_INTENTION_EXCLUSIVE鎖；在事務(wù)提交前，會先請求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中事務(wù)的提交順序會嚴格按照在relay-log中出現(xiàn)的順序提交。
所以，事務(wù)的執(zhí)行和flush tables with read lock語句獲得兩個鎖都不是原子的，并行復(fù)制時模式下按以下的順序就會出現(xiàn)死鎖。
- 事務(wù)A、B可以并行復(fù)制，relay-log中A在前，slave_preserve_commit_order=1
- 從庫回放時B事務(wù)執(zhí)行較快，先執(zhí)行到commit，獲得commit鎖，并進入waiting for preceding transaction to commit的狀態(tài)
- 執(zhí)行flush tables with read lock,進入waiting for commit的狀態(tài)
- 事務(wù)A執(zhí)行。事務(wù)A如果在FTWRL語句獲得global read lock鎖之后執(zhí)行，那么事務(wù)A就進入waiting for global read lock的狀態(tài)，即第一種死鎖；如果事務(wù)A在FTWRL獲得global read lock之前執(zhí)行，同時FTWRL獲得global commit鎖之后應(yīng)用Xid_event提交事務(wù)，則進入 waiting for the commit lock的狀態(tài)，即第二種死鎖。
復(fù)現(xiàn)
理解了死鎖出現(xiàn)的原因后，重現(xiàn)就簡單多了。重現(xiàn)這個死鎖步驟主要是2步：
1、在主庫構(gòu)造并行復(fù)制的事務(wù)，利用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);//事務(wù)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);//事務(wù)B
2、從庫執(zhí)行，修改源代碼，在關(guān)鍵地方sleep若干時間，控制并行復(fù)制的worker的執(zhí)行并留出足夠時間執(zhí)行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出現(xiàn)wait for preceding transaction to commit以后，執(zhí)行 ftwrl，出現(xiàn)圖1的死鎖；wait for preceding transaction to commit以后，出現(xiàn)日志before commit之后，執(zhí)行 ftwrl，出現(xiàn)圖2的死鎖。
如何解決？
出現(xiàn)死鎖以后如果不人工干預(yù)，IO線程正常，但是SQL線程一直卡住，一般需要等待lock-wait-timeout時間，這個值我們線上設(shè)置1800秒，所以這個死鎖會產(chǎn)生很大影響。
那么如何解決呢？kill ！kill哪個線程呢？
- 對圖1的死鎖，146處于wait for preceding transaction狀態(tài)的worker線程實際處于mysql_cond_wait的狀態(tài)，kill不起作用，所以只能kill 145線程或者備份線程，如果kill145worker線程，整個并行復(fù)制就報錯結(jié)束，show slave status顯示SQL異常退出，之后需要手動重新開啟sql線程，所以最好的辦法就是kill執(zhí)行flush tables with read lock的線程，代價最小。
- 至于圖2的死鎖，則只能kill掉執(zhí)行flush tables with read lock的線程。所以出現(xiàn)上述死鎖時，kill執(zhí)行flush tables with read lock的備份線程就恢復(fù)正常，之后擇機重新執(zhí)行備份即可。
如何避免？
設(shè)置xtrabackup的kill-long-queries-timeout參數(shù)可以避免第一種死鎖的出現(xiàn)，其實不算避免，只是出現(xiàn)以后xtrabackup會殺掉阻塞的執(zhí)行語句的線程；但是這個參數(shù)對第二種死鎖狀態(tài)則無能為力了，因為xtrabackup選擇殺掉的線程時，會過濾Info！=NULL。
另外還有個參數(shù)safe-slave-backup，執(zhí)行備份的時候加上這個參數(shù)會停掉SQL線程，這樣也肯定不會出現(xiàn)這個死鎖，只是停掉SQL未免太暴力了，個人不提倡這樣做。
可以設(shè)置slave_preserve_commit_order=0關(guān)閉從庫binlog的順序提交，關(guān)閉這個參數(shù)只是影響并行復(fù)制的事務(wù)在從庫的提交順序，對最終的數(shù)據(jù)一致性并無影響，所以如果無特別要求從庫的binlog順序必須與主庫保持一致，可以設(shè)置slave_preserve_commit_order=0避免這個死鎖的出現(xiàn)。
關(guān)于xtrabackup kill-long-query-type參數(shù)
首先說下```kill-long-queries-timeout,kill-long-query-type```參數(shù)，文檔介紹如下
- --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.
參數(shù)的作用的就是在Xtrabackup執(zhí)行FLUSH TABLES WITH READ LOCK以后，獲得全局讀鎖時，如果有正在執(zhí)行的事務(wù)會阻塞等待，kill-long-queries-timeout參數(shù)不為0時，xtrabackup內(nèi)部創(chuàng)建一個線程，連接到數(shù)據(jù)庫執(zhí)行show full processlist，如果TIME超過kill-long-queries-timeout，會kill掉線程，kill-long-query-type設(shè)置可以kill掉的SQL類型。
官方文檔介紹kill-long-query-type默認值時all，也就是所有語句都會kill掉。但在使用中發(fā)現(xiàn)，只設(shè)置kill-long-queries-timeout，未設(shè)置kill-long-query-type時，參數(shù)沒起作用！最后查閱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時一定要自己指定好類型！
總結(jié)
回顧這次執(zhí)行備份的從庫復(fù)制卡死故障，根本原因在于flush tables with read lock語句和事務(wù)執(zhí)行的過程都涉及到連個鎖，而且不是原子的，再加上并行復(fù)制以及設(shè)置了從庫binlog的順序提交，最終導(dǎo)致三個線程形成死鎖。在尋找問題的解決方案中，意外發(fā)現(xiàn)了Xtrabackup kill-long-query-type的“秘密”，告誡我們在使用中盡量顯示指定參數(shù)，一方面更準確，另一方面也便于查看。
另外，我們知道set global read_only=1語句執(zhí)行中涉及到的鎖和flush tables with read lock涉及的鎖時一樣的，也是兩個MDL鎖，所以理論上在并行復(fù)制的從庫執(zhí)行set global read_only=1語句也可能會出現(xiàn)上述的兩個死鎖，有興趣的可以驗證下。
