前言

身為後端工程師，你是否遇過這樣的情況：

  • 系統突然變得超級慢
  • 網頁一直轉圈圈
  • 收到「已超過連線逾時的設定」錯誤訊息

如果有，恭喜你！你可能遇到了資料庫連線池爆掉的問題。今天就來分享一個真實案例，看看如何從診斷到解決這個棘手的問題。

問題診斷

當你遇到以下錯誤訊息時，很可能是連線池出問題了：

1
System.InvalidOperationException: '已超過連線逾時的設定。在取得集區連線之前超過逾時等待的時間，可能的原因為所有的共用連線已在使用中，並已達共用集區大小的最大值。'

快速診斷方法

首先，我們可以使用以下 SQL 指令來查看目前的連線狀況：

1
2
3
4
5
6
7
8
9
10
SELECT 
    DB_NAME(dbid) as DatabaseName,
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName,
    status,
    hostname as HostName,
    program_name as ProgramName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame, status, hostname, program_name;

緊急處理方案

發現問題後，可以使用以下步驟處理：

  1. 檢視問題連線
    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
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    -- 1. 首先查看當前的連接詳情
    SELECT 
        spid as '處理序 ID',
        db_name(dbid) as '數據庫',
        loginame as '登入名稱',
        hostname as '主機名',
        program_name as '程式名稱',
        login_time as '登入時間',
        last_batch as '最後執行時間',
        status as '狀態'
    FROM sys.sysprocesses 
    WHERE dbid = DB_ID('資料庫名稱')  -- 替換成你的數據庫名
    ORDER BY login_time DESC;

    -- 2. 查看具體在執行什麼 SQL
    SELECT 
        spid,
        blocked as '被阻塞源',
        hostname as '主機名',
        program_name as '程式名稱',
        cmd as '指令',
        cpu as 'CPU時間',
        physical_io as '實體IO',
        last_batch as '最後執行時間',
        [text] as 'SQL內容'
    FROM sys.sysprocesses sp
    CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    WHERE dbid = DB_ID('資料庫名稱')  -- 替換成你的數據庫名

    -- 3. 殺掉特定連接 (請小心使用)
    -- KILL 52;  -- 替換成你要關閉的 SPID

    -- 4. 如果確定要關閉所有用戶連接，可以使用動態 SQL (需要特別小心)
    DECLARE @kill varchar(8000) = '';
    SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), spid) + ';'
    FROM sys.sysprocesses 
    WHERE dbid = DB_ID('資料庫名稱')  -- 替換成你的數據庫名
    AND spid > 50;

    -- 執行前先確認要關閉的連接
    PRINT @kill;

    -- 如果確認無誤，則可以執行（請小心使用）
    -- EXEC(@kill);

    -- 5. 如果要切換數據庫到單一用戶模式
    -- 這會強制斷開所有現有連接
    ALTER DATABASE 資料庫名稱
    SET SINGLE_USER 
    WITH ROLLBACK IMMEDIATE;

    -- 完成後切換回多用戶模式
    -- ALTER DATABASE 資料庫名稱 SET MULTI_USER;

代碼優化建議

這次發生的問題最後定位問題發現是很久以前前人寫的VB程式碼資料庫連接沒有正確的釋放導致的，為了避免類似問題再次發生，對程式碼進行以下優化：

  1. 使用 Using 區塊確保資源釋放

    1
    2
    3
    Using connection As New SqlConnection(connectionString)
        ' 資料庫操作
    End Using

  2. 避免全域宣告資料庫連線

    1
    2
    3
    4
    5
    6
    7
    8
    9
    ' 不建議
    Dim conn As New SqlConnection  '全域變數

    ' 建議
    Private Function GetData() As DataTable
        Using conn As New SqlConnection(connectionString)
            ' 程式碼
        End Using
    End Function

  3. 設定適當的連線池參數

    1
    2
    3
    4
    5
    6
    7
    <connectionStrings>
        <add name="MyConnection" 
             connectionString="Server=myserver;Database=mydb;
             Max Pool Size=100;Min Pool Size=5;
             Connection Timeout=30;"
             providerName="System.Data.SqlClient" />
    </connectionStrings>

預防措施

  1. 定期監控連線數

    • 設置警示機制
    • 監控連線成長趨勢

  2. 程式碼審查重點

    • 確認所有資料庫連線都有正確釋放
    • 避免無限期保持連線

  3. 效能調校建議

    • 適當設定 Max Pool Size
    • 配置合理的 Connection Timeout
    • 實作重試機制

結論

資料庫連線池問題雖然棘手，但只要掌握正確的診斷和處理方法，就能夠優雅地解決。預防永遠勝於治療，建議定期檢查程式碼和監控系統狀態，及早發現潛在問題。

參考資源