MySQL怎么一個(gè)殺掉數(shù)據(jù)庫(kù)空閑事務(wù)

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

　　我們經(jīng)常遇到一個(gè)情況，就是網(wǎng)絡(luò)斷開(kāi)或程序Bug導(dǎo)致COMMIT/ROLLBACK語(yǔ)句沒(méi)有傳到數(shù)據(jù)庫(kù)，也沒(méi)有釋放線程，但是線上事務(wù)鎖定等待嚴(yán)重，連接數(shù)暴漲，尤其在測(cè)試庫(kù)這種情況很多，線上也偶有發(fā)生，于是想為MySQL增加一個(gè)殺掉空閑事務(wù)的功能。下面億速云小編來(lái)講解下MySQ如何一個(gè)殺掉數(shù)據(jù)庫(kù)空閑事務(wù)?

　　MySQ如何一個(gè)殺掉數(shù)據(jù)庫(kù)空閑事務(wù)

　　通過(guò)MySQL Server層有很多不確定因素，最保險(xiǎn)還是在存儲(chǔ)引擎層實(shí)現(xiàn)，我們用的幾乎都是InnoDB/XtraDB，所以就基于Percona來(lái)修改了，Oracle版的MySQL也可以照著修改。

　　需求：

　　1. 一個(gè)事務(wù)啟動(dòng)，如果事務(wù)內(nèi)最后一個(gè)語(yǔ)句執(zhí)行完超過(guò)一個(gè)時(shí)間(innodb_idle_trx_timeout)，就應(yīng)該關(guān)閉鏈接。

　　2. 如果事務(wù)是純讀事務(wù)，因?yàn)椴患渔i，所以無(wú)害，不需要關(guān)閉，保持即可。

　　雖然這個(gè)思路被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.”的問(wèn)題，但是我們確實(shí)有場(chǎng)景SELECT是絕對(duì)不能kill的，除非之后的INSERT/UPDATE/DELETE發(fā)生了，所以我根據(jù) 我們的業(yè)務(wù)特點(diǎn)來(lái)修改。

　　跟Percona的Yasufumi Kinoshita和Alexey Kopytov提出過(guò)純SELECT事務(wù)不應(yīng)被kill，但通過(guò)一個(gè)參數(shù)控制的方案還沒(méi)有被Alexey Kopytov接受，作為通用處理我提出了用兩個(gè)變量分別控制純讀事務(wù)的空閑超時(shí)時(shí)間和有鎖事務(wù)的空閑超時(shí)時(shí)間，還在等待Percona的回復(fù)，因?yàn)檫@個(gè) 方案還在測(cè)試，就先不開(kāi)放修改了，當(dāng)然如果你很熟悉MYSQL源碼，我提出這個(gè)思路你肯定知道怎么分成這兩個(gè)參數(shù)控制了。

　　根據(jù)這兩個(gè)需 求我們來(lái)設(shè)計(jì)方法，首先想到這個(gè)功能肯定是放在InnoDB Master Thread最方便，Master Thread每秒調(diào)度一次，可以順便檢查空閑事務(wù)，然后關(guān)閉，因?yàn)樵谑聞?wù)中操作trx->mysql_thd并不安全，所以一般來(lái)說(shuō)最好在 InnoDB層換成Thread ID操作，并且InnoDB中除了ha_innodb.cc，其他地方不能飲用THD，所以Master Thread中需要的線程數(shù)值，都需要在ha_innodb中計(jì)算好傳遞整型或布爾型返回值給master thread調(diào)用。

　　首先，我們要增加一個(gè)參數(shù)：idle_trx_timeout，它表示事務(wù)多久沒(méi)有下一條語(yǔ)句發(fā)生就超時(shí)關(guān)閉。

　　在storage/innodb_plugin/srv/srv0srv.c的“/* plugin options */”注釋下增加如下代碼注冊(cè)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); # 從當(dāng)前事務(wù)列表里獲取第一個(gè)事務(wù)

　　while (trx) { # 依次循環(huán)每個(gè)事務(wù)進(jìn)行檢查

　　if (trx->conc_state == TRX_ACTIVE

　　&& trx->mysql_thd

　　&& innobase_thd_is_idle(trx->mysql_thd)) { # 如果事務(wù)還活著并且它的狀態(tài)時(shí)空閑的

　　ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd); # 獲取線程最后一個(gè)語(yǔ)句的開(kāi)始時(shí)間

　　ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd); #獲取線程ID，因?yàn)榇鎯?chǔ)引擎內(nèi)直接操作THD不安全

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

　　trx->idle_start = now; # 更新事務(wù)的空閑起始時(shí)間

　　trx->last_stmt_start = start_time; # 更新事務(wù)的最后語(yǔ)句起始時(shí)間

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

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

　　/* kill the session */

　　mutex_exit(&kernel_mutex);

　　thd_kill(thd_id); # 殺鏈接

　　goto rescan_idle;

　　}

　　}

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

　　}

　　mutex_exit(&kernel_mutex);

　　}

　　代碼往下找在innobase_system_variables結(jié)構(gòu)體內(nèi)加上：

　　MySQ如何一個(gè)殺掉數(shù)據(jù)庫(kù)空閑事務(wù)

　　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); # 從當(dāng)前事務(wù)列表里獲取第一個(gè)事務(wù)

　　while (trx) { # 依次循環(huán)每個(gè)事務(wù)進(jìn)行檢查

　　if (trx->conc_state == TRX_ACTIVE

　　&& trx->mysql_thd

　　&& innobase_thd_is_idle(trx->mysql_thd)) { # 如果事務(wù)還活著并且它的狀態(tài)時(shí)空閑的

　　ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd); # 獲取線程最后一個(gè)語(yǔ)句的開(kāi)始時(shí)間

　　ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd); #獲取線程ID，因?yàn)榇鎯?chǔ)引擎內(nèi)直接操作THD不安全

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

　　trx->idle_start = now; # 更新事務(wù)的空閑起始時(shí)間

　　trx->last_stmt_start = start_time; # 更新事務(wù)的最后語(yǔ)句起始時(shí)間

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

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

　　/* kill the session */

　　mutex_exit(&kernel_mutex);

　　thd_kill(thd_id); # 殺鏈接

　　goto rescan_idle;

　　}

　　}

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

　　}

　　mutex_exit(&kernel_mutex);

　　}

　　有了這個(gè)變量，我們需要在Master Thread(storage/innodb_plugin/srv/srv0srv.c )中執(zhí)行檢測(cè)函數(shù)查找空閑事務(wù)。在loop循環(huán)的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); # 從當(dāng)前事務(wù)列表里獲取第一個(gè)事務(wù)

　　while (trx) { # 依次循環(huán)每個(gè)事務(wù)進(jìn)行檢查

　　if (trx->conc_state == TRX_ACTIVE

　　&& trx->mysql_thd

　　&& innobase_thd_is_idle(trx->mysql_thd)) { # 如果事務(wù)還活著并且它的狀態(tài)時(shí)空閑的

　　ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd); # 獲取線程最后一個(gè)語(yǔ)句的開(kāi)始時(shí)間

　　ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd); #獲取線程ID，因?yàn)榇鎯?chǔ)引擎內(nèi)直接操作THD不安全

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

　　trx->idle_start = now; # 更新事務(wù)的空閑起始時(shí)間

　　trx->last_stmt_start = start_time; # 更新事務(wù)的最后語(yǔ)句起始時(shí)間

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

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

　　/* kill the session */

　　mutex_exit(&kernel_mutex);

　　thd_kill(thd_id); # 殺鏈接

　　goto rescan_idle;

　　}

　　}

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

　　}

　　mutex_exit(&kernel_mutex);

　　}

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

看完了這篇文章，相信你對(duì)“MySQL怎么一個(gè)殺掉數(shù)據(jù)庫(kù)空閑事務(wù)”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！