最近在處理一個付款流程問題時，遇到一支 API 發生 SQL timeout。

第一時間我先往 SQL query 本身去想，但後來把整個 request flow 跟時間線整理出來後，才發現問題跟前一支付款 API 的 transaction scope 有關。

這次排查剛好把幾個平常分開看的概念串在一起：

  • Transaction
  • Database Lock
  • Third-party API
  • Retry
  • Compensation
  • Idempotency
  • Race Condition

這篇就記錄一下整個思考過程。

問題現象

流程大概是這樣：

付款 API
   ↓
呼叫第三方系統
   ↓
後續查詢 API
   ↓
成功頁面

實際發生問題時，付款 API 呼叫第三方系統後等待了很長一段時間。

同時間，後續另一支 API 需要查詢資料，但最後發生 SQL timeout，導致後面的流程沒有繼續。

一開始看到 SQL timeout，很自然會先懷疑：

Query 是不是太慢？

但把兩支 API 的時間線放在一起看後，我注意到前一支付款 API 有使用 transaction，而且兩個流程都會碰到同一筆資料。

開始懷疑 Blocking

我的思路是：付款 API 有 transaction，後面的 API 又在讀相同資料，而且前面的 transaction 還沒有結束，那就有可能是 lock 還沒被釋放。

不過這時候還只能算懷疑，所以還是要去確認 database session、blocking 狀況和實際執行時間。

最後確認到後面的 query 其實大部分時間都在等待前一個 transaction。

也就是說，看到 SQL timeout 時，不能直接等同於「SQL 執行很慢」。有時候 query 本身沒有問題，它只是一直被 blocking。

Transaction Scope 太大

再往前查後，原本流程可以簡化成：

BEGIN TRANSACTION

    更新資料庫

    呼叫第三方 API

    更新資料庫

COMMIT

問題就在第三方 API call 被放進 transaction 裡。

第三方 API 的回應時間是不可控的。可能很快，也可能因為對方系統、網路或其他狀況等很久。

如果它在 transaction 裡面，第三方等多久，我這邊的 transaction 就可能跟著維持多久。這段期間如果 lock 還被持有，其他 request 就可能一起被擋住。

整個狀況大概會變成：

付款 API
↓
Transaction 開始
↓
Update DB
↓
Call 第三方 API
↓
等待很久
↓
Transaction 尚未結束

另一支 API：

查詢相同資料
↓
被 Blocking
↓
等待
↓
SQL Timeout

這次真正要處理的點，就是 transaction 維持時間太長。

第三方 API 為什麼不適合放在 Transaction 裡

我一開始想到的理由很簡單：

第三方 API 不可控。

但往下拆後，實際風險是 transaction 的生命週期會被外部服務一起拉長。

DB operation 本身可能只需要幾百毫秒，但第三方 API 如果等了 10 秒，整個 transaction 可能就跟著卡 10 秒以上。

所以我現在會比較偏向這個原則：

Transaction scope 越小越好，只包真正需要一起成功或失敗的 DB operation。

像 HTTP request、第三方 API、檔案 I/O 這類外部操作，都要特別小心要不要放進 transaction。

移出去後又遇到一致性問題

如果改成：

Call 第三方 API
↓
第三方成功
↓
Update DB

接著就會遇到另一個問題。

假設第三方成功，但 DB update 失敗：

第三方：成功
DB：失敗

這時候兩邊狀態就不一致，而 DB transaction 也沒辦法幫第三方 rollback。

第一個想到的解法：Retry 或 Compensation

我當時第一個想到兩種方向。

第一種是把 DB update 失敗記錄下來，之後再 retry。

第二種是如果第三方有提供 cancel、refund 或 rollback API，就呼叫一個補償操作。

例如：

第三方付款成功
↓
DB update 失敗
↓
Call Refund / Cancel API

這也就是 compensation 的概念。

如果第三方沒有提供 rollback 機制，就比較可能要靠 retry，把自己的 DB 狀態補回來。

Retry 一定要考慮 Idempotency

只要開始 retry，就要想下一個問題：

同一個操作被重跑很多次怎麼辦？

例如 retry job 跑三次，如果每次都新增資料，就可能出現重複紀錄。

所以這時候就要考慮 idempotency。

我的理解是：

同一個操作不管執行一次還是很多次，最後系統狀態都應該一致。

例如可以用第三方交易編號當 unique key：

TransactionId = ABC123

同一個 ABC123 不管 retry 幾次，都只能代表同一筆交易。

為什麼先查再 Insert 還是不夠

很直覺的寫法可能是：

if (!exists)
{
    Insert();
}

但這樣還是可能有 race condition。

例如兩個 request 同時進來：

Request A：查不到
Request B：也查不到

Request A：Insert
Request B：Insert

兩邊查詢當下都沒資料，所以最後還是有可能重複。

因此 application layer 可以先檢查，但資料庫還是要有最後一道保護。

例如：

UNIQUE CONSTRAINT

或：

UNIQUE INDEX

讓 DB 直接保證同一個 transaction id 只能存在一筆。

Unique Constraint 撞到後怎麼處理

如果 retry 時撞到 unique constraint，我原本會直覺想：

那就當這次 retry 成功。

但這裡還是要再確認一次既有資料。

例如確認：

TransactionId 一樣
Amount 一樣
User 一樣
Status 是預期狀態

如果資料一致，就可以合理視為之前其實已經成功過。

如果 transaction id 一樣，但內容卻不一致，那就是另一個資料一致性問題，不能直接忽略。

這次最大的收穫

這次一開始只是看到：

SQL Timeout

後來一路往前追變成：

SQL Timeout
↓
Blocking
↓
Long Transaction
↓
Third-party API 等太久

接著為了解 transaction scope，又一路延伸到：

Third-party API 移出 Transaction
↓
跨系統狀態不一致
↓
Retry / Compensation
↓
Idempotency
↓
Race Condition
↓
Unique Constraint

這些東西平常分開看都不算陌生，但真的放進同一個 production issue 裡，就比較能理解它們之間的關係。

總結

這次我整理下來幾個重點：

  • SQL timeout 不一定代表 query 本身慢，也可能是在等 lock
  • Transaction scope 要盡量小
  • Third-party API 這類不可控操作要小心放進 transaction
  • 跨系統一致性通常要靠 retry 或 compensation
  • 只要有 retry，就要考慮 idempotency
  • Application layer 的 exists check 不能完全避免 race condition，DB constraint 還是很重要

這次之後，我在看 production issue 時會更習慣先把整個 request flow 跟時間線拉出來。

最後爆出 error 的地方，很多時候只是結果，真正的 root cause 可能在更前面的流程。