Timeout và quan sát lock: tìm session đang chặn và giới hạn thời gian chờ Trung cấp

Vấn đề

API đặt hàng trừ tồn kho bằng UPDATE products SET stock = stock - 1 WHERE id = 101. Pool của API có 10 connection. Một script admin chạy UPDATE cùng sản phẩm 101 trong transaction rồi dừng ở prompt, không COMMIT.

Mọi request đặt sản phẩm 101 chờ row-level lock, mỗi request giữ một connection. Mười request là đủ cạn pool; request đặt sản phẩm khác cũng chờ pool, nên p99 của toàn API tăng vọt dù CPU database gần như rảnh.

Bài này trả lời hai câu: timeout nào cắt được chuỗi chờ này, đặt ở đâu, và làm sao tìm ra session gốc đang chặn.

Chọn gì? (tóm tắt trước)

Tình huống	Chọn	Điều kiện
Request không được chờ lock lâu	lock_timeout theo role app	Nhỏ hơn statement_timeout và deadline request
Query chậm, không chờ lock	statement_timeout	Theo query hợp lệ chậm nhất, cộng biên
Code quên COMMIT/release()	idle_in_transaction_session_timeout	Pool chịu được connection bị đóng
Chặn transaction dài bất kể lý do	transaction_timeout (17+)	Không áp cho job batch hợp lệ
Đang sự cố, cần tìm blocker	pg_stat_activity + pg_blocking_pids()	Đi ngược cây tới gốc rồi mới kill
Cần dấu vết lần chờ lâu	log_lock_waits = on	Ngưỡng là deadlock_timeout

Khái niệm cốt lõi

Bốn timeout trong PostgreSQL 17

Cả bốn mặc định 0 (tắt); giá trị không ghi đơn vị tính bằng mili giây.

Tham số	Đo cái gì	SQLSTATE	Huỷ gì
lock_timeout	Mỗi lần chờ lấy một lock	55P03 lock_not_available	Câu lệnh
statement_timeout	Từ lúc lệnh tới server đến khi xong, gồm cả chờ lock	57014 query_canceled	Câu lệnh
idle_in_transaction_session_timeout	Thời gian rảnh trong transaction đang mở	25P03	Session
transaction_timeout (mới ở 17)	Tổng thời gian transaction, kể cả transaction ngầm của một câu	25P04	Session

Hệ quả:

• Huỷ câu lệnh trong transaction tường minh làm transaction hỏng; app phải ROLLBACK trước khi dùng lại connection.
• Huỷ session là FATAL: connection bị đóng, pool phải loại bỏ và tạo mới.
• statement_timeout khác 0 và ≤ lock_timeout thì lock_timeout không bao giờ bắn.
• transaction_timeout ≤ một trong hai timeout idle/statement thì timeout dài hơn bị bỏ qua. Prepared transaction không chịu transaction_timeout.

deadlock_timeout và log_lock_waits

deadlock_timeout (mặc định 1s) là thời gian chờ trước khi server kiểm tra deadlock. Nó không huỷ lần chờ bình thường.

log_lock_waits ghi log khi một lần chờ vượt deadlock_timeout. Mặc định off ở PostgreSQL 17 và 18. Hai tham số này chỉ superuser hoặc role có quyền SET tương ứng đổi được.

Cách hoạt động

Chuỗi chờ trong tình huống trên, với lock_timeout = 2s cho role API; idle timeout của role script rút còn 5 giây cho gọn hình:

Blocker idle in transaction làm cạn pool; lock_timeout trả connection, idle timeout nhả lock

◀ Trước▶ PhátTiếp ▶↺ Từ đầuBước 1/7

admin-script UPDATE sản phẩm 101 rồi để transaction mở: idle in transaction, row lock vẫn giữ.

Bước	Ai giữ lock	Ai chờ	Pool
0	admin-script, idle in transaction	Không ai	Bình thường
1–3	admin-script	order-api-1 (transactionid), order-api-2 (tuple)	Cạn khi đủ 10 waiter
4	admin-script	Waiter nhận 55P03 sau 2 giây	Connection quay về
5–6	Không ai: session admin bị đóng, rollback	Request mới lấy lock ngay	Hồi phục

lock_timeout bảo vệ pool nhưng không gỡ blocker; idle_in_transaction_session_timeout gỡ blocker nhưng phải đợi đủ ngưỡng. Cần cả hai.

Chi tiết vận hành

Đặt timeout ở đâu

Tài liệu khuyên không đặt ba timeout huỷ lệnh/transaction trong postgresql.conf vì ảnh hưởng mọi session, gồm migration và bảo trì. Thứ tự ưu tiên từ thấp đến cao:

1. ALTER DATABASE ... SET.
2. ALTER ROLE ... SET, rồi ALTER ROLE ... IN DATABASE ... SET. Chỉ áp cho session mới.
3. options trong connection string. Đã chạy thử: role đặt 3s, options='-c lock_timeout=1s' cho 1s.
4. SET trong session; SET LOCAL chỉ đến hết transaction.

ALTER ROLE order_api SET lock_timeout = '2s';
ALTER ROLE order_api SET statement_timeout = '5s';
ALTER ROLE order_api SET idle_in_transaction_session_timeout = '10s';
ALTER ROLE admin_ops SET idle_in_transaction_session_timeout = '5min';

BEGIN;                              -- migration
SET LOCAL lock_timeout = '500ms';   -- chỉ trong transaction này
-- ALTER TABLE ...
COMMIT;

Qua PgBouncer chế độ transaction, SET cấp session có thể dính sang client khác; dùng ALTER ROLE hoặc SET LOCAL. Migration dùng lock_timeout ngắn kèm retry vì DDL đang chờ chặn mọi query sau nó (xem lock queue).

Quan hệ với timeout của app và driver

Theo timeout budget, timeout co lại khi đi vào trong: deadline request > statement_timeout > lock_timeout. Ví dụ deadline 8 giây: statement_timeout = 5s, lock_timeout = 2s.

Timeout phía app hết trước thì app ngừng chờ, nhưng server có thể vẫn chờ lock nếu driver không gửi cancel. Timeout phía server mới bảo đảm lần chờ dừng thật, kèm SQLSTATE để app map sang 503 hoặc retry.

Ví dụ thực tế

Flash sale: 10 connection, một script quên COMMIT

Pool order-api có 10 connection, sản phẩm 101 nhận 200 request/giây. Không có lock_timeout, chưa tới một giây cả pool đã chờ sau script admin. Có lock_timeout = 2s, request đặt sản phẩm 101 lỗi nhanh, endpoint khác vẫn có connection. Số liệu là kịch bản minh hoạ.

Demo: cây blocker → blocked

Chạy trên PostgreSQL 17.11 (examples/docker-compose.yml), schema demo_lock_timeouts. Ba session psql đặt application_name:

1. admin-script: BEGIN; UPDATE ... WHERE id = 101; rồi không gửi gì thêm.
2. order-api-1, rồi order-api-2: UPDATE ... SET stock = stock - 1 WHERE id = 101;.

Session thứ tư in cây chờ, gốc ở trên. Query gọi pg_blocking_pids() đúng một lần mỗi waiter vì hàm có chi phí:

WITH RECURSIVE waiting AS (
  SELECT pid, pg_blocking_pids(pid) AS blockers
  FROM pg_stat_activity
  WHERE wait_event_type = 'Lock'
), tree AS (
  SELECT r.pid, 0 AS depth, ARRAY[r.pid] AS path
  FROM (SELECT DISTINCT unnest(blockers) AS pid FROM waiting) r
  WHERE r.pid NOT IN (SELECT pid FROM waiting)
  UNION ALL
  SELECT w.pid, t.depth + 1, t.path || w.pid
  FROM tree t
  JOIN waiting w ON t.pid = ANY (w.blockers)
  WHERE w.pid <> ALL (t.path)
)
SELECT repeat('  ', t.depth) || t.pid AS pid_tree,
       a.application_name AS app, a.state, a.wait_event,
       round(extract(epoch FROM now() - a.xact_start)) AS xact_s,
       left(a.query, 45) AS query
FROM tree t
JOIN pg_stat_activity a USING (pid)
ORDER BY t.path;

 pid_tree  |     app      |        state        |  wait_event   | xact_s |                     query
-----------+--------------+---------------------+---------------+--------+-----------------------------------------------
 17864     | admin-script | idle in transaction | ClientRead    |      4 | UPDATE demo_lock_timeouts.products SET stock
   17871   | order-api-1  | active              | transactionid |      3 | UPDATE demo_lock_timeouts.products SET stock
     17884 | order-api-2  | active              | tuple         |      2 | UPDATE demo_lock_timeouts.products SET stock

Cách đọc:

• Gốc không chờ gì (ClientRead), state = idle in transaction, xact_start cũ nhất.
• query của session idle là câu cuối cùng đã chạy, không phải câu đang chạy.
• Gốc có PID 0 là prepared transaction. Waiter có nhiều blocker xuất hiện nhiều lần.

Output thật của các timeout

Waiter thứ ba với SET lock_timeout = '2s' và \set VERBOSITY verbose:

ERROR:  55P03: canceling statement due to lock timeout
LOCATION:  ProcessInterrupts, postgres.c:3399
Time: 2001.100 ms (00:02.001)

Blocker đặt idle_in_transaction_session_timeout = '3s'; waiter vào sau khoảng 1 giây:

-- blocker
FATAL:  25P03: terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
-- waiter
UPDATE 1
Time: 1999.035 ms (00:01.999)

• SET statement_timeout = '1s' khi đang chờ lock: ERROR: canceling statement due to statement timeout (57014).
• SET transaction_timeout = '2s'; BEGIN; SELECT pg_sleep(3);: FATAL: 25P04: terminating connection due to transaction timeout, session bị đóng khi đang chạy.

Cancel hay terminate

Đang tải diagram…

• pg_cancel_backend(pid): huỷ câu lệnh đang chạy; waiter bị cancel nhận 57014.
• pg_terminate_backend(pid, timeout): đóng session, rollback, nhả mọi lock; timeout (ms) khiến hàm chờ process thoát thật.
• Cần superuser, là thành viên role sở hữu session, hoặc có pg_signal_backend.

Demo: sau pg_terminate_backend(pid, 5000) trên blocker, waiter kế tiếp UPDATE 1 ngay. PID và số giây đổi mỗi lần chạy.

Trade-offs

Ưu điểm

• lock_timeout biến chờ vô hạn thành lỗi nhanh có SQLSTATE riêng, giữ pool cho endpoint khác.

• Timeout cấp session tự gỡ blocker, không cần người trực.

< slot="cons">

• Ngưỡng thấp gây lỗi giả lúc tải cao; app cần retry có giới hạn và idempotent.

• Timeout session đóng connection: pool và driver phải chịu được FATAL.

• Timeout chỉ cắt triệu chứng; transaction dài vẫn là lỗi thiết kế.

Nhược điểm

🎮 Thử tay

• Migration khoá cả bảng: tìm phiên chặn bằng pg_blocking_pids và huỷ đúng phiên.

Checklist production

1. Đặt lock_timeout, statement_timeout, idle_in_transaction_session_timeout bằng ALTER ROLE cho từng role app; kiểm tra bằng SHOW từ connection mới.
2. Giữ deadline request > statement_timeout > lock_timeout.
3. Map 55P03, 57014 sang lỗi retry có giới hạn; coi 25P03, 25P04 là connection chết.
4. Migration dùng SET LOCAL lock_timeout ngắn kèm retry.
5. Bật log_lock_waits trên PostgreSQL 17/18.
6. Mọi connection đặt application_name theo service.
7. Cảnh báo theo số session idle in transaction quá ngưỡng và số session chờ Lock.
8. Runbook: chạy query cây, lưu query và xact_start của gốc, rồi mới terminate.

Sai lầm thường gặp

Lỗi hay gặp nhất lúc sự cố là xử lý nhầm session:

Cancel blocker đang idle in transaction

pg_cancel_backend chỉ huỷ câu lệnh đang chạy. Session idle in transaction không chạy gì, nên hàm trả t mà lock vẫn giữ. Dùng pg_terminate_backend, sau khi lưu query và application_name để tìm code quên COMMIT.

Cách hiểu sai	Hệ quả	Cần làm
deadlock_timeout giới hạn thời gian chờ	Waiter vẫn chờ vô hạn	Dùng lock_timeout
Kill waiter đầu tiên thấy	Waiter sau lên đầu hàng	Đi ngược tới gốc
query của session idle là câu đang chạy	Đổ lỗi nhầm câu lệnh	Đó là câu cuối; xem state, xact_start
lock_timeout bằng statement_timeout	App chỉ thấy 57014, không biết là chờ lock	Đặt lock_timeout nhỏ hơn rõ

Tự kiểm tra

1. Vì sao lock_timeout giữ được pool nhưng không gỡ được blocker của sản phẩm 101?
2. pg_blocking_pids() của order-api-2 trả về order-api-1. Kill order-api-1 thì sao?
3. transaction_timeout = 30s, statement_timeout = 60s, một câu chạy 45 giây: lỗi gì, huỷ gì?
4. Vì sao timeout phía client hết hạn không chứng minh lần chờ lock trên server đã dừng?

Tóm tắt

Tóm tắt

• lock_timeout (55P03) và statement_timeout (57014) huỷ câu lệnh; idle_in_transaction_session_timeout (25P03) và transaction_timeout (25P04) đóng session.
• Đặt theo role hoặc SET LOCAL; deadline request > statement_timeout > lock_timeout.
• Gốc cây chờ thường là idle in transaction, không chờ gì, xact_start cũ nhất.
• Blocker idle phải terminate; cancel chỉ huỷ câu đang chạy.
• log_lock_waits mặc định off ở 17 và 18.

Tham khảo

• Client Connection Defaults: default và tương tác của bốn timeout.
• Error Codes: 55P03, 57014, 25P03, 25P04.
• Lock Management và Logging: deadlock_timeout, log_lock_waits.
• pg_stat_activity: state, query, xact_start, wait_event_type.
• Session Information Functions: pg_blocking_pids(), hard/soft block, chi phí.
• Server Signaling Functions: cancel, terminate, quyền.