您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 並發控制實戰：悲觀鎖與樂觀鎖的選型、實現與避坑指南

編輯：關於SqlServer

核心場景：先讀後寫的競態條件

典型業務流程：SELECT 讀取數據 → 業務邏輯判斷 → UPDATE 寫回。高併發下，兩個會話同時讀到同一版本數據，先後寫入導致更新丟失。SQL Server 提供兩種鎖機制解決：

悲觀鎖：假設衝突必發生，讀時加鎖（UPDLOCK/XLOCK），強制串行化。
樂觀鎖：假設衝突低概率，讀時不加鎖，寫時通過版本號/時間戳驗證「讀到的版本是否仍為最新」。

悲觀鎖實戰：`UPDLOCK` + `HOLDLOCK`

BEGIN TRAN;
-- 選取一張未使用的卡，並加更新鎖（U鎖：兼容共享鎖，不兼容排他鎖/更新鎖）
DECLARE @CardNo VARCHAR(20);
SELECT TOP 1 @CardNo = F_CardNo
FROM dbo.Card WITH (UPDLOCK, HOLDLOCK)  -- HOLDLOCK = SERIALIZABLE，持有鎖至事務結束
WHERE F_Flag = 0;

-- 模擬業務處理耗時
WAITFOR DELAY '00:00:05';

UPDATE dbo.Card
SET F_Name = SYSTEM_USER, F_Time = GETDATE(), F_Flag = 1
WHERE F_CardNo = @CardNo;

COMMIT TRAN;

關鍵點解析

鎖提示	含義	兼容性
`UPDLOCK`	讀取時獲取更新鎖（U 鎖）	兼容 S 鎖，不兼容 U/X 鎖
`HOLDLOCK`	相當於 `SERIALIZABLE`，鎖持有至事務結束	防止幻讀，範圍鎖
`ROWLOCK`	強制行級鎖（防止升級頁/表鎖）	配合 UPDLOCK 使用

優缺點

✅ 實現簡單，強一致性，適用於衝突率高、事務極短場景（如扣庫存、發紅包）。
❌ 併發度低，易產生阻塞鏈、死鎖；長事務下災難性。

樂觀鎖實戰：`ROWVERSION` / `TIMESTAMP` 版本檢查

-- 1. 表結構添加版本列（自動維護，每次 UPDATE 自動遞增）
ALTER TABLE dbo.Card ADD RowVer ROWVERSION NOT NULL;

-- 2. 讀取數據與版本（無鎖讀取）
DECLARE @CardNo VARCHAR(20), @Ver BINARY(8);
SELECT TOP 1 @CardNo = F_CardNo, @Ver = RowVer
FROM dbo.Card
WHERE F_Flag = 0;

-- 3. 業務處理（無鎖窗口，併發度最高）
WAITFOR DELAY '00:00:05';

-- 4. 條件更新：版本匹配才寫入
UPDATE dbo.Card
SET F_Name = SYSTEM_USER, F_Time = GETDATE(), F_Flag = 1
WHERE F_CardNo = @CardNo AND RowVer = @Ver;

IF @@ROWCOUNT = 0
BEGIN
    -- 版本不匹配：被其他會話搶先修改
    IF EXISTS (SELECT 1 FROM dbo.Card WHERE F_CardNo = @CardNo)
        THROW 50001, '該卡已被他人註冊', 1;
    ELSE
        THROW 50002, '卡號不存在', 1;
END

優缺點

✅ 讀不阻寫、寫不阻讀，併發度極高，適合長事務、用戶思考時間長、衝突率低場景（如表單編輯、審批流）。
❌ 應用層需處理重試邏輯；若衝突頻發，重試開銷大於悲觀鎖。

進階：應用層重試模式（C# 範例）


public async Task RegisterCardOptimisticAsync(string userName, int maxRetry = 3)
{
    for (int i = 0; i < maxRetry; i++)
    {
        using var tran = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
        var card = await db.Cards
            .Where(c => c.F_Flag == 0)
            .OrderBy(c => c.F_CardNo)
            .Select(c => new { c.F_CardNo, c.RowVer })
            .FirstOrDefaultAsync();

        if (card == null) return false;

        await Task.Delay(5000); // 模擬業務

        var rows = await db.Database.ExecuteSqlRawAsync(
            @"UPDATE dbo.Card SET F_Name={0}, F_Time=GETDATE(), F_Flag=1 
              WHERE F_CardNo={1} AND RowVer={2}", userName, card.F_CardNo, card.RowVer);

        if (rows > 0) { tran.Complete(); return true; }
        // 版本衝突，稍等重試
        await Task.Delay(Random.Shared.Next(50, 200));
    }
    throw new ConcurrencyException("多次重試失敗，請稍後再試");
}

選型決策矩陣

維度	悲觀鎖	樂觀鎖
衝突頻率	高（>20%）	低（<5%）
事務持續時間	極短（<10ms）	較長（含用戶思考）
一致性要求	強一致，零容忍衝突	最終一致，可重試
實現複雜度	低（純 T-SQL）	中（需應用配合重試）
典型場景	扣庫存、轉賬、搶單	表單編輯、文檔協作、審批