Lock queue: vì sao một ALTER TABLE đang chờ làm mọi SELECT phải xếp hàng Trung cấp

Vấn đề

Migration thêm cột vào bảng orders: ALTER TABLE orders ADD COLUMN note text. Không có DEFAULT nên PostgreSQL chỉ sửa metadata, không ghi lại bảng; trên máy dev chạy vài mili giây.

Trên production, một report đã SELECT từ orders trong transaction chưa kết thúc. Migration đứng chờ. Ngay sau đó mọi API đọc orders cũng chờ, connection pool cạn.

Report chỉ đọc, API chỉ đọc, hai bên không xung đột với nhau. Vì sao một DDL “nhanh” mà chưa chạy được lại chặn cả các SELECT vốn không liên quan tới report?

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

Tình huống	Chọn	Điều kiện
DDL cần ACCESS EXCLUSIVE trên bảng nóng	lock_timeout ngắn + retry có backoff	Migration chạy lại được an toàn
Có session giữ lock lâu trên bảng	Tìm và xử lý blocker trước	Được phép huỷ session đó
DDL ghi lại bảng (đổi kiểu, default volatile)	Tách theo expand–contract	App chịu được giai đoạn chuyển tiếp

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

Mỗi câu lệnh lấy table-level lock trên bảng nó đụng tới. Đủ 8 mode ở bài table-level locks; ở đây chỉ cần hai:

Mode	Ai lấy	Xung đột với
ACCESS SHARE	SELECT thường	Chỉ ACCESS EXCLUSIVE
ACCESS EXCLUSIVE	Phần lớn dạng ALTER TABLE, DROP TABLE	Mọi mode

Ba điều quyết định sự cố:

• Lock giữ đến hết transaction: report đọc xong mà chưa COMMIT vẫn giữ ACCESS SHARE.
• Theo tài liệu ALTER TABLE, lệnh lấy ACCESS EXCLUSIVE “unless explicitly noted”; ADD COLUMN không nằm trong các ngoại lệ.
• Request phải chờ đi vào hàng đợi lock (wait queue) của object đó.

Luật của hàng đợi

Chương Explicit Locking không mô tả thứ tự hàng đợi. Hai nguồn chính thống nói rõ:

1. Tài liệu pg_blocking_pids(): một process chặn process khác nếu nó giữ lock xung đột (hard block), hoặc đang chờ một lock xung đột và đứng trước trong hàng đợi (soft block).
2. src/backend/storage/lmgr/README (source PostgreSQL 17): request chỉ được cấp ngay khi không xung đột với lock đang giữ lẫn request đang chờ; khi nhả lock, waiter chỉ được đánh thức nếu không xung đột với waiter đứng trước.

README tóm lại: “conflicting requests are granted in order of arrival”. Nhờ vậy DDL không bị starvation bởi luồng SELECT liên tục chen lên trước.

Cách hoạt động

Ai giữ lock, ai chờ ai, và cái gì đổi khi migration bỏ cuộc?

Lock queue: ALTER chờ report, SELECT của API xếp hàng sau ALTER cho tới khi ALTER timeout

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

Report mở transaction, đọc orders rồi ngồi im: vẫn giữ ACCESS SHARE đến khi kết thúc transaction.

Trạng thái sau từng bước:

Bước	Giữ lock	Đang chờ	Thay đổi
1	Report: ACCESS SHARE	—	Report thành idle in transaction
2	Report	Migration (ACCESS EXCLUSIVE)	Hard block bởi Report
3	Report	Migration, API	API soft block bởi Migration
4	Report	API	Migration lỗi 55P03, rời hàng
5	Report, API	—	API được cấp ACCESS SHARE
6	API (đến khi xong)	—	Report commit

Không có lock_timeout (mặc định 0) thì bước 4 không xảy ra: mọi SELECT đến sau xếp hàng cho tới khi report kết thúc và migration chạy xong.

DDL nhanh không có nghĩa là thời gian chặn ngắn

Thời gian chặn = thời gian chờ lock + thời gian chạy. ADD COLUMN không default chạy vài mili giây, nhưng thời gian chờ phụ thuộc transaction lâu nhất đang mở trên bảng.

Chi tiết vận hành

• lock_timeout áp dụng riêng cho từng lần xin lock, cả tường minh lẫn ngầm; mặc định 0 (tắt); hết hạn thì huỷ câu lệnh với 55P03.
• Đặt lock_timeout theo session/transaction migration, không trong postgresql.conf (ảnh hưởng mọi session).
• Nếu statement_timeout khác 0 và ≤ lock_timeout, lock_timeout vô nghĩa vì statement_timeout luôn nổ trước.
• Gọi pg_blocking_pids() quá dày có chi phí: nó cần truy cập độc quyền ngắn vào lock manager.
• Mỗi lần retry, API vẫn có thể bị chặn tới lock_timeout. Chọn giá trị theo độ trễ API chấp nhận được.
• Chi tiết statement_timeout, idle_in_transaction_session_timeout, transaction_timeout, log_lock_waits: xem timeout và giám sát lock.

Quy trình migration an toàn

Đang tải diagram…

Tìm blocker trước khi chạy:

SELECT pid, application_name, state,
       now() - xact_start AS xact_age, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE pid IN (
  SELECT pid FROM pg_locks
  WHERE relation = 'orders'::regclass AND granted
)
ORDER BY xact_start;

Huỷ blocker là quyết định nghiệp vụ. Với session idle in transaction, pg_cancel_backend(pid) không nhả lock (đã thử: lock vẫn granted); pg_terminate_backend(pid) đóng connection và rollback transaction.

Ví dụ thực tế

Migration thêm cột vào orders lúc report đang mở transaction

Report mở transaction lúc 02:00, đọc orders rồi treo vì client chờ xuất file. Migration 02:05 chạy ADD COLUMN không lock_timeout: từ đó mọi API đọc orders chờ sau migration, pool 20 connection cạn.

Với lock_timeout = '3s', API chậm thêm tối đa khoảng 3 giây mỗi lần thử; hết lượt retry, pipeline dừng và báo người vận hành tìm report treo.

Demo trên PostgreSQL 17.11 (examples/docker-compose.yml), schema demo_lock_queue, bảng orders 1.000 row, ba session psql chạy nền:

1. report (t=0s): BEGIN; SELECT count(*) FROM demo_lock_queue.orders; rồi để idle in transaction 12 giây.
2. migration (t=1s): ALTER TABLE demo_lock_queue.orders ADD COLUMN note text;
3. api (t=2s): SELECT count(*) FROM demo_lock_queue.orders;

Session thứ tư quan sát lúc t=3s:

SELECT pid, application_name AS app, state,
       pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE application_name IN ('report', 'migration', 'api');

SELECT a.application_name AS app, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'demo_lock_queue.orders'::regclass;

  pid  |    app    |        state        | blocked_by
-------+-----------+---------------------+------------
 17673 | report    | idle in transaction | {}
 17680 | migration | active              | {17673}
 17708 | api       | active              | {17680}

    app    |        mode         | granted
-----------+---------------------+---------
 report    | AccessShareLock     | t
 migration | AccessExclusiveLock | f
 api       | AccessShareLock     | f

API bị chặn bởi migration (pid 17680), không phải report: lock nó xin tương thích với report nhưng granted = f. Đó là soft block.

Timing hai lần chạy (PID và mili giây đổi mỗi lần):

Lần chạy	Migration	API SELECT
lock_timeout = 0 (mặc định)	Thành công sau 10922 ms, khi report commit	Trả 1000 row sau 9914 ms
lock_timeout = '3s'	canceling statement due to lock timeout sau 3001 ms	Trả 1000 row sau 2002 ms, report chưa commit

Lần hai cho thấy hàng đợi thông ngay khi migration rời đi, dù report còn giữ lock. SQLSTATE (xem bằng VERBOSITY verbose) là 55P03.

Retry có backoff, chạy thử khi report giữ lock 5 giây:

for attempt in 1 2 3 4 5; do
  if psql -v ON_ERROR_STOP=1 \
       -c "SET lock_timeout = '1s'" \
       -c "ALTER TABLE demo_lock_queue.orders ADD COLUMN note text"; then
    echo "attempt $attempt: ok"; break
  fi
  sleep_s=$(( 2 ** (attempt - 1) ))
  echo "attempt $attempt: lock timeout, sleep ${sleep_s}s"; sleep "$sleep_s"
done

ERROR:  canceling statement due to lock timeout
attempt 1: lock timeout, sleep 1s
ERROR:  canceling statement due to lock timeout
attempt 2: lock timeout, sleep 2s
attempt 3: ok

Bản tối giản: chưa có jitter, chưa lọc riêng 55P03 (lỗi cú pháp cũng bị retry), chưa báo động khi hết lượt.

Trade-offs

Ưu điểm

• lock_timeout giới hạn thời gian một DDL có thể chặn cả bảng.

• Retry có backoff cho migration cơ hội chạy khi blocker ngắn đã xong.

• pg_blocking_pids() chỉ ra cả soft block.

< slot="cons">

• Mỗi lần thử vẫn chặn API tối đa bằng lock_timeout.

• Blocker kéo dài thì hết lượt retry, migration không chạy.

• Huỷ blocker có thể làm mất việc của report hoặc batch.

• Migration phải chạy lại được an toàn.

Nhược điểm

🎮 Thử tay

• Migration khoá cả bảng: lock queue làm cả API chờ sau một câu ALTER TABLE.

Checklist production

1. Mọi migration có DDL đặt lock_timeout ngắn trong cùng session/transaction.
2. Retry chỉ khi SQLSTATE 55P03, có giới hạn lượt, backoff tăng dần và jitter; hết lượt thì dừng và báo.
3. Trước khi chạy, tìm transaction đang giữ lock trên bảng đích.
4. Đặt statement_timeout lớn hơn lock_timeout để chặn DDL chạy lâu bất thường.
5. Không gộp DDL với backfill; DDL ghi lại bảng tách theo expand–contract.
6. Đặt idle_in_transaction_session_timeout cho role ứng dụng và report để transaction treo không giữ lock mãi.

Sai lầm thường gặp

Sai lầm hay gặp: đặt timeout cho câu DDL nhưng gộp cả backfill vào cùng transaction.

Một transaction migration giữ ACCESS EXCLUSIVE suốt quá trình backfill

✕ Hay gặp

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
UPDATE orders SET note = '';  -- giữ ACCESS EXCLUSIVE đến COMMIT
COMMIT;

✓ Nên viết

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
COMMIT;
-- Backfill riêng, theo lô nhỏ, mỗi lô một transaction

Vì sao

• Lock của ALTER giữ đến COMMIT, nên UPDATE cả bảng chặn mọi SELECT suốt thời gian chạy.
• lock_timeout chỉ giới hạn thời gian chờ, không giới hạn thời gian giữ.

Cách hiểu sai	Hệ quả	Cần làm
SELECT không xung đột với SELECT nên không bao giờ chờ nhau	API treo sau DDL đang chờ	Hiểu soft block; đặt lock_timeout cho DDL
Report đọc xong là nhả lock	idle in transaction giữ ACCESS SHARE vô hạn	Commit sớm, idle_in_transaction_session_timeout
Chỉ cần statement_timeout	≤ lock_timeout thì lock_timeout vô nghĩa	lock_timeout < statement_timeout

Tự kiểm tra

1. API xin ACCESS SHARE, tương thích với lock của report. Vì sao demo vẫn thấy granted = f và blocked_by trỏ tới migration?
2. Trong lần chạy lock_timeout = '3s', vì sao API nhận kết quả sau khoảng 2 giây dù report chưa commit?
3. Đặt statement_timeout = '2s' và lock_timeout = '5s' cho migration thì timeout nào xảy ra trước, và vì sao?

Tóm tắt

Tóm tắt

• Request lock xung đột với request đang chờ phía trước cũng phải chờ (soft block), nên một DDL đang chờ chặn cả SELECT đến sau.
• Migration đặt lock_timeout ngắn, retry chỉ với 55P03, có backoff và giới hạn lượt.
• Tìm và xử lý blocker (idle in transaction) trước; DDL ghi lại bảng thì tách theo expand–contract.

Tham khảo

• PostgreSQL 17 — Explicit Locking: Table-Level Locks: mode của từng lệnh, lock giữ đến hết transaction.
• PostgreSQL 17 — Session Information Functions: định nghĩa hard block và soft block của pg_blocking_pids().
• PostgreSQL 17 — lock_timeout: default, phạm vi, quan hệ với statement_timeout.
• PostgreSQL 17 — ALTER TABLE: lock mặc định, khi nào ADD COLUMN ghi lại bảng.
• PostgreSQL source — lmgr README: luật cấp lock theo thứ tự đến và cách đánh thức waiter.