141
一個參數救活被hang住的數據庫！
作者介紹
賀春暘，普惠金融MySQL專家，《MySQL管理之道》第一版、第二版作者。曾任職於中國移動飛信、機鋒安卓市場，擁有豐富的數據庫管理經驗。目前致力於MySQL、Linux等開源技術的研究。
現象
開年頭一天上班，開發說程序連接不上數據庫了，程序伴隨著有大量的update鎖超時，試著引導他們用SQLYOG客戶端連接均無問題，然後查看監控圖發現有大量的鎖，如下圖：
排查
-
數據庫版本為：10.0.28-MariaDB-enterprise - MariaDB Enterprise Certified Binary
-
DELL R730XD 128G內存（BP 70G）/14塊SAS 15000轉RAID10
-
Update操作/秒30-50左右 innodb_lock_wait_timeout鎖等待超時設置為10秒
在MySQL中information_schema庫下有三個經典的數據字典表：INNODB_LOCK_WAITS、PROCESSLIST、INNODB_TRX，三者可以結合起來，就能夠查到相對比較完整的阻塞信息和事務的情況。
1、通過以下SQL語句查看
SELECT
a.trx_id,
trx_state,
trx_started,
b.id AS thread_id,
b.info,
b.user,
b.host,
b.db,
b.command,
b.state
FROM
information_schema.`INNODB_TRX` a,
information_schema.`PROCESSLIST` b
WHERE a.trx_mysql_thread_id = b.id
ORDER BY a.trx_started;
查詢結果如下：
請注意紅色標識的，trx_state事務狀態是RUNNING，但command那裏查不到正在執行的SQL，顯示的是Sleep狀態。
2、通過以下SQL語句查看
SHOW ENGINE INNODB STATUS\G
查詢結果如下：
請注意紅色標識，事務ID和線程ID的狀態為ACTIVE且運行了563秒，憑著以往處理故障的經驗，這是N多條未提交事務的SQL引起的。
分析
當時慢查詢日誌裏並沒有記錄慢SQL，線上設置的為1秒，詢問開發是哪個SQL被鎖了，也不清楚，說是通過框架生成的SQL語句，不好排查。
然後我們開啟了general_log抓包，得到了很多簡單的update，每次更新為1條記錄，例如update t1 set name='aa' where id=XX，通過explain查看執行計劃，where後麵的字段都用到了索引，正常情況下執行這種SQL隻需零點幾毫秒的時間，但由於會話A對該記錄更改未提交，會話B又對該記錄進行更改，此時就會出現鎖等待，直到超過了innodb_lock_wait_timeout參數設置的閾值。
在並發訪問比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會占用大量的連接數資源，造成嚴重的性能問題，甚至拖跨數據庫。最終我們斷定為開發的代碼裏應忘加了commit提交事務的操作，導致這一慘案的發生，可參考下麵的重現操作。
前端應用JAVA Mybatis連接池一直不釋放，積壓過多的請求無法被處理，最終呈現給開發的現象是數據庫又掛了。通俗來講相當於在銀行裏辦理業務，一個人辦理不完，就得排隊等待，越排越多，最終造成銀行裏人流混亂。
重現
MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> update t5 set name = 'aa' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
記住！千萬別COMMIT！等待一會（抽一根煙的時間），然後重複執行上述命令，即可查看到跟我這裏的截圖完全一致。
解決方案
讓開發再排查代碼，已經不太現實了，時間不等人，業務不等人，不能再增加用戶的投訴量了，DBA首先要保證的是數據庫別跑掛了，先恢複！
下麵介紹給大家一個參數innodb_kill_idle_transaction，意思為當一個事務長時間未提交，那麼這個連接就不能關閉，內存就不釋放，並發一大，導致DB連接數增多，就會對性能產生影響。
默認是0秒，你可以根據自己的情況設定閾值。超過這個閾值，服務端自動殺死未提交的空閑事務。
下麵的截圖展示了這一過程：
從圖中結果上可以看出，當未提交的事務超過空閑時間30秒時，被後台進程自動KILL掉，執行COMMIT提交後，連接被強行斷開。
設置這個參數後隻針對新的連接有效，正在執行的連接無效，固我們讓運維又依次重啟前端應用後，數據庫終於複活成功！
原文發布時間為：2017-02-08
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-15 19:24:19