最近線上執行備份的從庫時出現復制卡死現象，分析以后發現是兩個死鎖，show full processlist的狀態如圖1所示，其中，數據庫版本是官方5.7.18版本，我們內部做了些許修改，但與此次死鎖無關。

圖一

先說一下結論，圖一中：

162線程是執行innobackup執行的flush tables with read lock;

144是SQL線程，并行復制中的Coordinator線程；

145/146是并行復制的worker線程，145/146worker線程隊列中的事務可以并行執行。

144Coordinator線程分發relaylog中事務時發現這個事務不能執行，要等待前面的事務完成提交，所以處于waiting for dependent transaction to commit的狀態。145/146線程和備份線程162形成死鎖，145線程等待162線程 global read lock 釋放，162線程占有MDL::global read lock 全局讀鎖，申請全局commit lock的時候阻塞等待146線程，146線程占有MDL:: commit lock，因為從庫設置slave_preserve_commit_order=1，保證從庫binlog提交順序，而146線程執行事務對應的binlog靠后面，所以等待145的事務提交。最終形成了145->162->146->145的死循環，形成死鎖。

同樣的，圖二中：

183是備份程序執行的flush tables with read lock;

165是SQL線程，并行復制的Coordinator線程；

166/167是并行復制的worker線程。

圖二

165Coordinator線程分發的事務還不能執行，進入waiting for dependent transaction to commit的狀態，183、166、167三個線程形成死鎖，183占有全局讀鎖，獲取全局commit鎖的時候進入阻塞，等待167釋放事務涉及到表的commit鎖；166，167的事務可以并行復制，167占有表級commit鎖，但是事務對應的binlog在后面，阻塞等待166先提交進入waiting for preceding transaction to commit的狀態；166線程事務執行時提交要獲得表級commit鎖，但已經被183占有，所以阻塞等待。這樣形成了183->167->166->183的死鎖。

三個線程相互形成死鎖，在我的經驗中還是很少見的，又因為涉及的MDL鎖是服務層的鎖，死鎖檢測也不會起作用。

死鎖原因分析

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、事務執行中涉及兩個鎖

在所有更新數據的代碼路徑里，除了必須的鎖外，還會額外請求MDL_key::GLOBAL鎖的MDL_INTENTION_EXCLUSIVE鎖；在事務提交前，會先請求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中事務的提交順序會嚴格按照在relay-log中出現的順序提交。

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

事務A、B可以并行復制，relay-log中A在前，slave_preserve_commit_order=1

從庫回放時B事務執行較快，先執行到commit，獲得commit鎖，并進入waiting for preceding transaction to commit的狀態

執行flush tables with read lock,進入waiting for commit的狀態

事務A執行。事務A如果在FTWRL語句獲得global read lock鎖之后執行，那么事務A就進入waiting for global read lock的狀態，即第一種死鎖；如果事務A在FTWRL獲得global read lock之前執行，同時FTWRL獲得global commit鎖之后應用Xid_event提交事務，則進入 waiting for the commit lock的狀態，即第二種死鎖。

復現

理解了死鎖出現的原因后，重現就簡單多了。重現這個死鎖步驟主要是2步：

1、在主庫構造并行復制的事務，利用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);//事務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);//事務B

2、從庫執行，修改源代碼，在關鍵地方sleep若干時間，控制并行復制的worker的執行并留出足夠時間執行flush tables with read lock

修改點如下：

//Xid_apply_log_event::do_apply_event_worker

if(w->id==0)

{

std::cout<

sleep(20);

}

//pop_jobs_item

if(worker->id==0)

sleep(20);

開啟slave以后，觀察show full processlist和輸出日志，在其中一個worker出現wait for preceding transaction to commit以后，執行 ftwrl，出現圖1的死鎖；wait for preceding transaction to commit以后，出現日志before commit之后，執行 ftwrl，出現圖2的死鎖。

如何解決？

出現死鎖以后如果不人工干預，IO線程正常，但是SQL線程一直卡住，一般需要等待lock-wait-timeout時間，這個值我們線上設置1800秒，所以這個死鎖會產生很大影響。

那么如何解決呢？kill ！kill哪個線程呢？

對圖1的死鎖，146處于wait for preceding transaction狀態的worker線程實際處于mysql_cond_wait的狀態，kill不起作用，所以只能kill 145線程或者備份線程，如果kill145worker線程，整個并行復制就報錯結束，show slave status顯示SQL異常退出，之后需要手動重新開啟sql線程，所以最好的辦法就是kill執行flush tables with read lock的線程，代價最小。

至于圖2的死鎖，則只能kill掉執行flush tables with read lock的線程。所以出現上述死鎖時，kill執行flush tables with read lock的備份線程就恢復正常，之后擇機重新執行備份即可。

如何避免？

設置xtrabackup的kill-long-queries-timeout參數可以避免第一種死鎖的出現，其實不算避免，只是出現以后xtrabackup會殺掉阻塞的執行語句的線程；但是這個參數對第二種死鎖狀態則無能為力了，因為xtrabackup選擇殺掉的線程時，會過濾Info！=NULL。

另外還有個參數safe-slave-backup，執行備份的時候加上這個參數會停掉SQL線程，這樣也肯定不會出現這個死鎖，只是停掉SQL未免太暴力了，個人不提倡這樣做。

可以設置slave_preserve_commit_order=0關閉從庫binlog的順序提交，關閉這個參數只是影響并行復制的事務在從庫的提交順序，對最終的數據一致性并無影響，所以如果無特別要求從庫的binlog順序必須與主庫保持一致，可以設置slave_preserve_commit_order=0避免這個死鎖的出現。

關于xtrabackup kill-long-query-type參數

首先說下```kill-long-queries-timeout,kill-long-query-type```參數，文檔介紹如下

--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.

參數的作用的就是在Xtrabackup執行FLUSH TABLES WITH READ LOCK以后，獲得全局讀鎖時，如果有正在執行的事務會阻塞等待，kill-long-queries-timeout參數不為0時，xtrabackup內部創建一個線程，連接到數據庫執行show full processlist，如果TIME超過kill-long-queries-timeout，會kill掉線程，kill-long-query-type設置可以kill掉的SQL類型。

官方文檔介紹kill-long-query-type默認值時all，也就是所有語句都會kill掉。但在使用中發現，只設置kill-long-queries-timeout，未設置kill-long-query-type時，參數沒起作用！最后查閱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時一定要自己指定好類型！

總結

總結

以上是生活随笔為你收集整理的mysql死锁释放时间参数_由FTWRL导致的MySQL从库死锁分析及参数深究的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。