SQL Server 2005 查詢獨佔模擬：UPDATE TOP + ROWLOCK + READPAST 實作無鎖競爭批次處理

應用場景

多執行緒/多行程並行處理同一張工作表（Queue Table），每個工作者需**獨佔認領 N 筆待處理任務**，避免重複處理或死鎖，且不想使用應用層鎖或 sp_getapplock。

核心技巧：UPDATE TOP (N) ... WITH (ROWLOCK, READPAST) + OUTPUT

• TOP (N)：限制單次認領筆數
• ROWLOCK：強制列級鎖，避免升級為頁/表鎖
• READPAST：跳過已被其他交易鎖定的列，**不等待、不阻塞、不報錯**
• OUTPUT inserted.*：原子性回傳認領成功的列（含身分識別值）

完整可執行範例

SET NOCOUNT ON;
USE tempdb;
GO

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb;
CREATE TABLE tb (
    id INT IDENTITY(1,1),
    name VARCHAR(10),
    tag INT DEFAULT 0  -- 0=待處理, 1=已認領
);
INSERT tb(name) VALUES ('a'),('b'),('c'),('d'),('e');
GO

-- 模擬三個工作者各認領 2 筆
-- Worker 1
UPDATE TOP (2) tb WITH (ROWLOCK, READPAST)
SET tag = 1
OUTPUT inserted.id, inserted.name
WHERE tag = 0;
GO
-- 結果: id=1,2 (a,b)

-- Worker 2
UPDATE TOP (2) tb WITH (ROWLOCK, READPAST)
SET tag = 1
OUTPUT inserted.id, inserted.name
WHERE tag = 0;
GO
-- 結果: id=3,4 (c,d)

-- Worker 3
UPDATE TOP (2) tb WITH (ROWLOCK, READPAST)
SET tag = 1
OUTPUT inserted.id, inserted.name
WHERE tag = 0;
GO
-- 結果: id=5 (e) -- 僅剩 1 筆

關鍵點解析

索引建議

CREATE INDEX idx_tb_tag ON tb(tag) INCLUDE (name);
-- 讓 WHERE tag = 0 能快速定位少量列，配合 ROWLOCK 只鎖定必要列

適用性與限制

• ✅ 適用：工作佇列、批次分發、並行 ETL 階段、訂單派單
• ⚠️ 注意：READPAST 會**靜默略過**被鎖列，**不保證公平性**（可能飢餓）
• ⚠️ 注意：UPDATE TOP 無 ORDER BY，認領順序依索引掃描順序（通常是 PK 順序）
• ❌ 不適用：需嚴格 FIFO、需交易一致性跨多表、需鎖定等待語意

替代方案比較

結語

UPDATE TOP (N) ... WITH (ROWLOCK, READPAST) OUTPUT ... 是 SQL Server 實現**高併發無鎖競爭工作分發**的極簡利器，適合「大致公平、高吞吐、容忍偶爾順序調換」的批次處理場景。搭配適當索引與 tag 狀態機，可構建輕量級佇列系統。

1. 上一篇文章： SQL Server 2005 OVER 子句進階應用：聚合函數配合窗口運算避免子查詢
2. 下一篇文章： SQL Server 刪除指定資料表所有索引與統計資料的預存程序實作

SQL Server 2005

SQL Server 字串最大遞增子序列查詢 (CTE 遞迴 + 窗口函數)

給定僅含小寫字母 a-z 的字串 (如 abcbklmnod

SQL Server 2005 圖文安裝教學與 SP3 修補程式套用完整指南

本文提供 SQL Server 2005 從版本選擇、硬軟體

SQL Server 索引完整指南：架構、建立、分析與維護

索引核心概念 資料庫索引如同書本目錄，對資料表一或多個欄位值

SQL Server 2005 CTE 共用資料表運算式完整教學：遞迴查詢與多次引用實戰

SQL Server 2005 引入 CTE (Common