以悲觀並發控制解決資料庫衝突：交易與鎖的實戰模式

核心思想：悲觀鎖機制

悲觀並發控制假設資料衝突機率高，因此在整個資料處理過程中預先鎖定目標資料，阻擋其他交易修改，確保資料一致性。在 SQL Server 中，核心實作依賴交易 + 鎖機制，唯有資料庫層級提供的鎖才能真正保證跨系統、跨連線的排他性。

關鍵鎖模式與隔離等級

預設隔離級別為 READ COMMITTED。若需行級鎖配合可重複讀語意，可在交易開啟前執行：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM Users WITH (ROWLOCK) WHERE Id = 2;

實戰程式碼範例 (C#)

場景：使用者 A 修改記錄，使用者 B 同時嘗試修改同一記錄

使用者 A：明確交易 + HOLDLOCK

private void btnUpdate_Click(object sender, EventArgs e) {
    using (var conn = new SqlConnection(connStr)) {
        conn.Open();
        using (var tran = conn.BeginTransaction()) {
            try {
                // 先查詢加共享鎖，再更新，鎖持有至 Commit/Rollback
                var cmd = new SqlCommand(
                    "SELECT * FROM Users WITH (HOLDLOCK) WHERE Id = 2; " +
                    "UPDATE Users SET Name = N'C羅', Address = N'basi' WHERE Id = 2",
                    conn, tran);
                cmd.ExecuteNonQuery();
                Thread.Sleep(20000); // 模擬長時間業務處理
                tran.Commit();
                MessageBox.Show("A 完成");
            } catch {
                tran.Rollback();
            }
        }
    }
}

使用者 B：隱式交易（無明確交易控制）

private void btnUpdate_Click(object sender, EventArgs e) {
    using (var conn = new SqlConnection(connStr)) {
        conn.Open();
        // 無 WITH (HOLDLOCK)，但會被 A 的鎖封鎖
        var cmd = new SqlCommand(
            "UPDATE Users SET Name = N'大羅', Address = N'basi' WHERE Id = 2", conn);
        cmd.ExecuteNonQuery(); // 此處阻塞等待 A 釋放鎖
        MessageBox.Show("B 完成");
    }
}

執行流程與結果分析

1. A 先執行，SELECT ... HOLDLOCK 取得 Id=2 記錄的共享鎖（S 鎖），後續 UPDATE 轉為更新鎖 (U) 再轉排他鎖 (X)。
2. B 在 A 交易未提交前執行 UPDATE，請求 X 鎖，因與 A 的 X 鎖衝突，進入等待隊列。
3. A 執行 Thread.Sleep(20000) 模擬業務邏輯，20 秒後 Commit 釋放所有鎖。
4. B 獲得鎖，執行更新提交。
5. 最終結果：Name = 大羅（A 先改為 C羅，B 後改為大羅），資料一致性得到保障。

業務應用場景：金融日終結算

在日終結算處理中，必須釐清某個 Cut-off 時間點的資料快照，結算過程（可能持續數秒至數小時）絕不能允許資料變動。此時在查詢快照資料時加上 HOLDLOCK 或使用 SERIALIZABLE 隔離級別，即可確保整個結算窗口期間資料凍結，防止髒寫與不可重複讀。

注意事項與最佳實務

• 鎖粒度選擇：優先使用行鎖 (ROWLOCK) 減少爭用；避免不必要的資料表鎖 (TABLOCKX) 影響並發度。
• 交易長度：盡量縮短交易範圍，避免長交易持有鎖導致阻塞鏈、死鎖風險上升、記錄檔膨脹。
• 死鎖預防：統一存取資料表/資料列順序、適當加入索引減少掃描範圍、考慮使用 READ COMMITTED SNAPSHOT (RCSI) 降低讀寫衝突（屬樂觀並發範疇）。
• 錯誤處理：務必在 Catch 區塊呼叫 Rollback()，並在 Finally 確保連線關閉。

總結

悲觀鎖透過資料庫原生鎖機制，在關鍵業務流程中提供強一致性保證。正確選擇鎖提示、隔離級別、控制交易範圍，是平衡資料正確性與系統並發效能的關鍵。實務上應視業務衝突頻率、容忍延遲程度，在悲觀與樂觀並發策略間做權衡選擇。