Table-level locks trong PostgreSQL: DDL nào chặn cả SELECT trên bảng đang chạy Trung cấp

Vấn đề

Shop có bảng orders 100.000 row, nhận INSERT và UPDATE liên tục từ checkout. Team cần thêm cột payment_status, index trên status, và ràng buộc total_cents > 0.

Row-level lock chỉ khoá row bị ghi. DDL khoá cả bảng, theo một trong tám mode. Mỗi DDL lấy mode nào, chặn ai, và viết lại thế nào để khoá ít hơn?

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

Tình huống trên orders	Chọn	Điều kiện
Thêm cột	ADD COLUMN nullable hoặc DEFAULT hằng	Chỉ sửa metadata; đặt lock_timeout
Thêm index	CREATE INDEX CONCURRENTLY	Ngoài transaction block; kiểm tra index INVALID
Thêm CHECK, foreign key	ADD CONSTRAINT ... NOT VALID rồi VALIDATE CONSTRAINT	Hai lệnh, hai transaction
Đổi kiểu cột có rewrite	Cột mới + backfill theo lô (expand–contract)	Tránh chép bảng dưới lock mạnh

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

Table-level lock là lock trên một relation (bảng, index), hiện trong pg_locks với locktype = 'relation'. Mỗi câu lệnh tự lấy mode cần thiết và giữ đến hết transaction. Tên có chữ “ROW” vẫn là lock cấp bảng.

Tám mode và lệnh điển hình

Trong pg_locks.mode tên viết liền: AccessShareLock, RowExclusiveLock…

#	Mode	Lệnh điển hình
1	ACCESS SHARE	SELECT
2	ROW SHARE	SELECT ... FOR UPDATE/SHARE
3	ROW EXCLUSIVE	INSERT, UPDATE, DELETE, MERGE
4	SHARE UPDATE EXCLUSIVE	VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VALIDATE CONSTRAINT
5	SHARE	CREATE INDEX
6	SHARE ROW EXCLUSIVE	CREATE TRIGGER, ADD FOREIGN KEY
7	EXCLUSIVE	REFRESH MATERIALIZED VIEW CONCURRENTLY
8	ACCESS EXCLUSIVE	Phần lớn ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL, CLUSTER, LOCK TABLE

Ma trận xung đột

X = hai transaction khác nhau không cùng giữ được. Ô in đậm là nguồn sự cố migration thường gặp.

Giữ \ Muốn	ACC. SHARE	ROW SHARE	ROW EXCL.	SHARE UPD. EXCL.	SHARE	SHARE ROW EXCL.	EXCL.	ACC. EXCL.
ACC. SHARE								X
ROW SHARE							X	X
ROW EXCL.					X	X	X	X
SHARE UPD. EXCL.				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCL.			X	X	X	X	X	X
EXCL.		X	X	X	X	X	X	X
ACC. EXCL.	X	X	X	X	X	X	X	X

Đọc ra ba điều:

• Chỉ ACCESS EXCLUSIVE chặn SELECT thường: DDL lấy mode này dừng cả đọc lẫn ghi.
• SHARE (CREATE INDEX thường) cho đọc nhưng chặn mọi ghi.
• SHARE UPDATE EXCLUSIVE không chặn đọc ghi nhưng tự xung đột (VACUUM chờ CREATE INDEX CONCURRENTLY).

Cách hoạt động

Migration chạy CREATE INDEX rồi ALTER TABLE trên orders trong khi API đọc và ghi:

CREATE INDEX chỉ chặn INSERT; ALTER TABLE chặn cả SELECT lẫn INSERT đến khi commit

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

migration chạy CREATE INDEX trong transaction, giữ SHARE trên orders.

1. migration giữ SHARE: SELECT (ACCESS SHARE) chạy, INSERT (ROW EXCLUSIVE) chờ.
2. migration commit, nhả SHARE; INSERT chạy.
3. migration giữ ACCESS EXCLUSIVE: cả SELECT lẫn INSERT chờ.
4. migration commit; hai request chạy tiếp.

Thời gian chặn bằng thời gian giữ lock cộng thời gian chờ lấy lock. Nếu DDL phải chờ một transaction dài, query đến sau xếp hàng sau nó: xem lock queue.

Chi tiết vận hành

DDL phổ biến → lock mode

ALTER TABLE mặc định lấy ACCESS EXCLUSIVE trừ subform được ghi chú riêng. Đối chiếu tài liệu PostgreSQL 17 và demo:

Lệnh trên orders	Lock trên bảng	Phần tốn thời gian
ADD COLUMN nullable / DEFAULT không volatile	ACCESS EXCLUSIVE	Không; chỉ metadata
ADD COLUMN ... DEFAULT gen_random_uuid()	ACCESS EXCLUSIVE	Rewrite bảng và index
ALTER COLUMN TYPE bigint → numeric	ACCESS EXCLUSIVE	Rewrite (varchar(50) → varchar(100) thì không)
SET NOT NULL	ACCESS EXCLUSIVE	Scan, trừ khi có CHECK hợp lệ
ADD CONSTRAINT CHECK	ACCESS EXCLUSIVE	Scan; NOT VALID bỏ scan
ADD FOREIGN KEY	SHARE ROW EXCLUSIVE (cả hai bảng)	Scan; NOT VALID bỏ scan
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	Scan, không chặn đọc ghi
CREATE INDEX	SHARE	Build index, chặn ghi
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	Hai lần scan, chờ transaction cũ
REINDEX TABLE	SHARE, và ACCESS EXCLUSIVE trên từng index	Build lại index
DROP TABLE, TRUNCATE, DROP COLUMN, RENAME	ACCESS EXCLUSIVE	Ngắn
VACUUM FULL, CLUSTER	ACCESS EXCLUSIVE	Chép lại toàn bộ bảng

ALTER TABLE nhiều subcommand lấy lock mạnh nhất trong số đó.

Đổi DDL nặng thành DDL nhẹ

Đang tải diagram…

Nguyên tắc: ACCESS EXCLUSIVE chỉ nên giữ trong lúc sửa metadata. Scan, build index, chép dữ liệu chuyển sang lệnh SHARE UPDATE EXCLUSIVE hoặc sang code chạy theo lô.

Ví dụ thực tế

Ba thay đổi trên bảng orders đang nhận checkout

Ba thay đổi ở phần Vấn đề, viết lại thành bốn lệnh autocommit (xem phần sai lầm): chỉ ADD COLUMN và ADD CONSTRAINT ... NOT VALID lấy ACCESS EXCLUSIVE, cả hai không scan. Index và validate chạy dưới SHARE UPDATE EXCLUSIVE, checkout vẫn ghi được.

Demo: xem lock mode qua pg_locks

PostgreSQL 17.11, schema demo_table_level_locks. Mỗi DDL chạy trong transaction mở, đọc lock của chính session, rồi ROLLBACK.

CREATE VIEW my_locks AS
  SELECT c.relname, l.mode, l.granted
  FROM pg_locks l JOIN pg_class c ON c.oid = l.relation
  WHERE l.pid = pg_backend_pid() AND l.locktype = 'relation'
    AND c.relname IN ('orders', 'orders_pkey', 'customers');

BEGIN;
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;
SELECT * FROM my_locks;
ROLLBACK;

  relname  |         mode          | granted
-----------+-----------------------+---------
 orders    | AccessShareLock       | t
 orders    | ShareRowExclusiveLock | t
 customers | AccessShareLock       | t
 customers | ShareRowExclusiveLock | t

Tóm tắt các lần chạy khác (lock trên orders; rewritten so pg_relation_filenode trước và sau):

ADD COLUMN ... DEFAULT gen_random_uuid() AccessExclusiveLock, rewritten = t
ADD COLUMN ... NOT NULL DEFAULT 'web'   rewritten = f
ALTER COLUMN total_cents TYPE numeric   AccessExclusiveLock, rewritten = t
VALIDATE CONSTRAINT                     ShareUpdateExclusiveLock
CREATE INDEX                            ShareLock
REINDEX TABLE                           ShareLock; orders_pkey: AccessExclusiveLock

CREATE INDEX CONCURRENTLY quan sát từ session khác khi transaction ghi long_tx còn mở:

   app   |           mode           | granted | wait_type | wait_event
---------+--------------------------+---------+-----------+------------
 cic     | ShareUpdateExclusiveLock | t       | Lock      | virtualxid
 long_tx | RowExclusiveLock         | t       | Timeout   | PgSleep

Lệnh đã có lock nhưng chờ long_tx kết thúc; INSERT mới vẫn chạy xong. Kịch bản của hình:

    app    |        mode         | granted | blocked_by_pid
-----------+---------------------+---------+----------------
 api_read  | AccessShareLock     | f       | {16832}
 api_write | RowExclusiveLock    | f       | {16832}
 migration | AccessExclusiveLock | t       | {}

pid đổi mỗi lần chạy.

Trade-offs

Ưu điểm

• CONCURRENTLY và NOT VALID giữ bảng phục vụ đọc ghi trong lúc làm phần nặng.

• Mỗi bước nhỏ retry riêng được dưới lock_timeout.

< slot="cons">

• Migration nhiều bước, không còn nguyên tử.

• CONCURRENTLY chậm hơn và có thể để lại index INVALID.

Nhược điểm

🎮 Thử tay

• Migration khoá cả bảng: DDL nào lấy lock gì, lock_timeout và NOT VALID.

Checklist production

1. Ghi lock mode của từng lệnh khi review migration; lệnh ACCESS EXCLUSIVE phải không rewrite, không scan.
2. Đặt SET lock_timeout cho session migration, retry có lùi dần.
3. Không gộp DDL vào một transaction; tắt transaction tự động của tool cho CONCURRENTLY.
4. Sau CREATE INDEX CONCURRENTLY, kiểm tra pg_index.indisvalid.
5. Thêm CHECK, foreign key bằng NOT VALID; validate ở bước riêng.
6. Trước khi chạy, kiểm tra pg_stat_activity không có transaction dài trên bảng.

Sai lầm thường gặp

DDL ngắn vẫn giữ lock lâu nếu transaction dài:

Gộp mọi thay đổi vào một transaction

✕ Hay gặp

BEGIN;
ALTER TABLE orders ADD COLUMN payment_status text;
CREATE INDEX orders_status_idx ON orders (status);
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total_cents > 0);
COMMIT;

✓ Nên viết

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN payment_status text;
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total_cents > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT total_positive;

Vì sao

Bản đầu giữ ACCESS EXCLUSIVE từ lệnh đầu đến COMMIT. Bản sau chạy autocommit: mỗi lệnh nhả lock khi xong.

Hiểu sai khác:

REINDEX TABLE vẫn chặn SELECT

REINDEX chỉ lấy SHARE trên bảng, nhưng lấy ACCESS EXCLUSIVE trên index. Planner lấy ACCESS SHARE trên mọi index của bảng khi lập plan, nên hầu như mọi query đều chờ. Demo: SELECT ép seq scan vẫn chờ ở orders_pkey. Bảng đang chạy thì dùng REINDEX ... CONCURRENTLY.

Cách hiểu sai	Hệ quả	Cần làm
ADD COLUMN nhanh nên không cần lock_timeout	DDL chờ transaction dài, query sau xếp hàng	Luôn đặt lock_timeout
NOT VALID là bỏ kiểm tra	Dữ liệu cũ vi phạm không bị phát hiện	Chạy VALIDATE

Tự kiểm tra

1. Vì sao CREATE INDEX thường để SELECT chạy nhưng chặn INSERT, còn ADD COLUMN chặn cả hai?
2. DEFAULT 'web' và DEFAULT gen_random_uuid() cùng lấy ACCESS EXCLUSIVE. Vì sao thời gian giữ lock khác nhau?
3. CREATE INDEX CONCURRENTLY đã granted = t nhưng chưa xong. Nó chờ gì, và INSERT mới có bị chặn không?

Tóm tắt

Tóm tắt

• Tám table lock mode, giữ đến hết transaction; chỉ ACCESS EXCLUSIVE chặn SELECT thường.
• Phần lớn ALTER TABLE, cùng DROP, TRUNCATE, VACUUM FULL, lấy ACCESS EXCLUSIVE; CREATE INDEX lấy SHARE, chặn ghi.
• Rủi ro nằm ở thời gian giữ: rewrite và scan dưới ACCESS EXCLUSIVE.
• Đổi DDL nặng thành nhẹ: CONCURRENTLY, NOT VALID + VALIDATE, cột mới + backfill.

Tham khảo

• PostgreSQL 17 — Table-Level Locks
• PostgreSQL 17 — ALTER TABLE
• PostgreSQL 17 — Building Indexes Concurrently
• PostgreSQL 17 — Rebuilding Indexes Concurrently