Table Lock — DDL Lock

DDL lock: vì sao một ALTER TABLE 50ms vẫn đủ làm sập cả API trong giờ cao điểm

DDL trong Postgres không phải "vài lệnh schema chạy nhanh". Hầu hết các form của ALTER TABLE, toàn bộ DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, và cả REFRESH MATERIALIZED VIEW (không có CONCURRENTLY) đều yêu cầu ACCESS EXCLUSIVE — lock mode mạnh nhất, xung đột với mọi mode khác kể cả ACCESS SHARE mà một SELECT thuần đọc cũng cần. Một DDL chạy 50ms vẫn có thể chôn cả service vài phút vì hai thứ Postgres làm theo design: lock được giữ tới hết transaction (không nhả sớm), và lock queue là FIFO — bên đến sau dù mode tương thích vẫn phải đứng sau bên đang đợi. "Migration chạy lúc giờ cao điểm, toàn bộ API treo 5 phút" gần như luôn là class incident này.

Cơ chế hoạt động

Mỗi lệnh DDL acquire một relation-level lock trên các object nó chạm, theo bảng mode cố định trong Postgres docs mục Explicit Locking. Có ba nhóm cần thuộc:

• ACCESS EXCLUSIVE — xung đột với mọi mode. Cấp bởi: phần lớn ALTER TABLE (kể cả những form không rewrite data), DROP TABLE, TRUNCATE, REINDEX (non-concurrently), CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW (non-concurrently), LOCK TABLE không kèm mode. Đây là lock "đông cứng" object.
• SHARE — xung đột với mọi mode ghi (ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE). Cấp bởi: CREATE INDEX (non-concurrently). Cho đọc đi qua, nhưng chặn mọi INSERT/UPDATE/DELETE — trên bảng OLTP nóng đó là downtime ghi.
• SHARE UPDATE EXCLUSIVE — xung đột với chính nó và các mode mạnh hơn, nhưng không xung đột với ROW EXCLUSIVE. Cấp bởi: CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VACUUM (không FULL), ANALYZE, ALTER TABLE ... VALIDATE CONSTRAINT, ALTER TABLE ... SET STATISTICS, ALTER INDEX ... RENAME. Đây là mode "online maintenance": cho cả đọc lẫn ghi đi qua, chỉ tự khoá lẫn nhau.

Quan trọng: lock giữ tới hết transaction, không có cách nhả sớm. BEGIN; ALTER TABLE ...; <30 phút làm việc khác>; COMMIT; giữ ACCESS EXCLUSIVE 30 phút dù lệnh DDL chỉ chạy mili-giây. Đây là sai lầm số một khi gói nhiều migration vào một transaction.

-- ALTER thực sự chạy 50ms, nhưng lock được giữ tới COMMIT
BEGIN;
ALTER TABLE orders ADD COLUMN note text;          -- ACCESS EXCLUSIVE, xong nhanh
-- ... 30 giây code application chạy giữa hai lệnh ...
ALTER TABLE orders ADD COLUMN updated_by bigint;  -- ACCESS EXCLUSIVE thứ hai
COMMIT;
-- Trong 30 giây đó, mọi SELECT trên orders đều đứng.

Một số form ALTER TABLE mặc dù vẫn yêu cầu ACCESS EXCLUSIVE nhưng không rewrite data nên giữ lock rất ngắn — đó là path "an toàn" của migration online: ADD COLUMN không kèm DEFAULT (hoặc kèm constant DEFAULT từ Postgres 11 trở đi, vì PG11 thêm pg_attribute.attmissingval để lưu default vào catalog thay vì rewrite heap), DROP COLUMN (chỉ đánh dấu cột invisible, vacuum dọn sau), ALTER COLUMN SET DEFAULT, ALTER COLUMN DROP NOT NULL. Các form rewrite gồm: ADD COLUMN với volatile default (now(), gen_random_uuid()), ALTER COLUMN TYPE đổi binary representation (int → bigint, text → uuid), SET LOGGED/UNLOGGED. Một bảng vài trăm GB rewrite nghĩa là ACCESS EXCLUSIVE giữ hàng giờ.

Vấn đề gặp trong production

Failure mode 1: rewrite vô tình vì default volatile. ADD COLUMN ... DEFAULT now() hay DEFAULT gen_random_uuid() không dùng được fast-path attmissingval (giá trị khác nhau từng row), nên Postgres rewrite toàn bộ heap. Lock ACCESS EXCLUSIVE giữ suốt thời gian rewrite, có thể là hàng giờ trên bảng lớn:

-- Sai: rewrite cả bảng dưới ACCESS EXCLUSIVE, service đứng cho tới khi xong
ALTER TABLE orders ADD COLUMN created_uuid uuid DEFAULT gen_random_uuid() NOT NULL;

-- Đúng: tách thành ba bước, mỗi bước lock ngắn hoặc không lock ghi
ALTER TABLE orders ADD COLUMN created_uuid uuid;                            -- metadata-only
ALTER TABLE orders ALTER COLUMN created_uuid SET DEFAULT gen_random_uuid(); -- metadata-only
-- backfill theo batch nhỏ ngoài transaction lớn:
UPDATE orders SET created_uuid = gen_random_uuid()
WHERE created_uuid IS NULL AND id BETWEEN $1 AND $2;
-- sau khi backfill xong, mới SET NOT NULL qua đường CHECK constraint (xem failure mode 3)

Failure mode 2: CREATE INDEX (không CONCURRENTLY) trên bảng nóng. CREATE INDEX lấy SHARE — đọc qua được, nhưng mọi INSERT/UPDATE/DELETE đứng vì cần ROW EXCLUSIVE xung đột với SHARE. Trên bảng OLTP write-heavy, đây là downtime ghi vài phút tới vài giờ tuỳ kích thước bảng. Phiên bản đúng cần CONCURRENTLY:

-- Sai
CREATE INDEX idx_orders_customer ON orders(customer);

-- Đúng
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer);

CREATE INDEX CONCURRENTLY đổi lại bằng việc: không chạy được trong transaction block (migration framework cần cờ riêng — Rails disable_ddl_transaction!, Django atomic = False, Sequelize raw query ngoài transaction), chạy lâu hơn vì hai pass, và nếu fail giữa chừng để lại index ở trạng thái INVALID — phải DROP INDEX CONCURRENTLY rồi build lại. Migration runner nên check pg_index.indisvalid = false sau mỗi lần build.

Failure mode 3: ALTER COLUMN SET NOT NULL full-table scan dưới ACCESS EXCLUSIVE. Lệnh này lấy ACCESS EXCLUSIVE và scan toàn bộ bảng để verify không có NULL — trên bảng lớn là vài phút lock cả đọc lẫn ghi. Postgres 12 trở đi có path tránh scan: tạo trước một CHECK (col IS NOT NULL) NOT VALID, VALIDATE CONSTRAINT (chỉ SHARE UPDATE EXCLUSIVE, không block ghi), rồi SET NOT NULL sẽ thấy constraint đã validated và bỏ qua scan:

-- Sai
ALTER TABLE orders ALTER COLUMN customer SET NOT NULL;
-- ACCESS EXCLUSIVE + scan toàn bảng

-- Đúng (Postgres 12+)
ALTER TABLE orders ADD CONSTRAINT orders_customer_not_null
  CHECK (customer IS NOT NULL) NOT VALID;                 -- ACCESS EXCLUSIVE nhưng không scan
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_not_null;
                                                          -- SHARE UPDATE EXCLUSIVE, không block ghi
ALTER TABLE orders ALTER COLUMN customer SET NOT NULL;    -- nhanh, không scan lại
ALTER TABLE orders DROP CONSTRAINT orders_customer_not_null;

ADD FOREIGN KEY và ADD CHECK cũng cùng pattern: dùng NOT VALID trước, VALIDATE sau, để tách bước "ACCESS EXCLUSIVE ngắn" khỏi bước "scan dài".

Failure mode 4: FIFO queue biến ALTER đợi 5 giây thành downtime 5 phút. Lock queue là FIFO. Khi ALTER đứng đợi ACCESS SHARE của một SELECT dài chưa nhả, mọi SELECT đến sau ALTER — dù chỉ cần ACCESS SHARE vốn tương thích với SELECT đang chạy — đều bị xếp sau ALTER trong hàng đợi. Một transaction idle in transaction từ một worker bị crash giữa chừng đủ làm cả service đơ. Migration script production phải luôn SET lock_timeout cấp session để ALTER tự huỷ nếu không lấy được lock nhanh, kèm vòng retry với backoff:

SET lock_timeout = '2s';
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN note text;
-- Nếu bị block quá 2s: ERROR: canceling statement due to lock timeout
-- Migration runner bắt lỗi, retry với jitter

Failure mode 5: DROP INDEX không CONCURRENTLY trên bảng được query liên tục. DROP INDEX lấy ACCESS EXCLUSIVE trên bảng — chặn cả đọc. Trên bảng nóng, dùng DROP INDEX CONCURRENTLY. Tương tự, REINDEX thường chỉ nên chạy ở dạng REINDEX INDEX CONCURRENTLY hoặc REINDEX TABLE CONCURRENTLY (Postgres 12+).

Cách debug và monitor

Triệu chứng cứng: latency tăng vọt đồng thời trên tất cả endpoint chạm cùng một bảng, không chỉ endpoint vừa deploy. Health check timeout dù CPU/IO database không cao. pg_stat_activity đầy session ở state = 'active', wait_event_type = 'Lock', wait_event = 'relation'. Một session ở trạng thái idle in transaction với xact_start rất cũ thường là thủ phạm gốc.

Query khoanh vùng "ai đang chặn ai" khi nghi DDL block:

SELECT
  a.pid,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  pg_blocking_pids(a.pid) AS blocked_by,
  left(a.query, 200) AS query
FROM pg_stat_activity a
WHERE a.backend_type = 'client backend'
  AND (a.wait_event_type = 'Lock' OR pg_blocking_pids(a.pid) <> '{}')
ORDER BY xact_age DESC NULLS LAST;

Lock cụ thể trên một relation và mode đang xin:

SELECT pid, mode, granted, locktype, relation::regclass AS rel
FROM pg_locks
WHERE relation = 'orders'::regclass
ORDER BY granted DESC, pid;

Khi đã xác định pid gốc và biết là transaction zombie (idle in transaction, application đã chết), gỡ bằng SELECT pg_terminate_backend(<pid>);. Đừng terminate cái đang giữ ACCESS EXCLUSIVE đúng nghĩa (DDL đang rewrite); hãy pg_cancel_backend để rollback sạch hơn.

GUC cần bật ở cluster production:

• log_lock_waits = on cùng deadlock_timeout (mặc định 1s) — server log mọi lock chờ vượt timeout, là dấu vết postmortem.
• lock_timeout ở session level cho mọi migration script.
• idle_in_transaction_session_timeout — cắt nguồn gốc nhiều DDL block (transaction zombie).
• log_line_prefix chứa %a để truy application_name từ log.

Tool tĩnh chặn migration nguy hiểm trước khi vào production: squawk (linter cho file SQL migration, gắn vào CI), strong_migrations (gem Ruby/Rails từ Andrew Kane), pgroll (Xata, dạng multi-version schema). Một rule tối thiểu cho CI: chặn mọi ALTER TABLE ... ALTER COLUMN ... TYPE, ADD COLUMN ... DEFAULT <volatile>, CREATE INDEX thiếu CONCURRENTLY, ADD FOREIGN KEY thiếu NOT VALID.

Metric tối thiểu export ra monitoring (qua postgres_exporter): số session đang wait_event_type = 'Lock' (gauge), tuổi block dài nhất, counter từ pg_stat_database.deadlocks, và xact_start cũ nhất trong pg_stat_activity (alert khi vượt vài phút).

Tradeoff

DDL lock mạnh đến vậy vì Postgres cần đảm bảo schema đổi nguyên tử dưới một MVCC engine chạy nhiều process — không thể có chuyện một backend thấy cột mới, backend kia thấy cột cũ giữa chừng. Cái giá: bất kỳ thay đổi schema nào cũng tiềm năng đóng băng object trong khoảng thời gian không xác định. Postgres bù lại bằng ba thứ: fast-path catalog từ PG11 cho ADD COLUMN constant default (không rewrite), NOT VALID + VALIDATE tách scan ra khỏi lock window, và CONCURRENTLY cho CREATE/DROP/REINDEX INDEX đổi từ SHARE/ACCESS EXCLUSIVE sang SHARE UPDATE EXCLUSIVE. Đổi lại, mỗi cái có ràng buộc riêng: CONCURRENTLY không chạy trong transaction; NOT VALID để lại một khoảng thời gian constraint chưa enforced; tách migration thành nhiều bước nghĩa là phải có "expand → migrate data → contract" pattern, code phải tương thích với cả schema cũ và mới giữa hai deploy. Quy tắc thực tế: mọi migration chạm bảng có traffic phải SET lock_timeout cấp session với giá trị thấp (vài giây), tách thành các bước không rewrite, dùng CONCURRENTLY cho index, NOT VALID cho constraint; gói nhiều DDL trong một transaction là anti-pattern; lint migration ở CI; deploy schema và code thành nhiều bước expand/contract thay vì big-bang.

Câu hỏi phỏng vấn

ALTER TABLE gây lock gì? Trên một bảng đang có traffic, làm sao thêm một cột NOT NULL có default mà không downtime?

ALTER TABLE mặc định lấy ACCESS EXCLUSIVE trên bảng — mode mạnh nhất, xung đột với mọi mode khác kể cả ACCESS SHARE của SELECT thuần đọc. Lock giữ tới hết transaction, không thể nhả sớm. Vì lock queue Postgres là FIFO, một ALTER đứng đợi sau một SELECT chưa commit sẽ kéo theo mọi query mới đến sau cũng đợi — biến block vài giây thành downtime nhiều phút trên cả service. Một số form ALTER vẫn lấy ACCESS EXCLUSIVE nhưng không rewrite data (giữ lock cực ngắn): ADD COLUMN không default hoặc với constant default từ PG11+ (nhờ attmissingval), DROP COLUMN, SET/DROP DEFAULT. Các form rewrite (ADD COLUMN với volatile default, ALTER COLUMN TYPE đổi binary repr, SET NOT NULL scan bảng) giữ ACCESS EXCLUSIVE suốt thời gian rewrite/scan — có thể hàng giờ. Để thêm cột NOT NULL có default mà không downtime: (1) ADD COLUMN col TYPE không default — metadata-only, lock ngắn; (2) ALTER COLUMN col SET DEFAULT <value> — metadata-only, không ảnh hưởng row cũ; (3) backfill theo batch nhỏ qua nhiều transaction, mỗi batch update WHERE id BETWEEN $1 AND $2 AND col IS NULL; (4) ADD CONSTRAINT ... CHECK (col IS NOT NULL) NOT VALID — lock ngắn vì không scan; (5) VALIDATE CONSTRAINT — chạy với SHARE UPDATE EXCLUSIVE, không chặn ghi; (6) ALTER COLUMN col SET NOT NULL — Postgres 12+ thấy constraint đã validated nên bỏ qua scan, lock vài ms; (7) DROP CONSTRAINT constraint phụ. Mọi migration đều SET lock_timeout = '2s' cấp session với retry-backoff để ALTER tự huỷ thay vì chôn FIFO queue. Điểm ăn điểm là phân biệt rewrite vs metadata-only, gọi tên FIFO queue, đưa được sequence cụ thể với NOT VALID/VALIDATE, và nhắc lock_timeout + tách deploy expand/contract.

Hands-on

Mục tiêu: quan sát ACCESS EXCLUSIVE của ALTER TABLE chặn cả SELECT, so sánh ALTER metadata-only vs ALTER rewrite, rồi tái hiện đầy đủ pattern NOT VALID + VALIDATE cho SET NOT NULL.

Dựng container:

docker run -d --name pg-ddl \
  -e POSTGRES_PASSWORD=pw -p 5438:5432 \
  postgres:16 \
  -c log_lock_waits=on \
  -c deadlock_timeout=1s \
  -c log_min_duration_statement=0 \
  -c log_line_prefix='%m [%p] %a '

sleep 5
docker exec -i pg-ddl psql -U postgres <<'SQL'
CREATE TABLE orders (
  id         bigserial PRIMARY KEY,
  customer   int      NOT NULL,
  amount     numeric  NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders(customer, amount)
SELECT g % 10000, (random()*1000)::numeric(10,2)
FROM generate_series(1, 500000) g;
SQL

Bước 1 — Bật ba terminal, session A giữ ACCESS SHARE:

docker exec -it pg-ddl psql "postgresql://postgres:pw@localhost/postgres?application_name=reader"

BEGIN;
SELECT count(*) FROM orders;        -- giữ ACCESS SHARE, chưa COMMIT

Bước 2 — Session B chạy ALTER rewrite (volatile default) — đợi A rồi rewrite cả bảng:

docker exec -it pg-ddl psql "postgresql://postgres:pw@localhost/postgres?application_name=migrator"

SET lock_timeout = '0';   -- cố tình tắt để thấy hậu quả
ALTER TABLE orders ADD COLUMN tracking uuid DEFAULT gen_random_uuid() NOT NULL;
-- Treo. Sau khi A COMMIT, ALTER bắt đầu rewrite toàn bảng.

Bước 3 — Session C, một SELECT mới đến sau ALTER, cũng treo vì FIFO:

docker exec -it pg-ddl psql "postgresql://postgres:pw@localhost/postgres?application_name=user_query"

SELECT count(*) FROM orders WHERE customer = 42;
-- Treo. Dù ACCESS SHARE tương thích A, vẫn xếp sau B trong FIFO.

Bước 4 — Session D quan sát:

docker exec -it pg-ddl psql "postgresql://postgres:pw@localhost/postgres?application_name=watcher"

SELECT a.pid, a.application_name, a.state,
       a.wait_event_type, a.wait_event,
       pg_blocking_pids(a.pid) AS blocked_by,
       left(a.query, 80) AS q
FROM pg_stat_activity a
WHERE a.application_name IN ('reader','migrator','user_query')
ORDER BY a.pid;

SELECT pid, mode, granted FROM pg_locks
WHERE relation = 'orders'::regclass ORDER BY granted DESC, pid;

Quan sát: reader giữ ACCESS SHARE granted=true; migrator xin ACCESS EXCLUSIVE granted=false, blocked_by là pid của reader; user_query xin ACCESS SHARE granted=false, blocked_by là pid của migrator — đó là FIFO. Commit reader, B rewrite vài giây tuỳ máy, C chạy ngay sau B.

Bước 5 — So sánh với version metadata-only. Dọn cột vừa thêm, thử path đúng:

ALTER TABLE orders DROP COLUMN tracking;

-- Path đúng:
ALTER TABLE orders ADD COLUMN tracking uuid;                      -- nhanh, metadata-only
ALTER TABLE orders ALTER COLUMN tracking SET DEFAULT gen_random_uuid();
-- backfill theo batch
DO $$
DECLARE v_min bigint; v_max bigint;
BEGIN
  FOR v_min IN SELECT generate_series(1, 500000, 10000) LOOP
    v_max := v_min + 9999;
    UPDATE orders SET tracking = gen_random_uuid()
      WHERE id BETWEEN v_min AND v_max AND tracking IS NULL;
    COMMIT;
  END LOOP;
END $$;

Bước 6 — Pattern NOT VALID + VALIDATE cho SET NOT NULL:

SET lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_tracking_nn CHECK (tracking IS NOT NULL) NOT VALID;
-- ACCESS EXCLUSIVE nhưng không scan, lock mili-giây.

ALTER TABLE orders VALIDATE CONSTRAINT orders_tracking_nn;
-- SHARE UPDATE EXCLUSIVE, không block INSERT/UPDATE/DELETE.

ALTER TABLE orders ALTER COLUMN tracking SET NOT NULL;
-- Postgres 12+ bỏ qua scan vì đã có CHECK validated, nhanh.

ALTER TABLE orders DROP CONSTRAINT orders_tracking_nn;

Bước 7 — Demo lock_timeout cứu service. Reset, mở reader giữ ACCESS SHARE như bước 1, rồi:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN flag boolean;
-- ERROR:  canceling statement due to lock timeout

ALTER tự huỷ sau 2 giây; FIFO queue không bị chôn. Đây là dòng phải có trong mọi migration script production.

Bước 8 — Đọc dấu vết trong log:

docker logs pg-ddl 2>&1 | grep -i 'still waiting\|lock timeout' | tail -20

Dọn dẹp:

docker rm -f pg-ddl