最近線上執(zhí)行備份的從庫時(shí)出現(xiàn)復(fù)制卡死現(xiàn)象，分析以后發(fā)現(xiàn)是兩個(gè)死鎖，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ù)時(shí)發(fā)現(xiàn)這個(gè)事務(wù)不能執(zhí)行，要等待前面的事務(wù)完成提交，所以處于waiting for dependent transaction to commit的狀態(tài)。145/146線程和備份線程162形成死鎖，145線程等待162線程 global read lock 釋放，162線程占有MDL::global read lock 全局讀鎖，申請(qǐng)全局commit lock的時(shí)候阻塞等待146線程，146線程占有MDL:: commit lock，因?yàn)閺膸煸O(shè)置slave_preserve_commit_order=1，保證從庫binlog提交順序，而146線程執(zhí)行事務(wù)對(duì)應(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三個(gè)線程形成死鎖，183占有全局讀鎖，獲取全局commit鎖的時(shí)候進(jìn)入阻塞，等待167釋放事務(wù)涉及到表的commit鎖；166，167的事務(wù)可以并行復(fù)制，167占有表級(jí)commit鎖，但是事務(wù)對(duì)應(yīng)的binlog在后面，阻塞等待166先提交進(jìn)入waiting for preceding transaction to commit的狀態(tài)；166線程事務(wù)執(zhí)行時(shí)提交要獲得表級(jí)commit鎖，但已經(jīng)被183占有，所以阻塞等待。這樣形成了183->167->166->183的死鎖。

三個(gè)線程相互形成死鎖，在我的經(jīng)驗(yàn)中還是很少見的，又因?yàn)樯婕暗腗DL鎖是服務(wù)層的鎖，死鎖檢測(cè)也不會(huì)起作用。

死鎖原因分析

1、MDL鎖 

參考：http://mysql.taobao.org/monthly/2015/11/04/

2、flush tables with read lock獲取兩個(gè)鎖

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í)行中涉及兩個(gè)鎖

在所有更新數(shù)據(jù)的代碼路徑里，除了必須的鎖外，還會(huì)額外請(qǐng)求MDL_key::GLOBAL鎖的MDL_INTENTION_EXCLUSIVE鎖；在事務(wù)提交前，會(huì)先請(qǐng)求MDL_key::COMMIT鎖的MDL_INTENTION_EXCLUSIVE鎖。對(duì)于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時(shí)，relay-log中事務(wù)的提交順序會(huì)嚴(yán)格按照在relay-log中出現(xiàn)的順序提交。

所以，事務(wù)的執(zhí)行和flush tables with read lock語句獲得兩個(gè)鎖都不是原子的，并行復(fù)制時(shí)模式下按以下的順序就會(huì)出現(xiàn)死鎖。

1. 事務(wù)A、B可以并行復(fù)制，relay-log中A在前，slave_preserve_commit_order=1
2. 從庫回放時(shí)B事務(wù)執(zhí)行較快，先執(zhí)行到commit，獲得commit鎖，并進(jìn)入waiting for   preceding transaction to commit的狀態(tài)
3. 執(zhí)行flush tables with read lock,進(jìn)入waiting  for commit的狀態(tài)
4. 事務(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í)行，同時(shí)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)這個(gè)死鎖步驟主要是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若干時(shí)間，控制并行復(fù)制的worker的執(zhí)行并留出足夠時(shí)間執(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和輸出日志，在其中一個(gè)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í)間，這個(gè)值我們線上設(shè)置1800秒，所以這個(gè)死鎖會(huì)產(chǎn)生很大影響。

那么如何解決呢？kill ！kill哪個(gè)線程呢？

• 對(duì)圖1的死鎖，146處于wait for  preceding transaction狀態(tài)的worker線程實(shí)際處于mysql_cond_wait的狀態(tài)，kill不起作用，所以只能kill 145線程或者備份線程，如果kill145worker線程，整個(gè)并行復(fù)制就報(bào)錯(cuò)結(jié)束，show slave status顯示SQL異常退出，之后需要手動(dòng)重新開啟sql線程，所以最好的辦法就是kill執(zhí)行flush tables with read lock的線程，代價(jià)最小。
• 至于圖2的死鎖，則只能kill掉執(zhí)行flush tables with read lock的線程。所以出現(xiàn)上述死鎖時(shí)，kill執(zhí)行flush tables with read lock的備份線程就恢復(fù)正常，之后擇機(jī)重新執(zhí)行備份即可。 

如何避免？

設(shè)置xtrabackup的kill-long-queries-timeout參數(shù)可以避免第一種死鎖的出現(xiàn)，其實(shí)不算避免，只是出現(xiàn)以后xtrabackup會(huì)殺掉阻塞的執(zhí)行語句的線程；但是這個(gè)參數(shù)對(duì)第二種死鎖狀態(tài)則無能為力了，因?yàn)閤trabackup選擇殺掉的線程時(shí)，會(huì)過濾Info！=NULL。

另外還有個(gè)參數(shù)safe-slave-backup，執(zhí)行備份的時(shí)候加上這個(gè)參數(shù)會(huì)停掉SQL線程，這樣也肯定不會(huì)出現(xiàn)這個(gè)死鎖，只是停掉SQL未免太暴力了，個(gè)人不提倡這樣做。

可以設(shè)置slave_preserve_commit_order=0關(guān)閉從庫binlog的順序提交，關(guān)閉這個(gè)參數(shù)只是影響并行復(fù)制的事務(wù)在從庫的提交順序，對(duì)最終的數(shù)據(jù)一致性并無影響，所以如果無特別要求從庫的binlog順序必須與主庫保持一致，可以設(shè)置slave_preserve_commit_order=0避免這個(gè)死鎖的出現(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以后，獲得全局讀鎖時(shí)，如果有正在執(zhí)行的事務(wù)會(huì)阻塞等待，kill-long-queries-timeout參數(shù)不為0時(shí)，xtrabackup內(nèi)部創(chuàng)建一個(gè)線程，連接到數(shù)據(jù)庫執(zhí)行show full processlist，如果TIME超過kill-long-queries-timeout，會(huì)kill掉線程，kill-long-query-type設(shè)置可以kill掉的SQL類型。

官方文檔介紹kill-long-query-type默認(rèn)值時(shí)all，也就是所有語句都會(huì)kill掉。但在使用中發(fā)現(xiàn)，只設(shè)置kill-long-queries-timeout，未設(shè)置kill-long-query-type時(shí)，參數(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時(shí)一定要自己指定好類型！

總結(jié)

回顧這次執(zhí)行備份的從庫復(fù)制卡死故障，根本原因在于flush tables with read lock語句和事務(wù)執(zhí)行的過程都涉及到連個(gè)鎖，而且不是原子的，再加上并行復(fù)制以及設(shè)置了從庫binlog的順序提交，最終導(dǎo)致三個(gè)線程形成死鎖。在尋找問題的解決方案中，意外發(fā)現(xiàn)了Xtrabackup kill-long-query-type的“秘密”，告誡我們?cè)谑褂弥斜M量顯示指定參數(shù)，一方面更準(zhǔn)確，另一方面也便于查看。

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

 

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

 

標(biāo)簽： Mysql ssl 代碼 數(shù)據(jù)庫

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。