這篇要回答的問題：handler 入口已經 dedup 了（篇 3），為什麼 SQL 寫法本身還要再考慮冪等？哪些 SQL idiom 跑 N 次跟跑 1 次結果一樣、哪些跑 N 次會扣 N 次餘額？
3 分鐘結論
- Handler 入口 dedup 不夠 ⸺ 「業務寫完了、markDone 前 crash」這個窗存在
- SQL 寫入分 6 類：純 INSERT / 絕對值 UPDATE / delta UPDATE（地雷） / UPSERT / DELETE / INSERT 無 PK
- Delta UPDATE 不冪等的解法：ledger + balance pattern（PK 防重 + balance 跟著 ledger 動）
- ON CONFLICT 不等於冪等 ⸺ DO UPDATE 內的 SET 才決定冪等性
這篇假設你知道
- 篇 3 Consumer 端冪等性 ⸺ handler 入口 dedup 是這篇的雙保險另一邊
- 基本 SQL（
INSERT/UPDATE/DELETE/ PK / WHERE） - 不熟下面這些詞時 → #16 EDA 名詞速查：idempotency / ACID / ON CONFLICT / UPSERT / ledger + balance pattern
後續篇：handler 內並發控制（event 亂序、跨 row 鎖）見 篇 4b 事件亂序 + 跨 row 鎖。
完整 runnable demo：tools3455147/mq-event-driven-demo。
1. 一個經典翻車場景
電商系統，handler 在 event 抵達時扣庫存：
// Laravel handler
public function handle(OrderCreated $event) {
foreach ($event->order->items as $item) {
DB::table('inventory')
->where('item_id', $item->id)
->decrement('qty', $item->quantity); // ← UPDATE inventory SET qty = qty - 1
}
}跑得起來，QA 也通過。直到 production 撞到下面這幾件事：
- broker 因為 ack 丟失重投同個 event → 同個 order 扣兩次庫存
- worker crash 重啟、Laravel
queue:retry-all觸發 → 扣三次 - 業務客訴：「我訂單明明只 1 件，但庫存少了 3 個。」
工程師反應：「我有用 篇 3 dedup 表 啊？怎麼還會扣多次？」
翻 log 發現是這個情境：
1. event 到 handler，dedup check 通過（第一次）
2. UPDATE inventory SET qty = qty - 1 ← 已執行
3. handler 還沒寫 processed_events 表，process crash（OOM / K8s kill）
4. broker 重投（dedup table 沒記 → isDone = false）
5. UPDATE inventory SET qty = qty - 1 又跑一次 → 重複扣
dedup table 防不到「業務寫入後、markDone 前 crash」這個視窗。即使把 dedup INSERT 跟業務 UPDATE 包同 transaction，也只是把這個視窗縮到極小 — 不是消除。
更根本的問題：那行 SQL 本身不是冪等的。跑 1 次扣 1 個、跑 2 次扣 2 個。即使 dedup 99.9% 擋下來，0.1% 漏網的會造成永遠對不平的庫存。
下面 §4 把 SQL 寫入分 6 類，逐個講「天然冪等 vs 不冪等」跟「不冪等怎麼改」。
2. Newcomer primer：先把幾個概念對齊
什麼叫「冪等」
數學定義：f(f(x)) = f(x)。實務翻成 SQL：跑 1 次跟跑 N 次的最終 DB 狀態一模一樣。
冪等的 SQL：
UPDATE users SET status='paid' WHERE id=$1— 跑 N 次 status 都是 ‘paid’DELETE FROM cart WHERE user_id=$1— 跑 N 次 cart 都是空的INSERT INTO orders (id, ...) VALUES (...) ON CONFLICT (id) DO NOTHING— 跑 N 次 orders 表都只有那一筆
不冪等的 SQL：
UPDATE balance SET amount = amount - 10 WHERE user_id=$1— 跑 N 次扣 N×10INSERT INTO orders (id, ...) VALUES (...)沒 ON CONFLICT — 第二次撞 PK 直接 throwINSERT INTO logs (...) VALUES (...)— 不指定 PK 的 INSERT，跑 N 次有 N 筆 log
SQL transaction 跟 ACID
SQL transaction 是「多條 SQL 包成原子單位」：
BEGIN;
INSERT INTO orders ...
UPDATE inventory ...
COMMIT;- 要嘛 INSERT + UPDATE 都生效，要嘛都不生效（atomicity）
- transaction 內看不到別人未 commit 的資料（isolation）
- commit 後資料不會因為 power outage 消失（durability）
- 任何時刻 DB 都符合 constraints（consistency）
ACID 的 A（atomicity）跟 I（isolation）是本篇大量會用的特性。冪等性的設計常常等價於「把多條 SQL 包進 transaction，讓 PK constraint 當原子鎖」。
ON CONFLICT 是 PostgreSQL/MySQL 都有的關鍵字
INSERT INTO orders (id, user_id) VALUES (123, 1)
ON CONFLICT (id) DO NOTHING;
-- 第二次跑同樣 SQL，因為 PK 撞，跳過。不 throw、不報錯- PostgreSQL:
ON CONFLICT (col) DO NOTHING/DO UPDATE SET ... - MySQL:
INSERT IGNORE(DO NOTHING) /ON DUPLICATE KEY UPDATE(DO UPDATE) - SQLite: 同 PG 語法
ON CONFLICT 是冪等寫入的核心 idiom。整篇大量會用到。
3. 為什麼還需要 handler 層冪等
篇 3 已經有 processed_events 表擋 broker 重投了，為什麼還要再做 SQL 層的冪等？雙保險的具體理由：
理由 1：dedup table 跟業務寫入的「時間窗」
理想路徑（dedup 跟業務同 transaction）：
BEGIN;
INSERT processed_events ... ← markDone
UPDATE balance ... ← 業務寫入
COMMIT;
→ 任何時間 crash 都是「兩個一起 rollback」，broker 重投時 dedup check 為 false，handler 再跑 → safe
實際 demo 的路徑（為了 retry loop 拆開）：
retry loop:
try:
handler 寫業務表 ... ← 業務寫入先
catch:
retry
dedup.markDone(...) ← 之後才 markDone
→ 「業務寫完了、markDone 前 crash」這個窗存在
→ broker 重投時 dedup check 為 false，業務寫入跑第二次 🔥
production 嚴格的版本應該把 dedup INSERT 包進業務 transaction，但即便如此 ⸺
理由 2：跨 transaction 邊界的呼叫
業務寫入不一定只在 DB。某些 handler 還會 call 外部 API：
async function processPayment(event) {
// 1. call 外部支付 API
await stripe.charges.create({ amount: event.amount });
// 2. 寫 DB
await db.query('INSERT INTO payments ...');
// 3. markDone
await dedup.markDone(...);
}如果 step 1 成功、step 2 失敗 throw：broker 重投時 step 1 又會跑一次 → 錢扣兩次。Stripe 自己的 Idempotency-Key 機制可以擋，但本質是「外部呼叫的冪等性不可能用本地 transaction 解」。
理由 3：跨 service 的 retry
跨 service 場景（service A 給 service B 推 event，service B 給 service C 推 event）：
service A: publish OrderCreated
↓
service B: handler 寫 inventory
↓
service B: publish InventoryDeducted
↓
service C: handler 寫 accounting
service B 跟 service C 各有自己的 dedup table，互相看不到對方。service B 的 handler 重跑（不管什麼原因）就會推第二個 InventoryDeducted。service C 收到第二個就會記第二筆 accounting。
跨 service 沒有 global dedup，唯一可靠的解是每個 service 的 handler 內部都做冪等。
結論：兩層各自的職責
| 層級 | 工具 | 解什麼 | 限制 |
|---|---|---|---|
| 入口層 | processed_events 表 | broker 重投同 event | 跨 transaction 邊界 / process crash 邊角 window 漏網 |
| 內部層 | SQL idiom + ON CONFLICT + version check | 業務寫入本身可重跑 | 設計時要逐個 SQL 分類，沒有萬靈丹 |
兩層做完，handler 才真的對任何重投都安全。下面 §4 講「內部層」的 6 種 SQL idiom。
4. 6 種 DB 寫入語意各自的冪等策略
把 handler 內可能出現的 SQL 寫入分成 6 類，分別講每類怎麼處理。每節 5 分鐘讀完，可獨立看。
4.1 純 INSERT + PK constraint — 天然冪等
INSERT INTO orders (event_id, user_id, items, status)
VALUES ($1, $2, $3, 'pending')
ON CONFLICT (event_id) DO NOTHING;- PK 是
event_id（業務上是「本筆訂單對應的事件 id」） - 跑第二次撞 PK → DO NOTHING → 直接跳過，不報錯
這是最乾淨的冪等。沒有狀態變化、不需要 WHERE 判斷、PK 自己就是鎖。
Demo 對應
本系列 demo 4 個原始 handler（email / inventory_movement / analytics / recommender）都是這個 pattern：
INSERT INTO emails_sent (event_id, user_id) VALUES ($1, $2)
ON CONFLICT (event_id) DO NOTHING;emails_sent 表 PK 是 event_id，跑 N 次最多寫一筆。
適用場景
「一個 event 對應一筆紀錄」型的寫入：
- 寄一封信
- 記一筆操作 log
- 寫一筆推播訊號
- 記一筆 audit trail
幾乎所有 audit / log / immutable record 都該長這樣。不要省略 PK，不要省略 ON CONFLICT。
4.2 絕對值 UPDATE（state machine）— 天然冪等
UPDATE orders SET status = 'paid', updated_at = NOW()
WHERE id = $1;- 把欄位設成指定值（不是相對某舊值算）
- 跑 N 次 status 都是 ‘paid’
關鍵在「絕對值」三個字：
-- 冪等 ✅
UPDATE orders SET status = 'paid';
-- 不冪等 ❌（相對值，跑 N 次 status_history 累 N 步）
UPDATE orders SET status_history = status_history || ',paid';Demo 對應
本系列 demo 的 orderStatusHandler + /demo/order-status endpoint：
# 先建一筆 order
EVENT_ID=$(curl -X POST http://localhost:8000/demo/place-order \
-H 'Content-Type: application/json' \
-d '{"user_id":1,"items":["A"]}' | jq -r '.event_id')
# 把 status 改成 paid
curl -X POST 'http://localhost:8000/demo/order-status' \
-H 'Content-Type: application/json' \
-d "{\"order_event_id\":\"$EVENT_ID\",\"to\":\"paid\"}"
# 再下同個請求 → status 還是 paid，沒新動作
curl -X POST 'http://localhost:8000/demo/order-status' \
-H 'Content-Type: application/json' \
-d "{\"order_event_id\":\"$EVENT_ID\",\"to\":\"paid\"}"業務上「從 N 個合法狀態之一 → 一個確定的新狀態」的場景幾乎都該用絕對值 UPDATE。
適用場景
- 訂單狀態變更（pending → paid → shipped → completed）
- 使用者狀態（active → suspended → deleted）
- 任務進度（new → in_progress → done）
state machine 設計的副作用是天然冪等：只要 to-state 是同一個，重跑就沒副作用。
4.3 Delta UPDATE — 不冪等，這是地雷
UPDATE inventory SET qty = qty - 1 WHERE item_id = $1;- 把欄位設成「現值減去某數」
- 跑 1 次扣 1、跑 2 次扣 2、跑 N 次扣 N
這是經典的不冪等 SQL。任何「SET col = col ± delta」格式的 UPDATE 都會在 broker 重投時翻車。
為什麼新人很容易寫這種 SQL
因為直覺：要扣庫存就 qty = qty - 1。Laravel decrement() / Eloquent $model->increment() 等 helper API 用起來很順手 ⸺ 但底下生成的就是 delta UPDATE，不冪等。
Demo 在 demo 重現 bug
mq-event-driven-demo 的 /demo/inventory-delta-bug?safe=0 故意演這個翻車路徑：
# 起始庫存：100
# safe=0 走「dangerous delta UPDATE」path：UPDATE balance SET qty = qty - 1
curl -X POST 'http://localhost:8000/demo/inventory-delta-bug?safe=0&item_id=demo-1&delta=1' | jq
# 回應：
# {
# "event_id": "9b22...",
# "before_qty": 100,
# "after_qty": 99,
# "warning": "THIS IS A DEMO OF WHAT NOT TO DO IN PRODUCTION"
# }
# 用「replay」endpoint 模擬 broker 重投同個 event
curl -X POST "http://localhost:8000/demo/inventory-delta-replay?event_id=9b22...&safe=0" | jq
# 回應：
# {
# "after_qty": 98 ← 扣兩次了 🔥
# }同個 event_id 扣了兩次。dedup table 沒擋到，因為這個 demo endpoint 故意繞過 dedup 直接示範 SQL 層的問題。
解法：見 §4.4
4.4 Delta UPDATE 的解法：ledger + balance pattern
SET col = col ± delta 沒救嗎？有，加一個「記事件」的 ledger 表：
-- Ledger：append-only，每筆扣減一筆 row，PK 是 event_id
CREATE TABLE inventory_ledger (
event_id UUID PRIMARY KEY,
item_id TEXT NOT NULL,
delta INT NOT NULL,
applied_at TIMESTAMPTZ DEFAULT NOW()
);
-- Balance：snapshot 當下數字
CREATE TABLE inventory_balance (
item_id TEXT PRIMARY KEY,
qty INT NOT NULL
);Handler 寫入：
BEGIN;
-- 1. INSERT ledger（同 event_id 撞 PK 會 throw）
INSERT INTO inventory_ledger (event_id, item_id, delta)
VALUES ($1, $2, $3);
-- 2. UPDATE balance（只有 step 1 成功才會跑到）
UPDATE inventory_balance SET qty = qty - $3
WHERE item_id = $2;
COMMIT;關鍵性質：
- ledger PK 是 event_id：重投時 INSERT 撞 PK → throw → transaction rollback → balance 也不變
- ledger 是 append-only 流水，可以稽核「歷史上每筆扣減」
- balance 是當下狀態，查餘額不用每次 sum 整個 ledger
對應 demo
?safe=1 走 ledger + balance path：
curl -X POST 'http://localhost:8000/demo/inventory-delta-bug?safe=1&item_id=demo-2&delta=1' | jq
# { "event_id": "...", "before_qty": 100, "after_qty": 99 }
# Replay：ledger PK 衝突 → rollback → balance 不變
curl -X POST "http://localhost:8000/demo/inventory-delta-replay?event_id=...&safe=1" | jq
# { "after_qty": 99, "note": "ledger PK conflict → balance unchanged" }業務語意完全對：扣了一次就是一次，重投不會多扣。
Production 真實系統的擴展
Demo 是最小骨架。Production inventory 系統還會：
- 加 type 欄位（‘reserve’ / ‘commit’ / ‘refund’ / ‘adjust’）
- 加 source_order_id 反查
- 用 SUM(ledger.delta) 驗證 balance 沒漂移
- 加 idempotency_key 在 ledger（如果 event_id 不夠表達邏輯單位）
但核心 idiom 不變：append-only ledger 當冪等鎖、balance 跟著 ledger 動。任何「絕對不能多算 / 少算」的計數（餘額 / 點數 / 庫存 / 倉位 / 訂閱用量）都該用這 pattern。
4.5 UPSERT (ON CONFLICT DO UPDATE) — 要小心冪等性
UPSERT 是「沒就 INSERT、有就 UPDATE」：
INSERT INTO user_profile (user_id, name, email, updated_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (user_id) DO UPDATE SET
name = EXCLUDED.name,
email = EXCLUDED.email,
updated_at = NOW();這個冪等嗎？ 看 SET clause 是絕對值還是相對值：
- ✅ 冪等：
SET name = EXCLUDED.name— 跑 N 次 name 永遠是 event 帶來的那個值 - ❌ 不冪等：
SET balance = balance + EXCLUDED.delta— 跑 N 次扣 N 次
UPSERT 的 ON CONFLICT 分支本質就是 UPDATE，§4.2 跟 §4.3 的規則完全適用。新人常以為「我用了 ON CONFLICT 就冪等了」，錯。ON CONFLICT 只解了「PK 衝突不會 throw」這件事，不解決「SET 的內容是不是冪等」。
適用場景
「sync 一份外部資料到本地表」型的 handler 適合 UPSERT：
-- 從 Stripe webhook 同步 customer 資料：
INSERT INTO customers (stripe_id, email, name, last_synced_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (stripe_id) DO UPDATE SET
email = EXCLUDED.email,
name = EXCLUDED.name,
last_synced_at = NOW();每次 webhook 進來都把本地表 sync 成最新值 — 跑 N 次結果一樣，因為 SET 都是絕對值。
4.6 DELETE — 天然冪等
DELETE FROM cart_items WHERE order_id = $1;- 第一次跑刪掉 row
- 第二次跑 WHERE 沒對應到任何 row → 不報錯、無副作用
DELETE 本身就是冪等。但有兩個 corner case：
- DELETE 跟 SELECT 的競賽：如果同 transaction 內先 SELECT 數量再 DELETE，重投時兩個值會不一致。把判斷邏輯改用
DELETE ... RETURNING拿實際刪除筆數 - soft delete：
UPDATE rows SET deleted_at = NOW() WHERE id=$1也是冪等（絕對值 UPDATE），但WHERE deleted_at IS NULL加上去後就要小心 race（兩個 handler 同時嘗試刪同筆）
6 種 SQL idiom 速查表
| Idiom | 範例 | 天然冪等 | 修法 |
|---|---|---|---|
| INSERT + PK | INSERT ... ON CONFLICT DO NOTHING | ✅ | — |
| 絕對值 UPDATE | SET status = 'paid' | ✅ | — |
| Delta UPDATE | SET qty = qty - 1 | ❌ | 用 ledger + balance pattern (§4.4) |
| UPSERT（SET 是絕對值） | ON CONFLICT DO UPDATE SET name=EXCLUDED.name | ✅ | — |
| UPSERT（SET 是相對值） | SET qty = qty + EXCLUDED.delta | ❌ | 看作 delta UPDATE，用 §4.4 |
| DELETE | DELETE FROM ... WHERE id=$1 | ✅ | — |
| INSERT 無 PK / 無 ON CONFLICT | INSERT INTO logs ... | ❌ | 加 PK 或外部 dedup |
新人 checklist：handler 內每一行 SQL 對應哪一類？非冪等的有沒有改？
5. 反思
寫這篇遇到最棘手的概念是「ON CONFLICT 不等於冪等」。很多人看到 ON CONFLICT DO NOTHING 就以為自動冪等了 ⸺ 但實際上：
INSERT ... ON CONFLICT DO NOTHING：✅ 冪等INSERT ... ON CONFLICT DO UPDATE SET col = EXCLUDED.col：✅ 冪等（絕對值）INSERT ... ON CONFLICT DO UPDATE SET col = col + EXCLUDED.delta：❌ 不冪等
ON CONFLICT 解的是「不要因 PK 衝突而 throw」這件事，不是「自動讓寫入冪等」。SET clause 是不是冪等要另外判斷。
更深一層的觀察：冪等性是個 application-level 的設計選擇，不是 framework 或 DB 自動處理的東西。Postgres 不會幫你判斷 qty = qty - 1 該不該重跑，你的 schema 跟 SQL 寫法決定了它能不能重跑。
EDA 系統「至少一次交付」是基礎前提，那麼每一行寫入 DB 的 SQL 都得在這個前提下重新 review。這不是「production 才需要管」，是「決定要用 EDA 那一刻就要管」。
最後一個問題給看完這篇的你：
拿出你最近寫的一個 listener / consumer / event handler。每一行 SQL 對應到本篇 §4 的哪一類？有沒有 delta UPDATE？如果 broker 把這個 event 重投三次，DB 最終狀態還對嗎？
接下來：handler 內並發控制 ⸺ event 亂序到達該怎辦、跨 row 寫入怎麼互斥 ⸺ 見 篇 4b 事件亂序 + 跨 row 鎖。
6. 相關文章
本系列前置：
- 篇 1 Production EDA 入門 — naive EDA 撐不住的 4 個問題
- 篇 2 Outbox pattern — producer 端 dual-write
- 篇 3 Consumer 端冪等性 — handler 入口前 dedup（本篇是「入口後 SQL 寫法」的雙保險）
本系列接下來：
- 篇 4b 事件亂序 + 跨 row 鎖 — 並發控制 + Laravel 對譯
- 篇 5 → Retry 跟 DLQ — handler 失敗該怎麼處理
- 篇 6 → EDA 端到端追蹤 — audit 怎麼觀察「同個 event 是否被處理過」
外部參考：
Runnable demo: tools3455147/mq-event-driven-demo — /demo/inventory-delta-bug?safe=0|1 直接重現 delta UPDATE 翻車跟 ledger 解法的對比
設計文件: docs/architecture.md