日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

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

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
由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鎖。    

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

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

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

  1. 事務(wù)A、B可以并行復(fù)制，relay-log中A在前，slave_preserve_commit_order=1
  2. 從庫回放時B事務(wù)執(zhí)行較快，先執(zhí)行到commit，獲得commit鎖，并進入waiting for   preceding transaction to commit的狀態(tài)
  3. 執(zhí)行flush tables with read lock,進入waiting  for commit的狀態(tài)
  4. 事務(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          

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

修改點如下：       

 
 
 
 
    
  
  
  
  
  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.

開啟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ù)，文檔介紹如下  

 
 
 
 
    
  
  
  
  
  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以后，獲得全局讀鎖時，如果有正在執(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代碼，如下：   

 
 
 
 
    
  
  
  
  
  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.

心中一萬頭草泥馬，也許只是筆誤，但也太坑爹了！所以使用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)上述的兩個死鎖，有興趣的可以驗證下。 


分享文章：由FTWRL導(dǎo)致的MySQL從庫死鎖分析及參數(shù)深究
本文來源：http://m.5511xx.com/article/cohccdi.html