未載入 Skill3318 tokens · 128760 ms
前提：以下是基於症狀的假設與計畫；未取得真實監控、慢查詢、鎖等待或部署設定，因此不能斷言 20 秒等待一定由鎖造成。最高概率的鏈條是「Serverless 連線暴增＋交易內等待付款 API，使連線與 invoice row lock 長時間不釋放」，但必須用資料驗證。
## P0：先止血並取得證據
1. **立即縮短交易**
- 禁止在 `BEGIN`～`COMMIT` 之間呼叫付款 API、重試、sleep 或做其他網路 I/O。
- 先不要靠提高 `max_connections` 解決；它可能只是把瓶頸移到 CPU、記憶體或 I/O。
- 對 invoice 寫入交易設定局部上限，例如：
```sql
BEGIN;
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '5s';
-- invoice update
COMMIT;
```
- 數值是 rollout 起始建議，不是由現有監控推導；應依既有 SLO 調整。鎖逾時應回傳可重試錯誤，搭配有限次數、指數退避與 jitter。
2. **保護資料庫連線額度**
- Serverless 執行流量改用 Supabase **transaction-mode pooler**（通常為 Supavisor、port `6543`），不要讓每個 invocation 直接連 Postgres。這正是官方建議給 Serverless/Edge 的模式。[Supabase：連線方式](https://supabase.com/docs/guides/database/connecting-to-postgres)
- 每個 Serverless instance 的 client-side pool 設為極小值，通常 `1`；若函式一次只做一段 DB 工作，可不建立額外 application pool，而由 transaction pooler 管理。
- 限制函式最大併發，讓最壞情況下的 DB 需求不超過可分配額度：
```text
所有應用 pool 上限總和
+ Supabase/PostgREST/Auth 等保留連線
+ 維運與遷移緩衝
< max_connections
```
- 盤點不同的 `database × role × pooling mode` 組合；它們可能各自形成 pool，不能只看單一 pool size。[Supabase：connection management](https://supabase.com/docs/guides/database/connection-management)
3. **先驗證相容性**
- Supavisor transaction mode 不支援部分 session-level 行為；prepared statements 也需依 driver/ORM 關閉或改成相容模式。不要把 migrations、`LISTEN/NOTIFY`、跨交易 temporary tables、session advisory locks 直接切到 transaction mode。[Supabase：停用 prepared statements](https://supabase.com/docs/guides/troubleshooting/disabling-prepared-statements-qL8lEL)
- migrations、備份及需要 session 語意的工作保留獨立連線設定，且不與 API runtime 共用連線預算。
## P1：確認 20 秒究竟在等什麼
在尖峰期間取樣；以下查詢本身不表示目前真的存在 blocker：
```sql
SELECT
pid,
application_name,
usename,
state,
wait_event_type,
wait_event,
now() - query_start AS query_age,
now() - xact_start AS transaction_age,
pg_blocking_pids(pid) AS blocking_pids,
left(query, 500) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY transaction_age DESC NULLS LAST;
```
針對阻塞鏈：
```sql
SELECT
blocked.pid AS blocked_pid,
blocked.application_name AS blocked_app,
now() - blocked.query_start AS blocked_for,
blocker.pid AS blocker_pid,
blocker.application_name AS blocker_app,
now() - blocker.xact_start AS blocker_xact_age,
blocked.wait_event,
left(blocked.query, 300) AS blocked_query,
left(blocker.query, 300) AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL
unnest(pg_blocking_pids(blocked.pid)) AS b(blocker_pid)
JOIN pg_stat_activity AS blocker
ON blocker.pid = b.blocker_pid
ORDER BY blocked_for DESC;
```
PostgreSQL 官方建議以 `pg_locks` 搭配 `pg_stat_activity` 檢視持鎖與等待 session；權限不足時可能看不到其他角色的完整 query。[PostgreSQL：pg_locks](https://www.postgresql.org/docs/current/view-pg-locks.html)
同時記錄並關聯：
- API request ID、invoice ID、DB `application_name`。
- 取得連線耗時、交易耗時、SQL 耗時、付款 API 耗時。
- active、idle、`idle in transaction`、等待連線數。
- `53300 too_many_connections`、pool timeout、lock timeout、deadlock。
- invoice 更新的 p50/p95/p99，以及付款 API latency。
- `pg_stat_statements` 中 invoice SQL 的 calls、mean/max execution time；先確認擴充已啟用。
判讀方式：
- **先等到連線才開始 SQL**：pool/併發或連線預算問題。
- `wait_event_type = 'Lock'` 且有 `blocking_pids`：鎖競爭。
- blocker 為 `idle in transaction`，交易時間接近付款 API latency：強烈支持外部呼叫延長持鎖假設。
- SQL 自身 active 很久、沒有 blocker：檢查執行計畫、索引、I/O 或資料量，不能歸因於 row lock。
## P1：重畫付款與 invoice 的交易邊界
資料庫與外部付款服務無法用一般 Postgres transaction 原子提交，應採狀態機、idempotency key 與補償/對帳：
```text
短交易 A
驗證 invoice
條件式更新為 payment_pending
建立 payment_attempt/outbox，保存唯一 idempotency_key
COMMIT
交易外
呼叫付款 API，傳同一 idempotency_key
短交易 B
依 payment_attempt ID 條件式寫入 succeeded/failed
更新 invoice 狀態
COMMIT
```
必要控制：
- `payment_attempt.idempotency_key` 加 `UNIQUE` constraint。
- 最終更新使用狀態或版本條件：
```sql
UPDATE invoices
SET status = 'paid', version = version + 1
WHERE id = $1
AND status = 'payment_pending'
AND version = $2;
```
`row_count = 0` 代表狀態已變，必須重新讀取，不可盲目覆寫。
- API timeout 或程序崩潰造成「結果未知」時，不可立刻換新 key 再扣款；先用原 idempotency key 查詢/重試。
- worker 需重試 outbox，另設 reconciliation job 對帳「付款成功但 DB 未完成」及其反向情況。
- 所有重試均假設至少一次執行，因此 DB 寫入和付款請求都必須冪等。
## P2：修正鎖定查詢與存取路徑
1. 確認 invoice 更新透過主鍵或高選擇性索引定位；在 staging 用 `EXPLAIN (ANALYZE, BUFFERS)` 驗證，避免生產環境直接分析可能產生副作用的寫入。
2. 多列、多表更新固定鎖定順序，例如永遠先 invoice、再 payment attempt，並依 ID 排序，降低 deadlock。
3. 只有確實需要「先讀後寫且防止並發修改」時才用：
```sql
SELECT ...
FROM invoices
WHERE id = $1
FOR UPDATE;
```
取得鎖後只做本地驗證與 DB 寫入，立即提交。
4. 互動式 invoice 更新可用 `NOWAIT` 或短 `lock_timeout`，快速回傳 conflict/retry；不要讓使用者請求沉默等待 20 秒。
5. `SKIP LOCKED` 只適合多 worker 從工作佇列領取任務；不適合一般 invoice 更新，否則可能把「未更新」誤當成功。
6. 檢查外鍵欄位索引、觸發器與級聯更新；它們也可能擴大鎖範圍或延長交易。
## 安全 rollout 與驗證
1. **先建立基線**
- 至少涵蓋一個代表性尖峰窗：連線占用、pool wait、lock wait、交易時間、invoice latency、錯誤率與付款重複/漏單數。
- 若目前拿不到這些資料，先加 telemetry，不能宣稱修正有效。
2. **預備環境驗證**
- 模擬低於、等於及高於預期尖峰的併發。
- 注入付款 API 慢 20 秒、timeout、500、回應後程序崩潰等故障。
- 驗證付款 API 變慢時 DB 交易與 row lock 仍快速結束。
- 驗證同一 invoice 的並發請求只產生一個有效 payment attempt，重試不會重複扣款。
- 驗證 transaction pooler 下 ORM、prepared statements、timeouts 與 migrations 的連線設定。
3. **分階段上線**
- 先切少量無付款或低風險流量到 transaction pooler。
- 再以 feature flag 啟用新交易邊界，例如 1% → 5% → 25% → 50% → 100%；每階段至少跨過預先定義的代表性負載窗。
- pooler 切換與付款狀態機分成兩個可獨立回滾的變更，以便定位效果。
4. **每階段通過條件**
- connection exhaustion 與 pool timeout 不增加，DB 保留連線仍有緩衝。
- invoice p95/p99、lock wait、最長 transaction age 朝既定 SLO 改善。
- DB latency 不再隨付款 API latency 同步上升。
- duplicate charge、漏記付款、卡在 `payment_pending` 的數量為零或低於事先核准門檻，且 reconciliation 能收斂。
- ORM/driver 沒有新增 prepared-statement 或 session-state 錯誤。
5. **回滾條件**
- 付款重複、狀態不一致或無法對帳時，立即停止新付款流量並保留 reconciliation worker；不要刪除 attempt/outbox 紀錄。
- pool timeout、DB errors 或 latency 超過預先設定門檻時，透過 feature flag 回切舊連線路徑或降低併發。
- 回滾應只切換程式與連線設定；schema 先採向後相容的 additive migration，待完整觀察期結束後再清理舊欄位與流程。
已載入 Skill +3728 tokens · 143680 ms
## 已知與待驗證
已知：
- 尖峰時 PostgreSQL connection slots exhausted。
- 每個請求建立新連線。
- 更新 `invoices` 偶爾等待約 20 秒。
- 交易內呼叫外部付款 API。
尚不能斷言：
- 20 秒是資料庫鎖等待、取得連線的排隊時間，或外部 API 延遲。
- 是否存在連線洩漏、缺少索引、deadlock 或特定熱點 invoice。
- 下列數值門檻需依實際容量與基準調整。
## 1. P0：先恢復連線可用性
### 診斷
同時量測並拆分請求時間：
- pool connection acquire
- `BEGIN` 到 `COMMIT`
- 外部付款 API
- `UPDATE invoices`
- commit/rollback
在尖峰期間採樣連線狀態：
```sql
select
application_name,
state,
wait_event_type,
wait_event,
count(*) as connections
from pg_stat_activity
where datname = current_database()
group by application_name, state, wait_event_type, wait_event
order by connections desc;
```
檢查長交易及 idle-in-transaction：
```sql
select
pid,
application_name,
state,
now() - xact_start as transaction_age,
now() - state_change as state_age,
wait_event_type,
wait_event,
pg_blocking_pids(pid) as blocking_pids
from pg_stat_activity
where datname = current_database()
order by xact_start nulls last;
```
這些查詢只能建立診斷證據，不能由目前描述推導出真實連線數或阻塞者。
### 修正
- 將 Serverless API 接到支援 **transaction pooling** 的外部／託管 pooler；只在每個 Serverless instance 內建立 pool，仍可能因 instance 數暴增而耗盡資料庫連線。
- 每個請求從 pool 借用連線並在 `finally` 中釋放，不再建立實體 PostgreSQL 連線。
- 設定全域連線預算：
```text
DB 可分配額度 =
max_connections
- 管理／故障排除保留
- migrations 與背景工作
- 其他服務額度
```
所有 Serverless instance、背景工作及 pooler 對資料庫的實體連線上限總和必須低於此預算。不要單純提高 `max_connections`；過多 backend 可能進一步耗盡記憶體並降低吞吐量。
- 設定短的 pool acquire timeout、API concurrency 上限與 backpressure；額度滿時快速回傳可重試錯誤，避免請求無界排隊 20 秒。
- 設定合理的 idle、連線存活與 idle-in-transaction timeout。
- 使用 transaction pooling 前，確認 driver/ORM 不依賴 session 狀態、temporary table 或跨交易的 named prepared statement；必要時改用 unnamed statements，或只讓確有需求的工作走 session pooling。
## 2. P0：把外部付款呼叫移出資料庫交易
外部網路呼叫位於交易內，很可能延長 row lock 與連線占用時間，但仍需由上述時間與阻塞資料確認。
建議改為三階段、可恢復的狀態機：
1. 短交易：以條件式更新把 invoice 從 `pending` 改為 `payment_processing`，建立唯一的 `payment_attempt` 與 idempotency key，隨即 commit。
2. 交易外：使用該 idempotency key 呼叫付款服務。
3. 短交易：記錄 provider payment ID，條件式將狀態改為 `paid` 或可重試／失敗狀態，隨即 commit。
例如：
```sql
begin;
update invoices
set status = 'payment_processing',
payment_attempt_id = $2,
updated_at = now()
where id = $1
and status = 'pending'
returning id;
commit;
```
付款完成後：
```sql
begin;
update invoices
set status = 'paid',
provider_payment_id = $2,
updated_at = now()
where id = $1
and status = 'payment_processing'
and payment_attempt_id = $3;
commit;
```
必要配套：
- `payment_attempt_id`／idempotency key 必須唯一。
- 重試付款請求時沿用同一 key，避免重複扣款。
- API timeout、process crash 或 commit 結果不明時，不可直接再次扣款；先向付款服務查詢。
- 用 webhook、outbox/job 或定期 reconciliation 修復長時間停在 `payment_processing` 的紀錄。
- 狀態轉移使用條件式 `UPDATE` 並檢查 affected rows，避免兩個請求同時付款。
## 3. P1：確認 20 秒是否為鎖等待
在問題發生期間執行：
```sql
select
blocked.pid as blocked_pid,
now() - blocked.query_start as blocked_for,
blocked.wait_event_type,
blocked.wait_event,
blocker.pid as blocker_pid,
now() - blocker.xact_start as blocker_transaction_age,
blocked.query as blocked_query,
blocker.query as blocker_query
from pg_stat_activity blocked
cross join lateral unnest(pg_blocking_pids(blocked.pid)) as b(blocker_pid)
join pg_stat_activity blocker
on blocker.pid = b.blocker_pid
where blocked.datname = current_database();
```
同時檢查：
- 是否反覆由同一 invoice ID 或同一工作流程造成熱點。
- blocker 是否正在等待外部付款 API，或處於 `idle in transaction`。
- 是否有 deadlock 記錄及 `pg_stat_database.deadlocks` 增量。
- 所有會鎖多筆 invoice／相關資料的交易是否採一致鎖定順序。
修正原則：
- 優先使用單一原子、條件式 `UPDATE`；沒有必要時不要先 `SELECT ... FOR UPDATE`。
- 必須鎖多筆資料時，依固定主鍵順序取得鎖。
- 背景 worker 競爭工作佇列時才考慮 `FOR UPDATE SKIP LOCKED`；一般 invoice 更新不應用它掩蓋衝突。
- 每筆交易可設定局部 timeout，例如：
```sql
begin;
set local lock_timeout = '2s';
set local statement_timeout = '5s';
-- short conditional update
commit;
```
timeout 後必須 rollback，並僅對具冪等性的操作採指數退避加 jitter 重試。實際秒數應先由正常延遲基準決定。
## 4. P1：用執行計畫驗證查詢與索引
蒐集實際 invoice 更新及其前置查詢，利用 `pg_stat_statements` 找出高總耗時、高平均耗時及高呼叫次數的語句。
在 staging 或可控條件下執行：
```sql
explain (analyze, buffers)
update invoices
set ...
where ...;
```
注意 `EXPLAIN ANALYZE` 會真正執行 DML；生產環境不可直接對真實 invoice 任意執行，可使用 rollback 包覆且確認沒有不可回滾的外部副作用，或先只用 `EXPLAIN`。
索引只依實際 predicate 與計畫新增：
- `WHERE id = $1` 通常已由 primary key 支援。
- `WHERE id = $1 AND status = ...` 不代表一定需要 `(id, status)`；主鍵通常已定位單列。
- 若工作查詢是 `WHERE status = ... ORDER BY updated_at ...`，才評估相符的複合或 partial index。
- 大表在生產新增索引時考慮 `CREATE INDEX CONCURRENTLY`，並確認 migration 工具不把它包在 transaction 中。
索引能修正掃描成本，但不能解除另一交易已持有的 row lock。
## 5. P1：安全 rollout 與驗證
### 上線前
- 記錄基準：DB 連線使用量、pool acquire p95/p99、交易時間、invoice update p95/p99、lock wait、deadlock、5xx、付款重複率及 reconciliation backlog。
- 在 staging 以代表性的 Serverless concurrency、付款 API 延遲／timeout 和相同 pool 配額進行負載及故障測試。
- 測試付款成功後程序崩潰、付款 timeout、DB commit 結果不明、webhook 重送及兩個請求同時付款。
### 分階段 rollout
1. 先部署可觀測性與連線時間拆分。
2. canary 啟用 pooler，逐步由 5% → 25% → 50% → 100% 流量；每階段跨過至少一個代表性尖峰。
3. pool 穩定後，再 canary 部署短交易／付款狀態機。
4. 索引與 timeout 個別 rollout，避免同時改動導致無法歸因。
### 每階段通過條件
- 實體 DB 連線峰值保持在預留額度內，且仍保有管理連線。
- pool acquire timeout、DB connection error 與 API 5xx 未惡化。
- 長交易、`idle in transaction`、鎖等待及 invoice update p99 明顯下降。
- 吞吐量未因 pool 過小而下降。
- 沒有新增 deadlock、付款重複、錯誤狀態轉移或 reconciliation backlog。
### 停止／回滾條件
若出現連線額度逼近上限、錯誤率或延遲顯著惡化、prepared-statement/session 相容錯誤，或任何重複扣款跡象，立即停止擴量。保留舊連線路徑與舊付款流程的 feature flag；狀態機回滾時仍須保留已產生的 idempotency key、attempt 記錄與 reconciliation，不能把處理中的付款直接重置後重扣。