最近線上執(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線程隊(duì)列中的事務(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，因?yàn)閺膸煸O(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í)行，進(jìn)入waiting for dependent transaction to commit的狀態(tài)，183、166、167三個線程形成死鎖，183占有全局讀鎖，獲取全局commit鎖的時候進(jìn)入阻塞，等待167釋放事務(wù)涉及到表的commit鎖；166，167的事務(wù)可以并行復(fù)制，167占有表級commit鎖，但是事務(wù)對應(yīng)的binlog在后面，阻塞等待166先提交進(jìn)入waiting for preceding transaction to commit的狀態(tài)；166線程事務(wù)執(zhí)行時提交要獲得表級commit鎖，但已經(jīng)被183占有，所以阻塞等待。這樣形成了183->167->166->183的死鎖。

三個線程相互形成死鎖，在我的經(jīng)驗(yàn)中還是很少見的，又因?yàn)樯婕暗腗DL鎖是服務(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 thattransactions 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ù)的提交順序會嚴(yán)格按照在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鎖，并進(jìn)入waiting for preceding transaction to commit的狀態(tài) 執(zhí)行flush tables with read lock,進(jìn)入waiting for commit的狀態(tài) 事務(wù)A執(zhí)行。事務(wù)A如果在FTWRL語句獲得global read lock鎖之后執(zhí)行，那么事務(wù)A就進(jìn)入waiting for global read lock的狀態(tài)，即第一種死鎖；如果事務(wù)A在FTWRL獲得global read lock之前執(zhí)行，同時FTWRL獲得global commit鎖之后應(yīng)用Xid_event提交事務(wù)，則進(jìn)入 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

修改點(diǎn)如下：

//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線程實(shí)際處于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ù)正常，之后擇機(jī)重新執(zhí)行備份即可。

如何避免？

設(shè)置xtrabackup的kill-long-queries-timeout參數(shù)可以避免第一種死鎖的出現(xiàn)，其實(shí)不算避免，只是出現(xiàn)以后xtrabackup會殺掉阻塞的執(zhí)行語句的線程；但是這個參數(shù)對第二種死鎖狀態(tài)則無能為力了，因?yàn)閤trabackup選擇殺掉的線程時，會過濾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 tounblock the global lock. Default is “all”. --KILL-LONG-QUERIES-TIMEOUT=SECONDS** This option specifies the number of seconds innobackupex waitsbetween starting FLUSH TABLES WITH READ LOCK and killing those queriesthat block it. Default is 0 seconds, which means innobackupex will notattempt to kill any queries. In order to use this option xtrabackupuser should have PROCESS and SUPER privileges.Where supported (PerconaServer 5.6+) xtrabackup will automatically use Backup Locks as alightweight 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默認(rèn)值時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ù)，一方面更準(zhǔn)確，另一方面也便于查看。

另外，我們知道set global read_only=1語句執(zhí)行中涉及到的鎖和flush tables with read lock涉及的鎖時一樣的，也是兩個MDL鎖，所以理論上在并行復(fù)制的從庫執(zhí)行set global read_only=1語句也可能會出現(xiàn)上述的兩個死鎖，有興趣的可以驗(yàn)證下。

來自：http://database.51cto.com/art/201801/562689.htm