多執行緒環境下避免重複讀取 SQL Server 資料的最佳實踐

簡訊、郵件發送等非同步業務常採用「待發送表 + 多背景執行緒輪詢」架構。若直接 SELECT TOP 100 * FROM SMS WHERE Status=0 再 UPDATE，極易造成多執行緒搶讀同一筆資料，導致重複發送。

問題場景

CREATE TABLE SMS (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Content varchar(1024),
    Status int NOT NULL DEFAULT 0,  -- 0 未讀取, 1 已讀取
    CreateTime datetime
);

多執行緒並發 SELECT TOP 100 * FROM SMS WHERE Status=0 → 同批資料被多線程撈走 → 重複發送。

解決方案：UPDATE + OUTPUT 子句 (原子性鎖定與取回)

SQL Server 2005+ 支援 OUTPUT 子句，可在 單一陳述式 中完成「更新狀態」與「回傳剛鎖定的資料」，完全避免競態條件。

DECLARE @RowID TABLE (RowID int);

SET ROWCOUNT 100;  -- 每批處理筆數

BEGIN TRAN;
    -- 1. 原子性更新並輸出主鍵
    UPDATE SMS 
    SET Status = 1 
    OUTPUT deleted.ID INTO @RowID 
    WHERE Status = 0;

    -- 2. 依主鍵精準讀取完整資料
    SELECT * 
    FROM SMS 
    WHERE ID IN (SELECT RowID FROM @RowID);
COMMIT TRAN;

關鍵點解析

• OUTPUT deleted.ID：捕獲更新前的主鍵值，deleted 虛擬表同觸發器語意
• SET ROWCOUNT / TOP (100)：限制批次大小，避免長交易鎖定過多資料列
• 隱含交易：單一 UPDATE 為自動交易，加上 BEGIN/COMMIT 確保 SELECT 見到剛提交的資料
• 索引建議：Status 欄位建立篩選索引 CREATE INDEX IX_SMS_Pending ON SMS(ID) WHERE Status=0，極大縮小掃描範圍

替代方案比較

實務上建議搭配 WAITFOR DELAY '00:00:01' 控制輪詢頻率，並監控 sys.dm_tran_locks 確認鎖定行為。此模式廣泛適用於工作佇列、訊息總線、ETL 批次協調等場景。