Một câu `ALTER TABLE` chạy trên production làm treo cả ứng dụng dù bảng nhỏ. Chẩn đoán bằng `pg_locks` ra sao?

Nguyên nhân thường không phải bản thân ALTER TABLE chậm, mà là hàng đợi lock.

ALTER TABLE cần ACCESS EXCLUSIVE lock. Nếu có một transaction cũ (kể cả một SELECT chạy dài, hay session idle in transaction) đang giữ ACCESS SHARE trên bảng đó, ALTER TABLE phải xếp hàng chờ. Và vì lock được cấp theo thứ tự, mọi truy vấn đến sau — kể cả SELECT — bị chặn phía sau nó. Một bảng nhỏ vẫn có thể làm sập trải nghiệm toàn hệ thống theo cách này.

Chẩn đoán ai chặn ai:

select blocked.pid            as blocked_pid,
       blocked.query          as blocked_query,
       blocking.pid           as blocking_pid,
       blocking.state         as blocking_state,
       now() - blocking.xact_start as blocking_age
from pg_stat_activity blocked
join lateral unnest(pg_blocking_pids(blocked.pid)) as b(pid) on true
join pg_stat_activity blocking on blocking.pid = b.pid
where blocked.wait_event_type = 'Lock';

pg_locks cho biết chi tiết loại lock và cột granted; pg_blocking_pids() là đường tắt để ra ngay thủ phạm. Xử lý tức thời: select pg_cancel_backend(pid) (huỷ truy vấn) rồi pg_terminate_backend(pid) nếu cần.

Cách phòng — bắt buộc khi đổi schema lúc đang chạy:

set lock_timeout = '3s';
alter table orders add column note text;

lock_timeout khiến lệnh tự bỏ cuộc thay vì đứng chặn hàng đợi; thất bại rồi thử lại luôn tốt hơn là khoá cả hệ thống.

Kèm theo: tách migration thành các bước lock nhẹ, dùng CREATE INDEX CONCURRENTLY, và không chạy DDL trong cùng transaction với thao tác dài.