Bài trước cho thấy khóa có thể làm một truy vấn chờ. Khi điều đó xảy ra trên production — một truy vấn "treo" không rõ lý do — bạn cần chẩn đoán ngay: ai đang chặn ai, và làm sao gỡ. PostgreSQL cung cấp pg_locks và hàm pg_blocking_pids() cho đúng việc đó. Bài này dựng một sự cố chặn thật rồi chạy các truy vấn chẩn đoán để bạn có sẵn "bộ đồ nghề" khi cần.

Bối cảnh: một truy vấn đang treo

Tôi dựng tình huống: session A UPDATE hàng id=1 rồi giữ giao dịch mở (chưa commit); session B cố UPDATE cùng hàng id=1 nên bị chặn. Giờ session B đang "treo" và ta cần tìm ra vì sao.

Bốn bước chẩn đoán

Bước 1 — tìm ai đang chờ khóa qua pg_stat_activity. Cột wait_event_type='Lock' là dấu hiệu một truy vấn đang bị chặn bởi khóa:

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity WHERE wait_event_type='Lock';

Bước 2 — pg_blocking_pids(), cách hiện đại và gọn nhất. Hàm này trả về mảng các PID đang chặn một PID cho trước:

SELECT pid, pg_blocking_pids(pid) AS bi_chan_boi
FROM pg_stat_activity WHERE wait_event_type='Lock';

Ảnh chụp đoạn mã SQL nền tối minh hoạ đọc pg_locks để tìm khóa chờ ai đang chặn ai, bước 1 tìm truy vấn đang chờ khóa pg_stat_activity SELECT pid state wait_event_type wait_event query FROM pg_stat_activity WHERE wait_event_type bằng Lock truy vấn này đang bị chặn bởi một khóa, bước 2 pg_blocking_pids cách hiện đại gọn nhất SELECT pid pg_blocking_pids pid AS bi_chan_boi FROM pg_stat_activity WHERE wait_event_type bằng Lock trả về mảng các PID đang chặn PID này vd 43487, bước 3 cây chặn đầy đủ blocked blocking cộng thời gian chờ SELECT blocked.pid blocked.query AS truy_van_bi_chan blocking.pid blocking.query AS truy_van_chan now trừ blocked.query_start AS cho FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid bằng ANY pg_blocking_pids blocked.pid WHERE blocked.wait_event_type bằng Lock, bước 4 pg_locks thô granted false là kẻ đang chờ SELECT pid locktype mode granted FROM pg_locks granted t đang giữ khóa granted f đang chờ khóa chờ khóa hàng hiện ra dạng ShareLock trên transactionid của kẻ giữ, bước 5 gỡ hủy truy vấn hoặc ngắt kết nối kẻ chặn SELECT pg_cancel_backend 43487 hủy truy vấn hiện tại của kẻ chặn nhẹ SELECT pg_terminate_backend 43487 ngắt hẳn kết nối mạnh tay hơn ưu tiên cancel chỉ terminate khi cancel không đủ

Hình 1: Năm bước chẩn đoán khóa chờ — tìm kẻ đang chờ qua pg_stat_activity, dùng pg_blocking_pids tìm kẻ chặn, dựng cây chặn đầy đủ, đọc pg_locks thô (granted=false), và gỡ bằng pg_cancel_backend/pg_terminate_backend.

Bước 3 — cây chặn đầy đủ, truy vấn thực dụng nhất khi gỡ sự cố. Nó nối pg_stat_activity với chính nó qua pg_blocking_pids() để hiện cả truy vấn bị chặn lẫn kẻ chặn, kèm thời gian đã chờ:

SELECT blocked.pid, blocked.query AS truy_van_bi_chan,
       blocking.pid, blocking.query AS truy_van_chan,
       now()-blocked.query_start AS cho
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type='Lock';

Bước 4 — pg_locks thô, khi cần chi tiết mức khóa: cột granted=false chỉ ra kẻ đang chờ.

Đo thật: kết quả

Ảnh chụp bảng kết quả đo thật nền tối PID 43494 bị chặn bởi PID 43487 đã chờ 15 giây bối cảnh A giữ UPDATE id 1 B chờ UPDATE id 1 cùng hàng PostgreSQL 16, 1 pg_stat_activity ai đang chờ khóa pid 43487 state active wait_event_type Timeout wait_event PgSleep A đang giữ khóa pid 43494 state active wait_event_type Lock wait_event transactionid B đang chờ khóa, 2 pg_blocking_pids gọn nhất pid 43494 bi_chan_boi 43487 wait_event_type Lock 43494 bị chặn bởi 43487, 3 cây chặn đầy đủ thực dụng nhất khi gỡ sự cố pid_bi_chan 43494 truy_van_bi_chan UPDATE kho SET sl bằng sl cộng 5 pid_chan 43487 truy_van_chan BEGIN UPDATE kho cho_giay 15.0, 4 pg_locks thô granted false là kẻ đang chờ pid 43487 locktype relation mode RowExclusiveLock granted t mức bảng 2 writer không chặn nhau pid 43494 relation RowExclusiveLock t pid 43494 transactionid ShareLock granted f đang chờ xin khóa txid của 43487, gỡ SELECT pg_cancel_backend 43487 hủy truy vấn kẻ chặn B chạy tiếp ngay

Hình 2: Kết quả thật. pg_stat_activity: PID 43494 wait_event_type=Lock. pg_blocking_pids(43494)={43487}. Cây chặn: 43494 bị chặn bởi 43487, đã chờ 15 giây. pg_locks: kẻ chờ có dòng ShareLock trên transactionid với granted=f.

Đọc các kết quả:

  • pg_stat_activity: PID 43487 đang PgSleep (giữ khóa), PID 43494 có wait_event_type=Lock — đây là kẻ đang chờ.
  • pg_blocking_pids(43494) = {43487} — trả lời trực tiếp: 43494 bị chặn bởi 43487.
  • Cây chặn: 43494 (UPDATE ... sl+5) bị chặn bởi 43487 (BEGIN; UPDATE ... sl+1), đã chờ 15 giây. Đây là bức tranh đầy đủ nhất để quyết định gỡ.
  • pg_locks thô: cả hai PID đều giữ RowExclusiveLock mức relation với granted=t (khóa bảng của hai writer không chặn nhau). Dòng quan trọng: PID 43494 xin ShareLock trên transactionid với granted=f — đây chính là cách một cuộc chờ khóa hàng hiện ra trong pg_locks: kẻ chờ xin khóa transaction id của kẻ giữ để đợi giao dịch đó kết thúc.

Gỡ sự cố

Khi đã biết PID kẻ chặn, có hai cách gỡ:

SELECT pg_cancel_backend(43487);     -- hủy truy vấn hiện tại của kẻ chặn (nhẹ)
SELECT pg_terminate_backend(43487);  -- ngắt hẳn kết nối của kẻ chặn (mạnh tay)

Ưu tiên pg_cancel_backend — nó chỉ hủy truy vấn đang chạy, giao dịch rollback, kẻ chặn nhả khóa và kẻ chờ chạy tiếp. Chỉ dùng pg_terminate_backend khi cancel không đủ (ví dụ kẻ chặn đang idle in transaction — không có truy vấn để hủy, phải ngắt kết nối).

Đánh đổi cần cân nhắc

idle in transaction là thủ phạm phổ biến nhất. Nhiều sự cố khóa không phải do truy vấn chạy lâu, mà do một giao dịch mở rồi bỏ đó (ứng dụng quên commit/rollback). Kẻ chặn hiện state='idle in transaction' — pg_cancel_backend không giúp (không có truy vấn để hủy), phải pg_terminate_backend. Đặt idle_in_transaction_session_timeout để PostgreSQL tự ngắt các giao dịch treo này.

pg_locks có thể rất lớn và không cho ngữ cảnh. Trên hệ thống bận, pg_locks có hàng nghìn dòng và không kèm nội dung truy vấn. Luôn dùng truy vấn "cây chặn" (join với pg_stat_activity) để có bức tranh có nghĩa, thay vì đọc pg_locks thô. Lưu sẵn truy vấn này ở nơi dễ lấy.

Chuỗi chặn có thể nhiều tầng. A chặn B, B chặn C, C chặn D... pg_blocking_pids() chỉ cho tầng trực tiếp. Trên sự cố phức tạp, cần lần theo chuỗi (hoặc dùng truy vấn đệ quy) để tìm gốc — kẻ chặn đầu tiên không bị ai chặn. Gỡ gốc thường giải phóng cả chuỗi.

Ba ý mang về

  1. pg_blocking_pids(pid) là cách nhanh nhất tìm kẻ chặn: đo thật, nó trả về {43487} cho PID 43494 đang chờ — không cần tự giải mã pg_locks thô để biết ai chặn ai.
  2. Truy vấn "cây chặn" (join pg_stat_activity qua pg_blocking_pids) là công cụ thực dụng nhất: hiện cả truy vấn bị chặn lẫn kẻ chặn kèm thời gian chờ (15 giây) — bức tranh đầy đủ để quyết định gỡ; pg_locks thô chỉ cần khi soi chi tiết mức khóa (granted=false là kẻ chờ).
  3. Gỡ bằng pg_cancel_backend (ưu tiên) hoặc pg_terminate_backend: chú ý thủ phạm phổ biến là idle in transaction (phải terminate, và đặt idle_in_transaction_session_timeout), và chuỗi chặn nhiều tầng cần lần tới gốc.

Phần sau ta xét tình huống khóa nguy hiểm nhất — khi hai bên chặn lẫn nhau: Phần sau mổ xẻ deadlock — PostgreSQL tự phát hiện và hủy một giao dịch thế nào, và cách thiết kế để tránh.