最近遇到應用頻繁的響應緩慢，無法正常訪問。幫忙一起定位原因，最後定位到的問題說起來真的是很小的細節問題，但是就是這些小細節導致了服務不穩定，真是細節決定成敗。這裡嘗試著來分享下，希望對大家有所幫助。

問題 1：佔著茅坑不拉屎

遇到問題首先要看的還是伺服器錯誤日誌。

錯誤日誌中看到頻繁有這樣的一個異常報錯：Error: ER_CON_COUNT_ERROR: Too many connections。這個報錯是因為資料庫的所有連線被客戶端都佔有了，沒有空閒的連線可以使用。MySQL 預設的最大併發連線數是 100，然而我們的應用這邊最多可能的併發也就 30~40 個任務，怎麼也不太可能報這樣的錯誤，推測很有可能是程式碼裡面建立連線後沒有及時的進行關閉。於是我們重點看了下執行 SQL 部分的程式碼，大概是下面這樣（使用了node-mysql庫）：

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();
    });

};

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 執行一般是非常快的（零點幾秒），如果我們執行完後不釋放，在同一時間產生很多資料庫連線時很有可能導致連線被阻塞，產生連線過多的異常。於是我們對程式碼進行了如下修改：

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);
        });
    });

};

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）日誌裡面對應的查詢資訊：

# 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';

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 欄位增加了索引，終於，這個異常不再復現，響應時間開始回歸正常。

記一次 MySQL 資料庫問題排查

問題 1：佔著茅坑不拉屎

問題 2：一條 UPDATE 引發的血案

參考資料

相關文章