最近遇到應用頻繁的響應緩慢，無法正常訪問。幫忙一起定位原因，最後定位到的問題說起來真的是很小的細節問題，但是就是這些小細節導致了服務不穩定，真是細節決定成敗。這裡嘗試著來分享下，希望對大家有所幫助。
問題 1：佔著茅坑不拉屎
遇到問題首先要看的還是伺服器錯誤日誌。
錯誤日誌中看到頻繁有這樣的一個異常報錯：Error: ER_CON_COUNT_ERROR: Too many connections。這個報錯是因為資料庫的所有連線被客戶端都佔有了，沒有空閒的連線可以使用。MySQL 預設的最大併發連線數是 100，然而我們的應用這邊最多可能的併發也就 30~40 個任務，怎麼也不太可能報這樣的錯誤，推測很有可能是程式碼裡面建立連線後沒有及時的進行關閉。於是我們重點看了下執行 SQL 部分的程式碼，大概是下面這樣（使用了node-mysql庫）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
var mysql = require('mysql'); // 建立連線池 var pool = mysql.createPool({ host: 'host', user: 'user', password: 'password', database: 'db' }); exports.query = function(sql, cb) { // 從池子裡面取一個可用連線 pool.getConnection(function(err, connection) { if (err) throw err; // 執行sql connection.query(sql, function(err, rows, fields) { if (err) { return cosole.error(err); } cb(rows); }); // 釋放此連線 connection.release(); }); }; |
剛開始我還真沒看出來有什麼問題，後來仔細讀了 node-mysql 的文件及這個 issue，終於發現了我們的寫法是有問題的。
再次看看上面的程式碼，pool.getConnection 後我們執行 connection.query，然後沒等 SQL 執行完，直接呼叫了 connection.release，由於 JavaScript 的非同步特性（雖然 SQL 可能很快就執行完，但是我們也必須在 connection.query 的 callback 裡面才明確的知道 SQL 執行完了），這個時候此次連線是不會被釋放的！程式碼裡面所有的 SQL 執行都呼叫到這個函式，這意味著我們佔著一堆資料庫連線不釋放，這時不斷的有其他資料庫連線過來，直接導致其他連線被阻塞，丟擲連線太多的異常。這真是典型的“拉完不及時讓坑，佔著茅坑不拉屎”的行為。所以，我們一定要在 SQL 執行完成後就將連線及時進行釋放。因為 SQL 執行一般是非常快的（零點幾秒），如果我們執行完後不釋放，在同一時間產生很多資料庫連線時很有可能導致連線被阻塞，產生連線過多的異常。於是我們對程式碼進行了如下修改：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
exports.query = function(sql, cb) { // 從池子裡面取一個可用連線 pool.getConnection(function(err, connection) { if (err) throw err; // 執行sql connection.query(sql, function(err, rows, fields) { // 釋放連線（一定要在錯誤處理前，不然出錯的時候也會導致該連線得不到釋放） connection.release(); if (err) { return cosole.error(err); } cb(rows); }); }); }; |
也可以用更簡單的寫法 pool.query，這個方法內部會在合適的時機來釋放連線，不用我們手動操作。
完成此次修改後，這個異常沒有再復現，但是響應緩慢的情況依然沒有得到緩解。
問題 2：一條 UPDATE 引發的血案
我們再次檢視了錯誤日誌，發現了另一個異常報錯：Error: ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction。這個報錯就非常令人費解了，原因是鎖等待超時，當前事務在等待其它事務釋放鎖資源造成的。
我們先大概說下什麼是事務（transaction）。事務應該具有 4 個屬性：
- 原子性（事務作為整體執行，操作要麼全部執行、要麼全部不執行）
- 一致性（事務應該確保資料庫狀態從一個一致狀態轉變為另一個一致狀態）
- 隔離性（多個事務併發執行時，一個事務執行不影響其他事務執行）
- 永續性（事務提交後，對資料庫修改應該永久儲存在資料庫中）
對於隔離性，還會分出多個隔離級別：
| 隔離級別 | 髒讀 | 不可重複讀 | 幻讀 |
|---|---|---|---|
| 未提交讀 | 可能 | 可能 | 可能 |
| 已提交讀 | 不可能 | 可能 | 可能 |
| 可重複讀 | 不可能 | 不可能 | 可能 |
| 序列化 | 不可能 | 不可能 | 不可能 |
- 髒讀（Dirty Read）：A 事務讀到 B 事務未提交的修改。
- 不可重複讀（NonRepeatable Read）：A 事務還沒有結束時，B 事務也訪問同一資料。在 A 事務的兩次讀取之間，由於 B 事務的修改，A 事務兩次讀到的資料可能是不一樣的。
- 幻讀（Phantom Read）：A 事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，B 事務也修改這個表中的資料，這種修改是向表中插入一行新資料。操作 A 事務的使用者發現表中出現了 B 事務插入的行，就好象發生了幻覺一樣。
MySQL 預設的級別是 REPEATABLE READ（可重複讀），這表示在 MySQL 的預設情況下，“髒讀”、“不可重複讀”是不會發生的。這就需要在更新的時候進行必要的鎖定（InnoDB 是採用行級鎖的方式），從而保證一致性。需要注意的是 InnoDB 的行鎖是通過給索引上的索引項加鎖來實現的，這個特點意味著：只有通過索引條件檢索資料，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖！
我們資料庫表是 InnoDB 引擎的表，而 MySQL 的 InnoDB 引擎是一個支援事務的引擎，其預設操作模式是 autocommit 自動提交模式。什麼意思呢？除非我們顯式地開始一個事務，否則每個查詢都被當做一個單獨的事務自動執行。
回到上面的報錯，錯誤日誌裡丟擲異常時執行的 SQL 語句，都是類似這樣的一條 UPDATE 語句：update testScore set status=1,executionId='946012' where token='f7900c40-8f4b-11e5-b2f1-6feca76a1bf5'。
問題產生的原因可以這樣來描述了：我們在執行 UPDATE 語句時，MySQL 會將其當成一個事務，對錶的行進行鎖定，這時又有其他連線進來要 UPDATE 同樣的表或者 SELECT 這張表時就必須等待鎖資源，而這個等待時間太久，導致超時了。
什麼？一個 UPDATE 語句居然會這麼慢？這我簡直不能接受啊！那我只能看看為啥這個語句如此慢了。
檢視慢查詢（slow_queries.log）日誌裡面對應的查詢資訊：
|
1 2 |
# Query_time: 56.855324 Lock_time: 48.054343 Rows_sent: 0 Rows_examined: 29400 update testScore set uiTaskId=81041 where token='e7d7d8f0-8f4b-11e5-99be-9dfbb419755e'; |
這樣一條 UPDATE 語句花了 56 秒，掃了 29400 條表記錄。看到這樣的執行日誌，也大概猜到原因了，沒有為查詢欄位 token 加索引！這樣 MySQL 在進行 update 操作時不會走行鎖，直接鎖定了整張表，而這個 update 語句本身也夠慢（掃了全表），那併發多個 update 更新時導致了等待鎖超時。
給 testScore 表的 token 欄位增加了索引，終於，這個異常不再復現，響應時間開始回歸正常。
參考資料
- https://github.com/felixge/node-mysql#pooling-connections
- https://github.com/felixge/node-mysql/issues/712
- http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html
- http://www.cnblogs.com/zhoujinyi/p/3437475.html
- http://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im
- https://www.percona.com/blog/2015/04/09/innodb-locks-deadlocks-without-index-different-isolation-level/