需求背景
特定業務需求（如跨資料庫唯一編號、自訂起始值/步長、外部系統同步）導致無法直接使用 IDENTITY(100,1) 或 SEQUENCE（SQL 2012+）。本文示範以資料表儲存計數器，配合預存程序實現執行緒安全的遞增取號。
核心物件
-- 計數器儲存表
CREATE TABLE dbo.Common_Info (
Name VARCHAR(20) PRIMARY KEY,
Value INT NOT NULL
);
INSERT INTO dbo.Common_Info (Name, Value) VALUES ('Msg_Count', 100);
並發安全預存程序
CREATE PROCEDURE dbo.GetNextMsgCount
@NextValue INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Current INT;
-- 1. 取得目前值（加 UPDLOCK, HOLDLOCK 防止幻讀與更新遺失）
SELECT @Current = Value
FROM dbo.Common_Info WITH (UPDLOCK, HOLDLOCK)
WHERE Name = 'Msg_Count';
-- 2. 嘗試原子性更新：預期值仍為 @Current 才更新
UPDATE dbo.Common_Info
SET Value = @Current + 1
WHERE Name = 'Msg_Count' AND Value = @Current;
-- 3. 判斷更新是否成功
IF @@ROWCOUNT = 0
BEGIN
-- 並發衝突，重試或回傳錯誤
RETURN -1000; -- 自訂錯誤碼
END
-- 4. 回傳新序號
SET @NextValue = @Current + 1;
RETURN 0;
END
關鍵並發控制解析
| 技術 | 作用 |
|---|
UPDLOCK | 讀取時加更新鎖，防止其他交易同時讀取相同資料列導致更新遺失 |
HOLDLOCK (相當於 SERIALIZABLE) | 維持鎖定至交易結束，防止幻讀 |
WHERE Value = @Current | 樂觀並發檢查：若值已被其他交易修改，更新影響 0 列，觸發重試 |
@@ROWCOUNT | 檢查更新是否成功 |
應用程式端呼叫模式（重試邏輯）
int maxRetry = 3;
int nextVal = -1;
for (int i = 0; i < maxRetry; i++) {
int ret = execGetNextMsgCount(out nextVal); // 呼叫預存程序
if (ret == 0) break; // 成功
if (ret == -1000) continue; // 並發衝突，重試
throw new Exception("DB Error: " + ret);
}
現代替代方案比較
| 方案 | 適用版本 | 優點 | 缺點 |
|---|
| 資料表計數器 + 預存程序 | 所有版本 | 完全可控、支援複雜業務邏輯 | 需自行處理並發、效能較低 |
IDENTITY | 所有版本 | 原生高效能、無縫隙（重啟可能跳號） | 無法自訂步長/起始值、難跨表/跨庫唯一 |
SEQUENCE | SQL 2012+ | 獨立物件、可跨表共用、支援 CYCLE/CACHE | 需較新版本、預設緩存可能導致跳號 |
NEWSEQUENTIALID() / UNIQUEIDENTIFIER | 所有版本 | 全域唯一、無協調成本 | 非人類可讀、索引碎片化、儲存空間大 |
最佳實務建議
- SQL 2012+ 優先使用 SEQUENCE，設定
CACHE 1000 平衡效能與跳號風險。
- 舊版本或需複雜業務邏輯（如依業務類型分段編號）才採用資料表計數器。
- 資料表計數器務必加上
UPDLOCK, HOLDLOCK 或採用 sp_getapplock 應用程式鎖保護關鍵區段。
- 避免在高併發核心路徑使用單點計數器，考慮號段預分配（批次取 1000 號一次）降低資料庫壓力。
