題幹與適用情境
資料表 doctorshifts(teamid, shiftdate, doctorid, on_call) 記錄值班狀態，業務規則是同一團隊、同一班次至少有 1 名醫師值班。Alice 和 Bob 都在班上，兩個請求同時嘗試讓不同醫師離班。每個交易先統計值班人數；只要人數大於 1，就更新自己的醫師資料列。
本題限定 PostgreSQL 18。初始值班人數是 2，兩個交易都在任何一方提交前完成讀取，之後分別更新不同資料列。數字與資料表結構是面試假設，不是醫療系統的通用資料模型。重點是跨多列述詞 count(on_call) >= 1，不討論排班核准、時區或權限。
這類問題也會出現在庫存不可為負、帳號必須保留核准人、叢集至少保留一個主節點等後端規則。只說「放進交易」並不足夠：原子性保證單一交易全部成功或全部失敗，隔離層級才決定並行交易能看到什麼，以及資料庫是否拒絕無法以序列順序解釋的結果。
面試官考察點
第一個訊號是能否辨識寫入偏斜。兩個交易讀取相同述詞，卻寫入不同資料列，因此不會形成一般的同列寫入衝突。每個交易單獨看都符合前置檢查，合併結果卻讓值班人數變成 0。若把它稱為髒讀或單純的遺失更新，會導向錯誤修正。
第二個訊號是能否區分標準名稱與資料庫實作。PostgreSQL 的 Read Committed 每個陳述式取得新快照；Repeatable Read 使用交易層級的穩定快照，實際屬於快照隔離，仍允許序列化異常；Serializable 在類似快照的讀取方式上追蹤讀寫相依，發現結果無法對應任何序列順序時中止交易。其他資料庫同名層級的鎖定與快照語意可能不同，不能直接套用。
第三個訊號是從不變量選擇並行控制邊界。不變量跨越多列時，鎖住「自己要更新的醫師資料列」不會讓兩個交易相遇。安全方案必須讓它們競爭同一個可鎖定物件、把規則收斂到同一列的原子條件，或交由 Serializable 偵測衝突。
最後看失敗處理。Serializable 與明確鎖定都可能讓交易中止；前者常見 40001，鎖定順序不一致還可能產生 40P01。好的回答會重試完整交易、限制嘗試次數，並把電子郵件等外部副作用放到成功提交後的冪等流程。
回答前需要釐清的問題
- 使用哪一種資料庫與版本？ 本題依 PostgreSQL 18 回答。若換成 MySQL、SQL Server 或分散式資料庫，要重新確認同名隔離層級的實作與重試錯誤。
- 規則只涉及一個班次嗎？ 若不變量位於
(teamid, shiftdate)層級，就能替每個班次建立一個穩定競爭點；若規則跨區域或跨資料庫，單一資料庫的列鎖無法覆蓋完整邊界。 - 值班狀態有哪些寫入路徑？ 請假、調班、批次匯入與後台修復都必須遵守同一協定。只修一個 API，旁路寫入仍會破壞計數或繞過鎖定。
- 衝突是偶發還是持續高頻？ 低衝突時，Serializable 加重試通常最清楚；高衝突熱點可用單列條件更新減少無效工作，但會把該班次變成序列熱點。
- 允許請求等待多久？ 悲觀鎖會等待持鎖交易。若 API 延遲預算嚴格，應使用短交易、鎖定等待上限，以及明確的「狀態已變更，請重試」結果。
- 交易內是否有外部副作用？ 重試會重新執行交易邏輯。寄信、呼叫排班服務或寫入非交易訊息必須延後到提交後，或使用交易式 outbox 與冪等消費。
30 秒回答架構
「這是寫入偏斜：兩個交易都從同一組值班資料做判斷，卻更新不同醫師資料列，所以 Repeatable Read 的穩定快照仍可能讓兩者提交。PostgreSQL Serializable 會追蹤這類讀寫相依，至少中止一個交易；應用程式必須依 40001 重跑完整決策。若不用 Serializable，我會把每個班次對應到同一列 roster，在 Read Committed 下原子執行 active_count > 1 的條件遞減，並於同一交易更新醫師狀態；也可以先鎖 roster 列，再重新統計。最後用兩個連線在讀取後設置屏障，證明弱隔離會得到 0，而安全方案最後正好保留 1 人。」
分步驟深入解答
先寫出並行歷程。初始 Alice=true, Bob=true：
T1: read count(on_call) = 2
T2: read count(on_call) = 2
T1: update Alice to false
T2: update Bob to false
T1: commit
T2: commit若先依序執行 T1，T2 應讀到 1 並拒絕；若先執行 T2，T1 也應拒絕。實際結果為 0，無法等價於任何一種序列順序，因此屬於序列化異常。它與遺失更新的關鍵差異是：T1 與 T2 沒有覆寫同一列，一般列級寫入鎖不會自然衝突。
在 Read Committed 下，兩個統計陳述式各自看到陳述式開始時已提交的資料。依題目的屏障，兩者都先讀到 2，之後針對不同目標列的 UPDATE 都能提交。後續陳述式會取得新快照，但不會回頭推翻已做出的業務判斷。
PostgreSQL 的 Repeatable Read 為整個交易保留同一快照，避免同一交易內的不可重複讀與幻讀，卻允許序列化異常。兩邊更新不同版本鏈，沒有同列並行更新所觸發的回滾條件，所以兩者可能都提交。MVCC 說明讀取如何避免阻塞寫入；它本身不等於 Serializable，也不會自動理解「至少一人值班」這項業務規則。
第一種安全方案是 Serializable：
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) AS active_count
FROM doctor_shifts
WHERE team_id = 42
AND shift_date = DATE '2026-07-20'
AND on_call;
-- Reject when active_count <= 1.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
AND shift_date = DATE '2026-07-20'
AND doctor_id = 101
AND on_call;
COMMIT;兩個請求同時執行時，PostgreSQL 偵測到述詞讀取與並行寫入組成無法序列化的相依，不能讓兩個交易都成功提交。失敗交易回傳 SQLSTATE 40001。重試必須從 BEGIN 前重新執行，包括重新統計與決定是否更新；只重播最後一個 UPDATE 會沿用過期判斷。重試應加入隨機退避、最大次數與總截止時間，因為高競爭時第二次仍可能失敗。
Serializable 的優點是業務程式碼直接圍繞真實述詞表達規則，不必替每項不變量手工設計鎖。代價是相依追蹤成本與衝突重試；交易讀取集合越大、持續越久、競爭越集中，失敗機率通常越高。索引 (teamid, shiftdate)、短交易，以及提交前不等待網路呼叫，都能縮小重疊視窗。
第二種方案把跨列不變量投影到單列 oncallrosters(teamid, shiftdate, active_count)，並在 Read Committed 下原子更新：
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE on_call_rosters
SET active_count = active_count - 1
WHERE team_id = 42
AND shift_date = DATE '2026-07-20'
AND active_count > 1
RETURNING active_count;
-- Continue only when exactly one roster row was returned.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
AND shift_date = DATE '2026-07-20'
AND doctor_id = 101
AND on_call;
-- Commit only when both updates changed exactly one row; otherwise roll back.
COMMIT;兩個請求會競爭同一列 roster。PostgreSQL 的 Read Committed 在等待並行更新完成後，會針對新版本重新檢查 activecount > 1；第一個從 2 改成 1，第二個條件失敗並回傳 0 列。可以再替 activecount 加上列級 CHECK (active_count >= 1) 作為最後防線。代價是所有值班、離班與資料修復路徑都必須在同一交易維護計數；還要定期比對計數與明細，發現漂移就告警，而非靜默覆寫。
若不想維護計數，可以替每個班次保留穩定 roster 列，在 Read Committed 交易的第一步對它執行 SELECT ... FOR UPDATE，取得鎖後再統計明細並更新。第二個請求等待後，下一個 SELECT 會取得包含第一次提交的新快照。關鍵是所有寫入路徑先鎖定同一列 roster，且交易保持短小。若在 Repeatable Read 中先建立舊快照，再只鎖一個未被修改的 guard 列，後續統計仍可能看到舊資料；「有 FOR UPDATE」不是脫離快照語意的萬用證明。
直接鎖定所有目前值班醫師也要謹慎。鎖定集合來自述詞，遺漏的新列、不同查詢順序或旁路寫入都會改變安全性；多列鎖定順序不一致還會死結。若確實採用多列鎖，應依穩定主鍵順序取得，並對 40P01 重跑完整交易。只鎖「我要請假的醫師」必定不足，因為兩個請求仍鎖定不同資料列。
驗證時不要依序呼叫兩個 API。使用兩個獨立資料庫連線與一個測試屏障：兩邊都開始 Repeatable Read，執行統計並確認都得到 2，屏障再同時放行更新與提交。基線應穩定重現「兩次提交、最終為 0」。換成 Serializable 後，斷言不得出現兩次成功；一個交易收到 40001，完整重試後看到 1 並拒絕離班。換成 roster 條件更新後，斷言恰好一個 RETURNING 有結果，最終明細與 active_count 都是 1。
重複執行這個並行案例並打亂提交順序，同時涵蓋同一醫師的重複請求、第三名醫師加入、交易在兩次更新之間回滾、鎖定等待逾時與死結。線上監控 40001、40P01、重試次數、鎖定等待、交易時間、最終失敗率，以及 roster 計數與明細的差異。衝突率突然升高通常表示熱點、長交易或新寫入路徑進入同一邊界，不能只用無限重試掩蓋。
高品質示範回答
「我會先判定這是寫入偏斜。兩個交易檢查同一條跨列規則，但 Alice 和 Bob 分別更新自己的資料列，因此列級寫入鎖不會讓它們衝突。依兩邊都先讀到 2 的執行順序，Read Committed 可以讓兩者提交；PostgreSQL Repeatable Read 雖然讓每個交易持續看到穩定快照，仍可能得到無法用任何序列順序解釋的最終結果 0。
我會優先用 Serializable 表達這項規則。交易內統計目前班次的值班人數，大於 1 才更新。PostgreSQL 會追蹤述詞讀取與並行寫入的相依；此例不能讓兩個交易都提交，其中一個會收到 40001。應用程式捕捉後，從頭重新執行統計與更新，並限制重試次數。通知等外部副作用不放在可能重試的交易中，成功提交後再由冪等 outbox 處理。
如果這個班次是高競爭熱點，我會考慮 oncallrosters 列。離班交易在 Read Committed 下用 UPDATE ... SET activecount = activecount - 1 WHERE active_count > 1 RETURNING ... 競爭同一列，成功後再更新醫師明細；任何一步沒有更新一列就整體回滾。如此第二個請求會在最新列版本上重新檢查條件並失敗，代價是所有寫入路徑都必須同步維護計數。
驗收時我會用兩個連線與屏障固定交錯順序。先證明 Repeatable Read 下兩邊能讀到 2 並把結果寫成 0；再證明 Serializable 正好有一個交易中止，重試後拒絕；計數方案則只有一個條件更新成功。最後監控序列化失敗、死結、鎖定等待與計數漂移，確認安全性不是靠測試時序剛好成立。」
常見錯誤
- 只說「用了交易就安全」 → 原子性沒有規定並行交易的可見性與提交順序 → 指出具體隔離層級，並寫出能破壞不變量的交錯歷程。
- 把寫入偏斜當成遺失更新 → 兩個交易寫入不同資料列，針對單列版本號的檢查不會涵蓋共享述詞 → 找出跨列不變量，建立共同競爭點或使用 Serializable。
- 宣稱 Repeatable Read 等於 Serializable → 穩定快照仍可能產生無法序列解釋的合併結果 → 依 PostgreSQL 實作說明它允許序列化異常。
- 只鎖定各自醫師資料列 → Alice 與 Bob 的鎖互不衝突 → 鎖定同一列 roster、統一計數列，或讓 SSI 偵測述詞相依。
- 在 Repeatable Read 舊快照後隨手加
FOR UPDATE→ 鎖住一個未變更的 guard 列不會更新交易快照 → 在 Read Committed 下鎖定後重讀，或改用 Serializable／可變計數列。 - 收到
40001只重試COMMIT或最後一段 SQL → 業務判斷仍來自舊快照 → 重跑整個交易及決定 SQL 的應用程式邏輯。 - 無限立即重試 → 熱點下請求會同步碰撞並放大資料庫負載 → 使用帶抖動退避、最大次數與總截止時間的重試策略。
- 在交易內先發通知再提交 → 序列化失敗重試可能送出重複通知 → 延後外部副作用，使用交易式 outbox 與冪等消費。
- 維護計數卻允許旁路更新明細 →
active_count與實際值班人數漂移，條件更新失去意義 → 讓所有寫入路徑共享交易協定並持續對帳。
追問及應對
追問一：為什麼一個原子 UPDATE 能解決庫存不可為負，卻不一定能解決本題？
單列庫存可寫成 UPDATE inventory SET stock = stock - 1 WHERE stock > 0，條件與寫入落在同一列；並行更新會在最新版本上重新檢查條件。本題條件是多列計數，寫入卻只碰到一名醫師。要取得相同性質，需要把值班人數具體化到同一列 roster，或使用能偵測述詞相依的 Serializable。
追問二：Serializable 失敗率很高怎麼辦？
先縮短交易、補齊述詞索引、移除交易內網路呼叫，並依班次觀察衝突分布。若少數熱門班次占大部分 40001，可只把這些寫入改成 roster 條件更新，讓競爭在一列上排隊。若所有班次都高衝突，應重新檢查批次操作與交易邊界，不能用更高重試上限取代容量分析。
追問三：能否只用 CHECK 約束保證至少一人值班？
直接放在單一 doctorshifts 列上的檢查只能驗證該列欄位，無法讓該列獨立證明同班次還有其他值班醫師。把不變量投影成 roster 的 activecount 後，CHECK (active_count >= 1) 才能成為資料庫層防線；明細與計數仍必須在同一交易更新並進行對帳。
追問四：服務程序在 roster 計數遞減後當機怎麼辦？
只要計數與醫師狀態位於同一資料庫交易，連線中斷會讓未提交交易回滾，兩者不會只完成一半。若已經提交但用戶端沒有收到回應，請求結果屬於未知；重試應帶業務操作 ID，並先讀取醫師目前狀態，避免將同一次離班再次計數。
追問五：規則擴展成兩個資料庫中的醫師總數怎麼辦？
單一資料庫的 Serializable 與列鎖只能保護本庫可見資料。需要先選一個權威寫入邊界，例如把班次容量集中到一個強一致 roster 服務，再由其他資料庫非同步投影；或引入分散式交易並接受協調可用性與延遲成本。若兩個資料庫各自判斷後非同步合併，就必須明確允許短暫違規與補償策略，不能沿用單庫安全結論。