上一篇，我處理了一個看似單純、實際上牽動整條資料流的需求：把三碼流水號改成四碼十進位。
在釐清三碼十進位、三碼十六進位與四碼十進位的歷史規則後，我把新資料的產號邏輯集中到資料庫，Delphi Client 不再各自判斷下一號。
單人測試時，一切看起來都很正常：
目前最後一號：0295
新增第一筆： 0296
新增第二筆： 0297
連續新增十幾次，也沒有出現重複。
但這種測試其實只證明了一件事：
前一筆新增完成後，下一筆可以取得正確號碼。
它沒有證明兩位使用者同時新增時，系統仍然安全。
假設使用者 A 與使用者 B 幾乎同時按下「新增」，兩邊都先查到目前最大值是 0295，接著各自加一：
使用者 A：MAX = 0295 → 下一號 = 0296
使用者 B：MAX = 0295 → 下一號 = 0296
兩個人的計算都沒有錯，結果卻撞在一起。
這類問題不一定每次發生，也很難靠一般手動測試碰到。開發者自己測一百次可能都正常，正式環境只要剛好有兩個人同時操作，就可能出現重複鍵、存檔失敗，甚至主檔與明細拿到不同號碼。
這也是今天要處理的問題：
如何讓「取得下一號」與「使用這個號碼新增資料」成為一個不可被插隊的完整動作？
MAX + 1 錯的不是算式，而是時間差最常見的流水號寫法，大概長這樣：
SELECT @NextNo = MAX(ItemNo) + 1
FROM dbo.DocumentHeader;
INSERT INTO dbo.DocumentHeader (ItemNo, Title)
VALUES (@NextNo, @Title);
在只有一個使用者時，流程是：
查最大值 → 加一 → 新增
問題出在「查最大值」與「新增」並不是同一個瞬間。
兩個 Session 可能交錯成這樣：
| 時間 | Session A | Session B |
|---|---|---|
| T1 | 讀到最大值 0295 |
|
| T2 | 讀到最大值 0295 |
|
| T3 | 算出 0296 |
|
| T4 | 算出 0296 |
|
| T5 | 寫入 0296 |
|
| T6 | 也嘗試寫入 0296 |
這就是 Race Condition：最後結果取決於兩段程式執行時剛好如何交錯。
它最麻煩的地方不是「一定會錯」，而是「大部分時間都不會錯」。
也因此，當使用者回報「偶爾新增失敗」時，如果只看單次操作與單一 Log，很容易誤以為是網路不穩、Dataset 沒 Post，或使用者重複按按鈕。
實際原因可能只是兩個完全正常的請求，在錯誤的時間點讀到了同一個狀態。
Concurrency Bug 不需要任何一位使用者做錯事；只要兩個正確流程同時發生，就可能產生錯誤。
第八篇提到，我把產號集中到資料庫處理。
但「放進資料庫」與「具備併發安全」是兩回事。如果 Stored Procedure 仍然只是先 SELECT MAX(...)，再執行 INSERT，中間沒有正確的 Transaction 與 Lock，兩個 Session 仍然可能讀到同一個最大值。
下面這段雖然集中在 Stored Procedure，仍有 Race Condition。為了只展示併發問題，這個簡化範例先假設測試資料只包含新制四碼數字；否則舊制的 3E8 會讓 CONVERT(int, ItemNo) 直接失敗，反而看不到真正要觀察的搶號現象。
CREATE PROCEDURE dbo.CreateDocument_Unsafe
@ProjectNo varchar(20),
@Title nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NextValue int;
DECLARE @NextNo varchar(4);
SELECT @NextValue = ISNULL(MAX(CONVERT(int, ItemNo)), 0) + 1
FROM dbo.DocumentHeader
WHERE ProjectNo = @ProjectNo
AND LEN(ItemNo) = 4
AND ItemNo NOT LIKE '%[^0-9]%';
SET @NextNo = RIGHT('0000' + CONVERT(varchar(4), @NextValue), 4);
INSERT INTO dbo.DocumentHeader (ProjectNo, ItemNo, Title)
VALUES (@ProjectNo, @NextNo, @Title);
SELECT @NextNo AS NewItemNo;
END;
它改善了「每個 Client 都有一套產號程式」的問題，卻還沒有保證兩個呼叫不能同時進入。
這裡有一個很重要的判斷：
集中規則解決的是一致性；Transaction 與 Lock 解決的才是同時執行。
兩者都需要，但不能互相取代。
BEGIN TRAN 就有魔法看到併發問題後，第一個直覺通常是加上 Transaction：
BEGIN TRAN;
SELECT @NextValue = MAX(...) + 1;
INSERT INTO ...;
COMMIT TRAN;
Transaction 提供這組操作的原子邊界；再配合 SET XACT_ABORT ON、TRY...CATCH 與明確的 ROLLBACK，才能讓執行期間發生錯誤時，交易按照預期Rollback。
但即使錯誤處理完整，在 SQL Server 預設的 READ COMMITTED 隔離等級下，Transaction 仍不一定會阻止另一個 Session 在相同時間讀到同一個最大值。
也就是說：
XACT_ABORT 與錯誤處理共同保護這組異動的完整性；因此，我需要的不只是「包成一個交易」，而是：
這也是 UPDLOCK 與 HOLDLOCK 出場的地方。
UPDLOCK 與 HOLDLOCK 各自在做什麼？先用比較白話的方式理解：
UPDLOCK：先取得 Update Lock，並持有到交易結束一般查詢通常取得 Shared Lock。多個 Session 可以同時讀，因此 A 與 B 都可能拿到 0295。
UPDLOCK 會要求 SQL Server 在讀取時使用 Update Lock，先表明：
這不是普通查詢，我讀完後準備依據這個結果進行更新或新增。
對同一個鎖定資源而言，相衝突的 Update Lock 不能同時取得，因此後來的 Session 必須等待。這個 Update Lock 本身就會持有到 Transaction 結束，不需要靠 HOLDLOCK 才延長生命週期。
HOLDLOCK：用 SERIALIZABLE 語意保護查詢範圍HOLDLOCK 相當於針對該資料來源套用 SERIALIZABLE 語意。它的重要性不只是「鎖久一點」，而是在適當的索引與執行計畫下，保護符合查詢條件的 Key Range。
產號時，下一個號碼目前還不存在。只鎖住已讀到的 0295 不一定足夠；真正要控制的是其他 Session 能不能在交易完成前，插入同一個查詢範圍內的競爭資料。
因此可以把兩者理解成：
UPDLOCK
→ 讀取目前資料時先取得 Update Lock，並持有到交易結束
HOLDLOCK
→ 對查詢套用 SERIALIZABLE 語意，連可能被插入的 Key Range 一起納入控制
兩者放在一起，想表達的是：
我要讀取目前號碼，準備產生下一號；
在我完成新增或Rollback以前，其他人先等一下。
但要注意，Lock Hint 不是貼上去就保證萬無一失。鎖定是否精準，仍會受到查詢條件、索引、執行計畫與流水號作用域影響。
這篇使用的 Lock Hint，是針對目前 Legacy System 產號模型採取的解法，不代表所有
MAX + 1都應直接複製同一組 Hint。
如果查詢缺少合適索引，SQL Server 可能掃描或鎖住比預期更大的範圍，雖然避免了重複號，卻把不同案件的新增也一起排隊。安全是第一步，鎖太大則會變成下一個效能問題。
在寫 SQL 前，必須先回答：
這個流水號是在整張表內唯一，還是只在同一個案件內遞增？
假設規則是「同一個案件內，流水號不得重複」，唯一條件就不應該只有 ItemNo，而應是：
ProjectNo + ItemNo
匿名化後的資料表可以簡化成：
CREATE TABLE dbo.DocumentHeader
(
ProjectNo varchar(20) NOT NULL,
ItemNo varchar(4) NOT NULL,
Title nvarchar(100) NOT NULL,
CreatedAt datetime2(0) NOT NULL
CONSTRAINT DF_DocumentHeader_CreatedAt DEFAULT SYSDATETIME(),
CONSTRAINT PK_DocumentHeader
PRIMARY KEY (ProjectNo, ItemNo)
);
這個複合鍵有兩個用途：
鎖定負責避免碰撞，唯一鍵負責守住最後一道底線。兩者不應二選一。
以下範例刻意只處理新制四碼十進位資料；上一篇提到的舊制辨識與共同數值換算，正式系統仍應依已確認的歷史 Context 納入計算，不能只靠 TRY_CONVERT 猜測舊資料格式。
CREATE OR ALTER PROCEDURE dbo.CreateDocument
@ProjectNo varchar(20),
@Title nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @NextValue int;
DECLARE @NextNo varchar(4);
BEGIN TRY
BEGIN TRANSACTION;
-- 讀取同一案件目前最大的「新制十進位」流水號。
-- UPDLOCK：讀取時取得 Update Lock，並持有到交易結束。
-- HOLDLOCK：套用 SERIALIZABLE 語意，保護符合條件的 Key Range。
SELECT @NextValue = ISNULL(MAX(TRY_CONVERT(int, ItemNo)), 0) + 1
FROM dbo.DocumentHeader WITH (UPDLOCK, HOLDLOCK)
WHERE ProjectNo = @ProjectNo
AND LEN(ItemNo) = 4
AND ItemNo NOT LIKE '%[^0-9]%';
IF @NextValue > 9999
BEGIN
THROW 50001, N'流水號已超過四碼十進位上限。', 1;
END;
SET @NextNo = RIGHT(
'0000' + CONVERT(varchar(4), @NextValue),
4
);
INSERT INTO dbo.DocumentHeader
(
ProjectNo,
ItemNo,
Title
)
VALUES
(
@ProjectNo,
@NextNo,
@Title
);
COMMIT TRANSACTION;
-- 只有新增成功後，才把正式號碼交回呼叫端。
SELECT @NextNo AS NewItemNo;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;
END;
這段程式真正重要的不是 RIGHT('0000'...)，而是整個順序：
開始交易
↓
鎖定該案件的產號範圍
↓
取得目前最大值
↓
計算下一號
↓
使用該號碼新增
↓
提交交易
↓
回傳已成功寫入的號碼
如果任何一步失敗，就 Rollback，不能先把號碼交給 Client，再期待後面的新增一定成功。
這裡加入 LEN(ItemNo) = 4，是為了讓簡化範例只計算新制四碼資料。像 295、400、999 這些三碼純數字舊值，不會因為「只含數字」就被誤認成新制。
但這仍只是為了說明併發控制而縮小的模型。正式系統如果要從歷史舊值繼續往下編號，仍必須沿用 Day 8 已確認的歷史 Context，先轉成共同數值再比較，不能只查四碼資料，也不能只靠長度或字元外觀猜測格式。
另一種看似合理的作法是：
0296；0296 顯示在畫面；問題是，第二步之後，資料庫已經不再保護這個號碼。
如果號碼尚未真正占用，另一個 Client 也可能取得 0296。如果先建立一筆空白保留資料，又要處理取消新增、逾時、斷線與廢號問題。
因此，這次採取的原則是：
正式流水號的取得與主檔新增，必須位於同一個資料庫交易中。
畫面在新增狀態時若需要暫時識別資料，可以使用獨立的暫存 ID；正式流水號則等真正存檔成功後再回傳。
這裡也提醒我，流水號除了是格式問題，還涉及生命週期：它是在按下「新增」時產生，還是在按下「儲存」並成功寫入時才成立？
兩種設計都可能有合理場景，但必須明確定義，不能讓 Client 與資料庫各自理解。
而且 Transaction 裡不應夾入使用者互動、長時間外部呼叫或其他不必要的慢操作。取得 Lock 後如果還停下來等待使用者輸入，會延長 Blocking 時間，也提高 Deadlock 風險。
交易裡只做必要的資料庫工作，不要拿著 Lock 等使用者操作。
SEQUENCE？SQL Server 的 SEQUENCE 確實是另一種產號方案，也能避免多個 Session 各自執行 MAX + 1。
但 SEQUENCE 取號不受目前 Transaction 的 Rollback 影響。號碼一旦被取出，即使後續新增失敗或交易Rollback，該號碼仍可能已被消耗，因此出現跳號是正常行為。
此外，這個案例的流水號若是依案件各自從頭編號，就不能直接把一條全域 SEQUENCE 套用到所有案件；若要為每個案件建立或管理不同 SEQUENCE，維護方式也會變得更複雜。
所以不是 SEQUENCE 不好，而是要先確認商業需求是否接受：
這次選擇 MAX + 1 配合 Transaction 與 Lock，是基於既有 Legacy System 的資料規則與低侵入改造需求，不代表所有新系統都應該採用相同設計。
當產號與新增由資料庫完成後，Delphi 端不應再保留另一套 MAX + 1 或補零邏輯。
匿名化後，可以簡化成：
procedure TDocumentForm.SaveNewDocument;
begin
qryCreateDocument.Close;
qryCreateDocument.ParamByName('ProjectNo').AsString :=
edtProjectNo.Text;
qryCreateDocument.ParamByName('Title').AsString :=
edtTitle.Text;
qryCreateDocument.Open;
// 資料庫成功新增後，才取得正式流水號。
edtItemNo.Text :=
qryCreateDocument.FieldByName('NewItemNo').AsString;
end;
Client 在這裡只做三件事：
特別是第三點。如果 Client 收到重複鍵錯誤後，自動再查一次 MAX + 1 重試，雖然偶爾看似能救回操作，卻可能掩蓋真正的鎖定問題，也讓重複送出與部分成功更難追查。
Concurrency Bug 不能只靠閱讀程式碼判斷，最好真的把兩個 Session 打開來測。
可以先建立一個只用於測試的資料表與測試資料，切勿直接拿 Production 做阻塞實驗。以下測試假設 TEST-001 已有一筆 0295。
先不加 Lock，也暫時不新增資料。
在 Session A 執行：
DECLARE @NextNo int;
SELECT @NextNo = ISNULL(MAX(CONVERT(int, ItemNo)), 0) + 1
FROM dbo.DocumentHeader
WHERE ProjectNo = 'TEST-001'
AND LEN(ItemNo) = 4
AND ItemNo NOT LIKE '%[^0-9]%';
SELECT @NextNo AS SessionA_NextNo;
接著在 Session B 執行相同查詢：
DECLARE @NextNo int;
SELECT @NextNo = ISNULL(MAX(CONVERT(int, ItemNo)), 0) + 1
FROM dbo.DocumentHeader
WHERE ProjectNo = 'TEST-001'
AND LEN(ItemNo) = 4
AND ItemNo NOT LIKE '%[^0-9]%';
SELECT @NextNo AS SessionB_NextNo;
在資料尚未改變的情況下，兩邊都會得到候選值 296。這一階段只證明：兩個獨立 Session 可以根據相同基準算出同一個候選號碼。
先在 Session A 執行以下程式，完成 INSERT 後刻意不要立刻 COMMIT：
BEGIN TRANSACTION;
DECLARE @NextValue int;
DECLARE @NextNo varchar(4);
SELECT @NextValue = ISNULL(MAX(CONVERT(int, ItemNo)), 0) + 1
FROM dbo.DocumentHeader WITH (UPDLOCK, HOLDLOCK)
WHERE ProjectNo = 'TEST-001'
AND LEN(ItemNo) = 4
AND ItemNo NOT LIKE '%[^0-9]%';
SET @NextNo = RIGHT(
'0000' + CONVERT(varchar(4), @NextValue),
4
);
INSERT INTO dbo.DocumentHeader (ProjectNo, ItemNo, Title)
VALUES ('TEST-001', @NextNo, N'Session A 測試');
SELECT @NextNo AS SessionA_InsertedNo;
-- 先停在這裡，不要 COMMIT。
-- 切到 Session B 執行下一段。
接著在 Session B 執行：
BEGIN TRANSACTION;
DECLARE @NextValue int;
DECLARE @NextNo varchar(4);
SELECT @NextValue = ISNULL(MAX(CONVERT(int, ItemNo)), 0) + 1
FROM dbo.DocumentHeader WITH (UPDLOCK, HOLDLOCK)
WHERE ProjectNo = 'TEST-001'
AND LEN(ItemNo) = 4
AND ItemNo NOT LIKE '%[^0-9]%';
SET @NextNo = RIGHT(
'0000' + CONVERT(varchar(4), @NextValue),
4
);
INSERT INTO dbo.DocumentHeader (ProjectNo, ItemNo, Title)
VALUES ('TEST-001', @NextNo, N'Session B 測試');
SELECT @NextNo AS SessionB_InsertedNo;
COMMIT TRANSACTION;
此時 Session B 應停在查詢處等待，而不是立即取得 0296。
確認 B 正在等待後，回到 Session A 執行：
COMMIT TRANSACTION;
Session A 提交後，Session B 才會繼續執行。它重新看到已提交的 0296，因此應建立 0297，而不是再次使用 0296。
如果要測試 Rollback，則把 Session A 最後一步改成：
ROLLBACK TRANSACTION;
這時 Session B 繼續後，應依實際已提交的最大值重新計算。測試重點不是「每次都一定跳到下一個數字」，而是另一個 Session 不能根據尚未完成的舊基準，偷偷拿走同一個號碼。
既有案件不是唯一要測的情境。對一個目前完全沒有資料的 TEST-NEW，兩個 Session 若同時執行未加鎖的 MAX + 1，都可能得到：
MAX = NULL
ISNULL(MAX(...), 0) + 1 = 1
下一號 = 0001
測試前先確認該案件沒有任何資料：
DELETE FROM dbo.DocumentHeader
WHERE ProjectNo = 'TEST-NEW';
接著重複第二階段的 Session A、Session B 腳本，只要把兩邊的 ProjectNo 都改成 TEST-NEW。
預期行為是：Session A 先建立 0001 並保持交易未提交；Session B 在相同案件的查詢範圍等待。Session A COMMIT 後，Session B 才繼續並建立 0002，不能也取得 0001。
這個案例驗證的不只是「有資料時能鎖住最後一號」，還包括空集合時，HOLDLOCK 的 SERIALIZABLE 範圍保護能否在合適索引與執行計畫下，保護目前尚不存在的 Key Range。
測試結束後，要記得將兩個測試 Session COMMIT 或 ROLLBACK，避免自己留下鎖，再花半小時追查「為什麼資料庫突然卡住」。工程師偶爾也會親手製造非常逼真的事故現場。
UPDLOCK、HOLDLOCK 與唯一鍵可以避免兩個 Session 使用同一個流水號，卻不能判斷兩次請求是否來自同一次使用者操作。
例如第一次請求成功建立 0296，但 Client 因為連點按鈕或網路逾時而再次送出。第二次請求可以合法取得 0297，所以 (ProjectNo, ItemNo) 唯一鍵不會攔住它，最後仍可能多出兩張內容相同的單據。
這屬於 Idempotency 問題，而不是流水號 Race Condition 本身。
若系統需要防止重複送出，可以讓每次建立動作帶入一個唯一的 RequestId 或 OperationId，並在資料庫建立 Unique Constraint：
第一次送出 RequestId = ABC
→ 建立 0296
相同 RequestId 再次送出
→ 回傳原本結果，或明確拒絕
→ 不再建立 0297
完整的重試策略還要定義逾時、錯誤回傳與既有結果查詢。這次先把它與產號鎖定分開：鎖處理同號碰撞，Idempotency Key 處理同一操作被送出多次。
我把原本的產號 SQL 交給 AI 時，如果只問：
「這段
MAX + 1有問題嗎？」
AI 通常很快就會回答可能發生 Race Condition，並建議加入 Transaction、UPDLOCK、HOLDLOCK，甚至改用 SEQUENCE。
方向並不差，但仍然不夠落地。
因為 AI 不會自動知道：
所以我沒有直接請 AI「修好這段 SQL」，而是先要求它列出成立條件：
以下是目前的流水號產生與新增流程。
請先不要改程式，請分析：
1. 哪兩個步驟之間可能被其他 Session 插入？
2. Transaction 實際從哪裡開始、在哪裡結束？
3. 鎖定範圍是否符合流水號的唯一作用域？
4. 查詢條件需要什麼索引，才不會鎖住過大範圍？
5. 唯一鍵是否能作為最後一道防線？
6. 請設計兩個 Session 可重現的測試步驟。
請區分：
- 已從程式確認的事實
- 需要從 Schema 或執行計畫確認的條件
- 尚未驗證的推論
這種問法讓 AI 不只是丟出幾個 Lock Hint，而是協助我檢查整個解法需要哪些前提。
AI 很容易告訴你「加鎖」；工程師真正要判斷的是鎖哪裡、鎖多久，以及會不會把整張表都鎖得不能動。
這次我接受「把產號與新增包在同一個 Transaction，並使用 UPDLOCK、HOLDLOCK」的方向，但不是看到關鍵字就直接上線。
至少要再確認四件事：
如果缺少其中任何一項，程式可能只是從「偶爾重複」變成「不重複，但整套系統很慢」，或是「主檔安全了，明細仍然半套」。
💡 工程師判決：正確的鎖不是越多越好，而是在正確作用域內，剛好保護到交易完成。
這次改版相關的併發、失敗Rollback與重送測試，至少要涵蓋以下情境：
| 測試情境 | 預期結果 |
|---|---|
| 同一案件、兩個 Session 同時新增 | 依序取得不同流水號 |
| 全新案件、目前沒有任何流水號 | 兩個 Session 同時新增時，仍須依序取得 0001、0002，不得同時取得 0001 |
| 不同案件同時新增 | 若規則分案編號，兩邊不應互相長時間阻塞 |
| 第一個 Session 尚未 Commit | 第二個 Session 等待，不得取得相同基準值 |
| 第一個 Session Rollback | 第二個 Session 繼續後，依實際已提交資料產號 |
| 新增途中發生例外 | 不留下只有號碼、沒有完整主檔的資料 |
流水號已達 9999 |
明確阻擋，不產生五碼或回到 0000 |
| 歷史資料包含舊制格式 | 依已確認的歷史規則換算，不靠字串外觀猜測 |
Client 重複送出同一 RequestId |
由 Idempotency 機制回傳既有結果或拒絕，不再建立新單據 |
其中「不同案件能否同時新增」很重要。
如果同一時間只有一個人可以在整張表新增，雖然最不容易撞號，卻可能把原本只需要保護單一案件的規則放大成全系統瓶頸。
這就是為什麼併發安全不能只用「測試沒有錯誤」判定，還要觀察：
程式沒有撞號，只是最低標準；它還必須能在真實使用量下運作。
即使 MAX(...) 的併發控制正確，當單一案件資料量與產號頻率持續增加時，也可以考慮讓每個案件在 Counter Table 中各自保存一筆 LastValue。每次產號只鎖定該案件的 Counter Row，不必反覆從業務主檔尋找最大值。
這次為了降低 Legacy System 的改造範圍，仍採用 MAX + 1 + Lock；如果資料量與競爭成本繼續上升，再評估 Counter Table 會更合適。低侵入是這次的取捨，不是所有新系統的標準答案。
這個問題其實跟 Delphi 沒有直接關係。
只要系統採用「讀取目前狀態 → 計算下一個值 → 寫回」的流程，就可能遇到相同的 Race Condition，例如：
真正需要保護的不是某一行 MAX + 1，而是從讀取決策依據到完成寫入的整段 Critical Section。
單人測試證明功能會動；併發測試才證明多人一起使用時，資料仍然可信。
MAX + 1。UPDLOCK、HOLDLOCK 的鎖定範圍與索引行為。RequestId／OperationId 與唯一限制。今天真正修正的不是：
MAX + 1
而是：
讀取目前狀態
↓
鎖定正確範圍
↓
計算下一號
↓
完成新增
↓
提交後才回傳結果
MAX + 1 本身只是一個算式。真正危險的是把「讀取」與「寫入」拆成兩個可以被其他 Session 插隊的步驟。
AI 可以快速指出 Race Condition、提供 Lock Hint 與測試腳本，但它不會自動知道流水號的商業作用域、既有索引、舊資料規則與其他新增入口。這些仍需要工程師用 Schema、程式碼與實際操作逐一確認。
💡 今日金句：最難重現的 Bug，往往不是程式算錯，而是兩段都正確的程式剛好同時執行。
下一篇，我們會繼續追問一個更容易引發架構爭論的問題：
流水號與驗證規則到底該由誰負責——Delphi UI、Stored Procedure，還是 Trigger？
iThome鐵人賽
看影片追技術