SQL Server 獨佔鎖 (XLOCK) 導致記憶體暴增與 CPU 100% 怎麼辦？

問題現象

兩個視窗同時執行無限迴圈，分別對同一資料表執行 INSERT ... WITH (XLOCK) 與 SELECT/UPDATE ... WITH (XLOCK)。運行一段時間後，記憶體持續上升、CPU 佔用達 100%。

根本原因

• 獨佔鎖 (XLOCK) 造成嚴重阻塞：WITH (XLOCK) 強制排他鎖，寫入與讀取彼此互斥，大量執行緒排隊等待鎖釋放。
• 無限迴圈無節流：程式碼中 while(true) 沒有任何 Thread.Sleep 或等待機制，導致連線建立、指令送出、關閉連線的動作極度頻繁，佔滿 CPU。
• 連線未妥善管理：雖然 finally 有 conn.Close()，但高頻率開關連線仍會產生大量物件等待 GC，推高記憶體。

解決方向

1. 移除不必要的 XLOCK：除非有極嚴格的隔離需求，否則預設隔離等級通常足夠。
2. 加入適當延遲：在迴圈中加入 Thread.Sleep(100) 等，降低併發壓力。
3. 使用連線池：連線字串加入 Pooling=true（預設開啟），避免頻繁建立/銷毀實體連線。
4. 批次處理：將多筆 INSERT/UPDATE 合併為單一交易，減少往返次數。
5. 檢查索引與查詢計畫：確保 WHERE states=1 有索引，減少掃描範圍與鎖持有時間。

改寫範例（關鍵點）

// 移除 XLOCK、加入 Sleep、使用參數化查詢
while (running) {
    using (var conn = new SqlConnection(connStr)) {
        conn.Open();
        using (var cmd = conn.CreateCommand()) {
            cmd.CommandText = "INSERT INTO test_interfacetable0760 (SendPhone, text, SendTime, states, UserID) VALUES (@phone, @txt, @time, 1, 32)";
            cmd.Parameters.AddWithValue("@phone", "13888888888");
            cmd.Parameters.AddWithValue("@txt", "測試內容");
            cmd.Parameters.AddWithValue("@time", "2013-8-14");
            cmd.ExecuteNonQuery();
        }
    }
    Thread.Sleep(200); // 關鍵：給 CPU 喘息空間
}

總結

鎖本身不會直接「佔用記憶體」，但大量阻塞導致執行緒堆疊、連線物件堆積、GC 壓力增大，最終表現為記憶體與 CPU 飆高。解決核心在於降低併發強度、縮短鎖持有時間、避免忙碌等待。