日本综合一区二区|亚洲中文天堂综合|日韩欧美自拍一区|男女精品天堂一区|欧美自拍第6页亚洲成人精品一区|亚洲黄色天堂一区二区成人|超碰91偷拍第一页|日韩av夜夜嗨中文字幕|久久蜜综合视频官网|精美人妻一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時(shí)間：8:30-17:00
你可能遇到了下面的問(wèn)題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
由FTWRL導(dǎo)致的MySQL從庫(kù)死鎖分析及參數(shù)深究

最近線上執(zhí)行備份的從庫(kù)時(shí)出現(xiàn)復(fù)制卡死現(xiàn)象，分析以后發(fā)現(xiàn)是兩個(gè)死鎖，show full processlist的狀態(tài)如圖1所示，其中，數(shù)據(jù)庫(kù)版本是官方5.7.18版本，我們內(nèi)部做了些許修改，但與此次死鎖無(wú)關(guān)。   

                     

圖一

先說(shuō)一下結(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)閺膸?kù)設(shè)置slave_preserve_commit_order=1，保證從庫(kù)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)中還是很少見(jià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鎖。    

 
 
 
 
    
  
  
  
  
  1. //Global_read_lock::lock_global_read_lock 
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3.     MDL_REQUEST_INIT(&mdl_request,MDL_key::GLOBAL, "", "", MDL_SHARED, MDL_EXPLICIT); 
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5.     //Global_read_lock::make_global_read_lock_block_commit 
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7.     MDL_REQUEST_INIT(&mdl_request,MDL_key::COMMIT, "", "", MDL_SHARED, MDL_EXPLICIT);  
    8.

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鎖來(lái)說(shuō)，IX鎖和S鎖是不兼容的。 

4、--slave_preserve_commit_order      

 
 
 
 
    
  
  
  
  
  1. For multi-threaded slaves, enabling this variable ensures that  
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3.      transactions are externalized on theslave in the same order as they appear 
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5.      in the slave's relay log.  
    6.

slave_preserve_commit_order=1時(shí)，relay-log中事務(wù)的提交順序會(huì)嚴(yán)格按照在relay-log中出現(xiàn)的順序提交。 

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

  1. 事務(wù)A、B可以并行復(fù)制，relay-log中A在前，slave_preserve_commit_order=1
  2. 從庫(kù)回放時(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語(yǔ)句獲得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)就簡(jiǎn)單多了。重現(xiàn)這個(gè)死鎖步驟主要是2步： 

1、在主庫(kù)構(gòu)造并行復(fù)制的事務(wù)，利用debug_sync          

 
 
 
 
    
  
  
  
  
  1. session 1 
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3. SET DEBUG_SYNC='waiting_in_the_middle_of_flush_stage SIGNAL s1 WAIT_FOR f'; 
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5. insert into test.test values(13);//事務(wù)A 
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7.   
    8. 
  
  
  
  
  8.  
    9. 
  
  
  
  
  9. //session 2 
    10. 
  
  
  
  
  10.  
    11. 
  
  
  
  
  11. SET DEBUG_SYNC= 'now WAIT_FOR s1';  
    12. 
  
  
  
  
  12.  
    13. 
  
  
  
  
  13. SET DEBUG_SYNC= 'bgc_after_enrolling_for_flush_stage SIGNAL f';    
    14. 
  
  
  
  
  14.  
    15. 
  
  
  
  
  15. insert into test.test values(16);//事務(wù)B  
    16.

2、從庫(kù)執(zhí)行，修改源代碼，在關(guān)鍵地方sleep若干時(shí)間，控制并行復(fù)制的worker的執(zhí)行并留出足夠時(shí)間執(zhí)行flush tables with read lock 

修改點(diǎn)如下：       

 
 
 
 
    
  
  
  
  
  1. //Xid_apply_log_event::do_apply_event_worker 
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3.          if(w->id==0) 
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5.          { 
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7.              std::cout<<"before commit"<
  
  
  
  
  8.  
    9. 
  
  
  
  
  9.              sleep(20); 
    10. 
  
  
  
  
  10.  
    11. 
  
  
  
  
  11.          } 
    12. 
  
  
  
  
  12.  
    13. 
  
  
  
  
  13.          //pop_jobs_item 
    14. 
  
  
  
  
  14.  
    15. 
  
  
  
  
  15.          if(worker->id==0)     
    16. 
  
  
  
  
  16.  
    17. 
  
  
  
  
  17.              sleep(20);   
    18.

開(kāi)啟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)重新開(kāi)啟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í)行語(yǔ)句的線程；但是這個(gè)參數(shù)對(duì)第二種死鎖狀態(tài)則無(wú)能為力了，因?yàn)閤trabackup選擇殺掉的線程時(shí)，會(huì)過(guò)濾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)閉從庫(kù)binlog的順序提交，關(guān)閉這個(gè)參數(shù)只是影響并行復(fù)制的事務(wù)在從庫(kù)的提交順序，對(duì)最終的數(shù)據(jù)一致性并無(wú)影響，所以如果無(wú)特別要求從庫(kù)的binlog順序必須與主庫(kù)保持一致，可以設(shè)置slave_preserve_commit_order=0避免這個(gè)死鎖的出現(xiàn)。 

關(guān)于xtrabackup  kill-long-query-type參數(shù) 

首先說(shuō)下```kill-long-queries-timeout,kill-long-query-type```參數(shù)，文檔介紹如下  

 
 
 
 
    
  
  
  
  
  1. --KILL-LONG-QUERY-TYPE=ALL|SELECT 
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3.      This option specifies which types of queries should be killed to  
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5.      unblock the global lock. Default is “all”. 
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7. --KILL-LONG-QUERIES-TIMEOUT=SECONDS** 
    8. 
  
  
  
  
  8.  
    9. 
  
  
  
  
  9.      This option specifies the number of seconds innobackupex waits  
    10. 
  
  
  
  
  10.  
    11. 
  
  
  
  
  11.      between starting FLUSH TABLES WITH READ LOCK and killing those queries  
    12. 
  
  
  
  
  12.  
    13. 
  
  
  
  
  13.      that block it. Default is 0 seconds, which means innobackupex will not  
    14. 
  
  
  
  
  14.  
    15. 
  
  
  
  
  15.      attempt to kill any queries. In order to use this option xtrabackup  
    16. 
  
  
  
  
  16.  
    17. 
  
  
  
  
  17.      user should have PROCESS and SUPER privileges.Where supported (Percona  
    18. 
  
  
  
  
  18.  
    19. 
  
  
  
  
  19.      Server 5.6+) xtrabackup will automatically use Backup Locks as a  
    20. 
  
  
  
  
  20.  
    21. 
  
  
  
  
  21.      lightweight alternative to FLUSH TABLES WITH READ LOCK to copy non- 
    22. 
  
  
  
  
  22.  
    23. 
  
  
  
  
  23.      InnoDB data to avoid blocking DML queries that modify InnoDB tables.  
    24.

參數(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ù)庫(kù)執(zhí)行show full processlist，如果TIME超過(guò)kill-long-queries-timeout，會(huì)kill掉線程，kill-long-query-type設(shè)置可以kill掉的SQL類型。 

官方文檔介紹kill-long-query-type默認(rèn)值時(shí)all，也就是所有語(yǔ)句都會(huì)kill掉。但在使用中發(fā)現(xiàn)，只設(shè)置kill-long-queries-timeout，未設(shè)置kill-long-query-type時(shí)，參數(shù)沒(méi)起作用！最后查閱xtrabackup代碼，如下：   

 
 
 
 
    
  
  
  
  
  1. {"kill-long-query-type", OPT_KILL_LONG_QUERY_TYPE, 
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3.    "This option specifies which types of queries should be killed to " 
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5.    "unblock the global lock. Default is \"all\".", 
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7.    (uchar*) &opt_ibx_kill_long_query_type, 
    8. 
  
  
  
  
  8.  
    9. 
  
  
  
  
  9.    (uchar*) &opt_ibx_kill_long_query_type, &query_type_typelib, 
    10. 
  
  
  
  
  10.  
    11. 
  
  
  
  
  11.    GET_ENUM, REQUIRED_ARG, QUERY_TYPE_SELECT, 0, 0, 0, 0, 0}  
    12.

心中一萬(wàn)頭草泥馬，也許只是筆誤，但也太坑爹了！所以使用kill-long-query-type時(shí)一定要自己指定好類型！ 

總結(jié) 

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

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


分享文章：由FTWRL導(dǎo)致的MySQL從庫(kù)死鎖分析及參數(shù)深究
標(biāo)題URL：http://www.dlmjj.cn/article/cohccdi.html