MySQL怎么一個殺掉數據庫空閑事務

小編給大家分享一下MySQL怎么一個殺掉數據庫空閑事務，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

　　我們經常遇到一個情況，就是網絡斷開或程序Bug導致COMMIT/ROLLBACK語句沒有傳到數據庫，也沒有釋放線程，但是線上事務鎖定等待嚴重，連接數暴漲，尤其在測試庫這種情況很多，線上也偶有發生，于是想為MySQL增加一個殺掉空閑事務的功能。下面億速云小編來講解下MySQ如何一個殺掉數據庫空閑事務?

　　MySQ如何一個殺掉數據庫空閑事務

　　通過MySQL Server層有很多不確定因素，最保險還是在存儲引擎層實現，我們用的幾乎都是InnoDB/XtraDB，所以就基于Percona來修改了，Oracle版的MySQL也可以照著修改。

　　需求：

　　1. 一個事務啟動，如果事務內最后一個語句執行完超過一個時間(innodb_idle_trx_timeout)，就應該關閉鏈接。

　　2. 如果事務是純讀事務，因為不加鎖，所以無害，不需要關閉，保持即可。

　　雖然這個思路被Percona的指出Alexey Kopytov可能存在“Even though SELECT queries do not place row locks by default (there are exceptions), they can still block undo log records from being purged.”的問題，但是我們確實有場景SELECT是絕對不能kill的，除非之后的INSERT/UPDATE/DELETE發生了，所以我根據 我們的業務特點來修改。

　　跟Percona的Yasufumi Kinoshita和Alexey Kopytov提出過純SELECT事務不應被kill，但通過一個參數控制的方案還沒有被Alexey Kopytov接受，作為通用處理我提出了用兩個變量分別控制純讀事務的空閑超時時間和有鎖事務的空閑超時時間，還在等待Percona的回復，因為這個 方案還在測試，就先不開放修改了，當然如果你很熟悉MYSQL源碼，我提出這個思路你肯定知道怎么分成這兩個參數控制了。

　　根據這兩個需 求我們來設計方法，首先想到這個功能肯定是放在InnoDB Master Thread最方便，Master Thread每秒調度一次，可以順便檢查空閑事務，然后關閉，因為在事務中操作trx->mysql_thd并不安全，所以一般來說最好在 InnoDB層換成Thread ID操作，并且InnoDB中除了ha_innodb.cc，其他地方不能飲用THD，所以Master Thread中需要的線程數值，都需要在ha_innodb中計算好傳遞整型或布爾型返回值給master thread調用。

　　首先，我們要增加一個參數：idle_trx_timeout，它表示事務多久沒有下一條語句發生就超時關閉。

　　在storage/innodb_plugin/srv/srv0srv.c的“/* plugin options */”注釋下增加如下代碼注冊idle_trx_timeout變量。

　　if (srv_idle_trx_timeout && trx_sys) {

　　trx_t* trx;

　　time_t now;

　　rescan_idle:

　　now = time(NULL);

　　mutex_enter(&kernel_mutex);

　　trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list); # 從當前事務列表里獲取第一個事務

　　while (trx) { # 依次循環每個事務進行檢查

　　if (trx->conc_state == TRX_ACTIVE

　　&& trx->mysql_thd

　　&& innobase_thd_is_idle(trx->mysql_thd)) { # 如果事務還活著并且它的狀態時空閑的

　　ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd); # 獲取線程最后一個語句的開始時間

　　ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd); #獲取線程ID，因為存儲引擎內直接操作THD不安全

　　if (trx->last_stmt_start != start_time) { # 如果事務最后語句起始時間不等于線程最后語句起始時間說明事務是新起的

　　trx->idle_start = now; # 更新事務的空閑起始時間

　　trx->last_stmt_start = start_time; # 更新事務的最后語句起始時間

　　} else if (difftime(now, trx->idle_start) # 如果事務不是新起的，已經執行了一部分則判斷空閑時間有多長了

　　> srv_idle_trx_timeout) { # 如果空閑時間超過閾值則殺掉鏈接

　　/* kill the session */

　　mutex_exit(&kernel_mutex);

　　thd_kill(thd_id); # 殺鏈接

　　goto rescan_idle;

　　}

　　}

　　trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); # 檢查下一個事務

　　}

　　mutex_exit(&kernel_mutex);

　　}

　　代碼往下找在innobase_system_variables結構體內加上：

　　MySQ如何一個殺掉數據庫空閑事務

　　if (srv_idle_trx_timeout && trx_sys) {

　　trx_t* trx;

　　time_t now;

　　rescan_idle:

　　now = time(NULL);

　　mutex_enter(&kernel_mutex);

　　trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list); # 從當前事務列表里獲取第一個事務

　　while (trx) { # 依次循環每個事務進行檢查

　　if (trx->conc_state == TRX_ACTIVE

　　&& trx->mysql_thd

　　&& innobase_thd_is_idle(trx->mysql_thd)) { # 如果事務還活著并且它的狀態時空閑的

　　ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd); # 獲取線程最后一個語句的開始時間

　　ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd); #獲取線程ID，因為存儲引擎內直接操作THD不安全

　　if (trx->last_stmt_start != start_time) { # 如果事務最后語句起始時間不等于線程最后語句起始時間說明事務是新起的

　　trx->idle_start = now; # 更新事務的空閑起始時間

　　trx->last_stmt_start = start_time; # 更新事務的最后語句起始時間

　　} else if (difftime(now, trx->idle_start) # 如果事務不是新起的，已經執行了一部分則判斷空閑時間有多長了

　　> srv_idle_trx_timeout) { # 如果空閑時間超過閾值則殺掉鏈接

　　/* kill the session */

　　mutex_exit(&kernel_mutex);

　　thd_kill(thd_id); # 殺鏈接

　　goto rescan_idle;

　　}

　　}

　　trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); # 檢查下一個事務

　　}

　　mutex_exit(&kernel_mutex);

　　}

　　有了這個變量，我們需要在Master Thread(storage/innodb_plugin/srv/srv0srv.c )中執行檢測函數查找空閑事務。在loop循環的if (sync_array_print_long_waits(&waiter, &sema)判斷后加上這段判斷

　　if (srv_idle_trx_timeout && trx_sys) {

　　trx_t* trx;

　　time_t now;

　　rescan_idle:

　　now = time(NULL);

　　mutex_enter(&kernel_mutex);

　　trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list); # 從當前事務列表里獲取第一個事務

　　while (trx) { # 依次循環每個事務進行檢查

　　if (trx->conc_state == TRX_ACTIVE

　　&& trx->mysql_thd

　　&& innobase_thd_is_idle(trx->mysql_thd)) { # 如果事務還活著并且它的狀態時空閑的

　　ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd); # 獲取線程最后一個語句的開始時間

　　ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd); #獲取線程ID，因為存儲引擎內直接操作THD不安全

　　if (trx->last_stmt_start != start_time) { # 如果事務最后語句起始時間不等于線程最后語句起始時間說明事務是新起的

　　trx->idle_start = now; # 更新事務的空閑起始時間

　　trx->last_stmt_start = start_time; # 更新事務的最后語句起始時間

　　} else if (difftime(now, trx->idle_start) # 如果事務不是新起的，已經執行了一部分則判斷空閑時間有多長了

　　> srv_idle_trx_timeout) { # 如果空閑時間超過閾值則殺掉鏈接

　　/* kill the session */

　　mutex_exit(&kernel_mutex);

　　thd_kill(thd_id); # 殺鏈接

　　goto rescan_idle;

　　}

　　}

　　trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); # 檢查下一個事務

　　}

　　mutex_exit(&kernel_mutex);

　　}

　　其中trx中的變量是新加的，在storage/innodb_plugin/include/trx0trx.h的trx_truct加上需要的變量。

看完了這篇文章，相信你對“MySQL怎么一個殺掉數據庫空閑事務”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！