Migration database: mỗi DDL giữ khóa nào và chặn ai

Câu hỏi bài này trả lời: một câu ALTER TABLE hoặc CREATE INDEX trong migration giữ khóa nào, chặn truy vấn nào, và vì sao chỉ cần nó đang chờ khóa cũng đủ làm cả bảng đứng?

Cần biết trước: SQL cơ bản, lab database và khóa ngoại (bài này dùng lại cách quan sát khóa bằng nhiều phiên). Bài chạy PostgreSQL 18.6 trên macOS arm64 với dữ liệu giả; MySQL không có phép đo nào. Docker/Linux của fixture chưa kiểm.

Mức khóa bảng, đọc theo hai câu hỏi

Mỗi câu lệnh lấy một khóa mức bảng, và với migration chỉ cần trả lời hai câu: nó chặn ai, và nó giữ bao lâu. Bảng dưới là phần của tài liệu PostgreSQL 18 mà migration hay chạm tới (tài liệu có tám mức, ở đây chọn sáu):

Ba điều đáng nhớ từ tài liệu. Chỉ khóa ACCESS EXCLUSIVE mới chặn được SELECT thường. Khóa được giữ cho tới hết giao dịch chứa câu lệnh, nên một DDL nằm trong giao dịch dài giữ khóa lâu hơn chính nó. Và ALTER TABLE lấy ACCESS EXCLUSIVE trừ khi tài liệu của từng dạng ghi khác đi.

Bảng chỉ nói mức khóa, không nói thời gian giữ và không nói chuyện chờ đợi. Hai chủ đề đó là phần còn lại của bài.

Tạo thư mục sạch, chép bốn file của cách A từ bài lab (lab-common.sh, lab-local.sh, seed-pg.sql, seed-mysql.sql) rồi dựng lab. File lab-wait.sh dưới đây dùng cho các bước hai phiên: nó hỏi pg_stat_activity cho tới khi phiên nền tới đúng điểm cần, thay vì đoán bằng sleep:

wait_for() {
  i=0
  until [ "$(lab_psql -At -c "$1")" = "$2" ]; do
    i=$((i + 1))
    [ "$i" -lt 150 ] || { echo "hết thời gian chờ: $1" >&2; return 1; }
    sleep 0.1
  done
}

. ./lab-local.sh
lab_up
lab_seed
lab_whoami

Đo mức khóa của từng DDL

Mỗi DDL chạy trong một giao dịch rồi rollback. Lab in các mức khóa mà chính phiên đó đang giữ trên bảng, và với DDL đổi cấu trúc bảng còn in cột rewritten: relfilenode của bảng có đổi không, tức bảng có bị ghi lại thành file mới không.

\set ON_ERROR_STOP on
\echo == ADD COLUMN không default
BEGIN;
SELECT pg_relation_filenode('wiki_lab.orders') AS before \gset
ALTER TABLE wiki_lab.orders ADD COLUMN note text;
SELECT string_agg(l.mode, ',' ORDER BY l.mode), pg_relation_filenode('wiki_lab.orders') <> :before AS rewritten FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation = 'wiki_lab.orders'::regclass;
ROLLBACK;
\echo == ADD COLUMN default hằng
BEGIN;
SELECT pg_relation_filenode('wiki_lab.orders') AS before \gset
ALTER TABLE wiki_lab.orders ADD COLUMN flag boolean NOT NULL DEFAULT false;
SELECT string_agg(l.mode, ',' ORDER BY l.mode), pg_relation_filenode('wiki_lab.orders') <> :before AS rewritten FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation = 'wiki_lab.orders'::regclass;
ROLLBACK;
\echo == ADD COLUMN default volatile
BEGIN;
SELECT pg_relation_filenode('wiki_lab.orders') AS before \gset
ALTER TABLE wiki_lab.orders ADD COLUMN stamp timestamptz DEFAULT clock_timestamp();
SELECT string_agg(l.mode, ',' ORDER BY l.mode), pg_relation_filenode('wiki_lab.orders') <> :before AS rewritten FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation = 'wiki_lab.orders'::regclass;
ROLLBACK;
\echo == ALTER COLUMN TYPE bigint
BEGIN;
SELECT pg_relation_filenode('wiki_lab.orders') AS before \gset
ALTER TABLE wiki_lab.orders ALTER COLUMN total_cents TYPE bigint;
SELECT string_agg(l.mode, ',' ORDER BY l.mode), pg_relation_filenode('wiki_lab.orders') <> :before AS rewritten FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation = 'wiki_lab.orders'::regclass;
ROLLBACK;
\echo == CREATE INDEX
BEGIN;
CREATE INDEX ix_status ON wiki_lab.orders(status);
SELECT string_agg(l.mode, ',' ORDER BY l.mode) FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation = 'wiki_lab.orders'::regclass;
ROLLBACK;
\echo == ANALYZE
BEGIN;
ANALYZE wiki_lab.orders;
SELECT string_agg(l.mode, ',' ORDER BY l.mode) FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation = 'wiki_lab.orders'::regclass;
ROLLBACK;
\echo == ADD CHECK có quét
BEGIN;
ALTER TABLE wiki_lab.orders ADD CONSTRAINT chk_total CHECK (total_cents > 0);
SELECT string_agg(l.mode, ',' ORDER BY l.mode) FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation = 'wiki_lab.orders'::regclass;
ROLLBACK;
\echo == ADD CHECK NOT VALID
BEGIN;
ALTER TABLE wiki_lab.orders ADD CONSTRAINT chk_total CHECK (total_cents > 0) NOT VALID;
SELECT string_agg(l.mode, ',' ORDER BY l.mode) FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation = 'wiki_lab.orders'::regclass;
ROLLBACK;
\echo == ADD FOREIGN KEY
BEGIN;
ALTER TABLE wiki_lab.order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id);
SELECT l.relation::regclass, string_agg(l.mode, ',' ORDER BY l.mode) FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation IN ('wiki_lab.orders'::regclass, 'wiki_lab.order_items'::regclass) GROUP BY 1 ORDER BY 1;
ROLLBACK;
\echo == ADD FOREIGN KEY NOT VALID
BEGIN;
ALTER TABLE wiki_lab.order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id) NOT VALID;
SELECT l.relation::regclass, string_agg(l.mode, ',' ORDER BY l.mode) FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation IN ('wiki_lab.orders'::regclass, 'wiki_lab.order_items'::regclass) GROUP BY 1 ORDER BY 1;
ROLLBACK;

VALIDATE CONSTRAINT chỉ có nghĩa khi ràng buộc đã tồn tại ở dạng NOT VALID, nên nó có file riêng và chạy sau khi lab thêm hai ràng buộc đó:

\set ON_ERROR_STOP on
\echo == VALIDATE FOREIGN KEY
BEGIN;
ALTER TABLE wiki_lab.order_items VALIDATE CONSTRAINT fk_items_order;
SELECT l.relation::regclass, string_agg(l.mode, ',' ORDER BY l.mode) FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation IN ('wiki_lab.orders'::regclass, 'wiki_lab.order_items'::regclass) GROUP BY 1 ORDER BY 1;
ROLLBACK;
\echo == VALIDATE CHECK
BEGIN;
ALTER TABLE wiki_lab.orders VALIDATE CONSTRAINT chk_total;
SELECT string_agg(l.mode, ',' ORDER BY l.mode) FROM pg_locks l WHERE l.pid = pg_backend_pid() AND l.relation = 'wiki_lab.orders'::regclass;
ROLLBACK;

Đầu ra mong đợi viết thành file để lab so đúng từng dòng bằng diff: một dòng thừa hay thiếu đều làm lab hỏng. Đây là chủ ý, vì khối hiển thị output chỉ kiểm tập con dòng.

== ADD COLUMN không default
AccessExclusiveLock | f
== ADD COLUMN default hằng
AccessExclusiveLock | f
== ADD COLUMN default volatile
AccessExclusiveLock,ShareLock | t
== ALTER COLUMN TYPE bigint
AccessExclusiveLock,ShareLock | t
== CREATE INDEX
ShareLock
== ANALYZE
ShareUpdateExclusiveLock
== ADD CHECK có quét
AccessExclusiveLock
== ADD CHECK NOT VALID
AccessExclusiveLock
== ADD FOREIGN KEY
wiki_lab.orders | AccessShareLock,RowShareLock,ShareRowExclusiveLock
wiki_lab.order_items | AccessShareLock,ShareRowExclusiveLock
== ADD FOREIGN KEY NOT VALID
wiki_lab.orders | AccessShareLock,ShareRowExclusiveLock
wiki_lab.order_items | AccessShareLock,ShareRowExclusiveLock
== VALIDATE FOREIGN KEY
wiki_lab.orders | AccessShareLock,RowShareLock
wiki_lab.order_items | AccessShareLock,ShareUpdateExclusiveLock
== VALIDATE CHECK
ShareUpdateExclusiveLock

. ./lab-local.sh
{
  lab_psql -At -F ' | ' -f modes.sql
  lab_psql -c 'ALTER TABLE wiki_lab.order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id) NOT VALID'
  lab_psql -c 'ALTER TABLE wiki_lab.orders ADD CONSTRAINT chk_total CHECK (total_cents > 0) NOT VALID'
  lab_psql -At -F ' | ' -f validate.sql
} > modes.actual
diff modes.expected modes.actual
cat modes.actual
echo 'mức khóa khớp từng dòng'

Đọc bảng này theo ba nhóm:

• Cùng khóa mạnh nhất, khác thời gian giữ. ADD COLUMN không default và với default hằng đều lấy AccessExclusiveLock, và rewritten là f: tài liệu nói default không volatile được tính một lần lúc chạy lệnh rồi lưu trong metadata của bảng, và lab xác nhận file của bảng không đổi. Với default volatile (như clock_timestamp()) tài liệu nói cả bảng và index bị ghi lại, còn đổi kiểu cột thì thường phải ghi lại; lab đo cả hai (đổi integer sang bigint) có rewritten là t, trong lúc ACCESS EXCLUSIVE vẫn được giữ.
• Cùng AccessExclusiveLock, một bản quét bảng một bản không. ADD CHECK có quét và ADD CHECK ... NOT VALID đều lấy ACCESS EXCLUSIVE; khác biệt theo tài liệu là NOT VALID bỏ qua bước quét ban đầu, nên thời gian giữ khóa mạnh không còn phụ thuộc kích thước bảng (suy ra từ tài liệu, bài không đo riêng cặp này). Bước VALIDATE sau đó chỉ lấy ShareUpdateExclusiveLock, mức không chặn đọc và ghi.
• ADD FOREIGN KEY là ngoại lệ so với các ràng buộc khác: tài liệu ghi nó chỉ cần ShareRowExclusiveLock thay vì ACCESS EXCLUSIVE, nhưng lab cho thấy mức đó nằm trên cả hai bảng, và mức này chặn ghi. Bản NOT VALID cũng lấy mức đó nhưng bỏ qua bước quét nên chỉ cần giữ ngắn; VALIDATE hạ xuống ShareUpdateExclusiveLock ở bảng con và chỉ còn RowShareLock ở bảng cha.

Các dòng AccessShareLock, RowShareLock và ShareLock đi kèm trong kết quả là những khóa khác mà chính phiên đó đang giữ trên cùng bảng; bài chỉ đọc mức mạnh nhất vì mức đó quyết định ai bị chặn. Cũng nhớ rằng bảng chỉ nói về mức khóa, không nói thời gian giữ: cùng AccessExclusiveLock, một lần ghi lại bảng tốn thời gian theo kích thước bảng còn một lần đổi metadata thì không. Lab dưới đây đo chênh lệch đó trên fixture nhỏ, nên chỉ nên đọc tỉ lệ:

\set ON_ERROR_STOP on
BEGIN;
\echo == default hằng, chỉ đổi metadata
\timing on
ALTER TABLE wiki_lab.orders ADD COLUMN flag boolean NOT NULL DEFAULT false;
\timing off
ROLLBACK;
BEGIN;
\echo == default volatile, ghi lại bảng
\timing on
ALTER TABLE wiki_lab.orders ADD COLUMN stamp timestamptz DEFAULT clock_timestamp();
\timing off
ROLLBACK;

. ./lab-local.sh
lab_psql -At -f timing.sql | tee timing.txt
awk '/^Time:/ { time[++n] = $2 + 0 } END { printf "ghi lại bảng chậm hơn %.0f lần\n", time[2] / time[1]; exit !(time[2] > 5 * time[1]) }' timing.txt
echo 'tỉ lệ ghi lại bảng đạt'

== default hằng, chỉ đổi metadata
Time: 0.503 ms
== default volatile, ghi lại bảng
Time: 53.070 ms
ghi lại bảng chậm hơn 106 lần

Fixture chỉ có 100.000 hàng và các số trên là một lần chạy trên một máy: lab đòi tỉ lệ ít nhất 5 lần, không đòi con số cụ thể. Bài không đo ở quy mô lớn hơn; tài liệu ghi việc dựng lại bảng hoặc index với bảng lớn có thể mất nhiều thời gian và tạm thời cần tới gấp đôi dung lượng đĩa, còn bản đổi metadata thì không ghi lại gì.

DDL đang chờ khóa cũng chặn người khác

Phần dễ bị bỏ sót của migration không nằm ở lúc DDL chạy mà ở lúc nó chờ khóa. Lab ba vòng dưới đây dùng ba phiên nền. Một phiên đọc dài giữ ACCESS SHARE (chỉ đọc, không xung đột với ai trừ ACCESS EXCLUSIVE) trong vài giây; một phiên chạy ALTER TABLE cần ACCESS EXCLUSIVE nên phải chờ; phiên thứ ba là một SELECT bình thường.

• Vòng 1, DDL không có lock_timeout: phiên thứ ba hết thời gian chờ dù phiên đọc dài không hề chặn nó.
• Vòng 2, DDL đặt lock_timeout = 300ms: DDL bỏ cuộc sau 0,3 giây và phiên thứ ba chạy ngay.
• Vòng 3, vòng lặp retry với lock_timeout = 200ms: DDL thử lại tới khi phiên đọc dài kết thúc, trong lúc đó một phiên thăm dò đọc mỗi 0,4 giây và không được bị lỗi.

Vòng 3 còn dùng một phép kiểm nên chạy trước mọi migration: liệt kê các giao dịch của client đã mở quá một ngưỡng, kèm trạng thái và đoạn đầu câu lệnh.

SELECT pid, state, now() - xact_start AS open_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND xact_start < now() - :'threshold'::interval
  AND pid <> pg_backend_pid()
ORDER BY xact_start;

set -eu
. ./lab-local.sh
. ./lab-wait.sh
lab_seed
echo '== vòng 1: DDL không lock_timeout'
( lab_psql -At -c "BEGIN; SELECT count(*) FROM wiki_lab.orders; SELECT pg_sleep(6); COMMIT;" > a1.log 2>&1 ) &
a1=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'" 1
( lab_psql -At -c "ALTER TABLE wiki_lab.orders ADD COLUMN note text" > b1.log 2>&1 ) &
b1=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock' AND query LIKE 'ALTER TABLE%'" 1
echo 'phiên đọc dài chỉ giữ ACCESS SHARE; DDL đang chờ'
out=$(lab_psql -v VERBOSITY=terse -At -c "SET statement_timeout = '1500ms'" -c "SELECT count(*) FROM wiki_lab.orders WHERE id = 1" 2>&1 || true)
echo "$out"
echo "$out" | grep -q 'statement timeout'
wait "$a1" "$b1"
echo '== vòng 2: DDL có lock_timeout'
lab_psql -c 'ALTER TABLE wiki_lab.orders DROP COLUMN note'
( lab_psql -At -c "BEGIN; SELECT count(*) FROM wiki_lab.orders; SELECT pg_sleep(6); COMMIT;" > a2.log 2>&1 ) &
a2=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'" 1
out=$(lab_psql -v VERBOSITY=terse -At -c "SET lock_timeout = '300ms'" -c "ALTER TABLE wiki_lab.orders ADD COLUMN note text" 2>&1 || true)
echo "$out"
echo "$out" | grep -q 'lock timeout'
[ "$(lab_psql -At -c "SET statement_timeout = '1500ms'" -c "SELECT count(*) FROM wiki_lab.orders WHERE id = 1" | tail -n 1)" = 1 ]
echo 'đọc chạy ngay'
wait "$a2"
echo '== vòng 3: retry với lock_timeout ngắn'
( lab_psql -At -c "BEGIN; SELECT count(*) FROM wiki_lab.orders; SELECT pg_sleep(4); COMMIT;" > a3.log 2>&1 ) &
a3=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'" 1
sleep 1
lab_psql -At -F ' | ' -v threshold='500 milliseconds' -f long-txns.sql | tee long.txt
[ "$(wc -l < long.txt)" -eq 1 ]
( n=0
  until lab_psql -At -c "SET lock_timeout = '200ms'" -c "ALTER TABLE wiki_lab.orders ADD COLUMN note text" > /dev/null 2>&1; do
    n=$((n + 1))
    sleep 0.3
  done
  echo "$n" > attempts.txt ) &
m3=$!
errors=0
for poll in 1 2 3 4 5 6 7 8; do
  lab_psql -At -c "SET statement_timeout = '1000ms'" -c "SELECT 1 FROM wiki_lab.orders WHERE id = 1" > /dev/null 2>&1 || errors=$((errors + 1))
  sleep 0.4
done
wait "$a3" "$m3"
echo "số lần retry $(cat attempts.txt), đọc lỗi $errors"
[ "$(cat attempts.txt)" -ge 1 ]
[ "$errors" -eq 0 ]
echo 'retry không chặn đọc'

bash queue.sh

== vòng 1: DDL không lock_timeout
phiên đọc dài chỉ giữ ACCESS SHARE; DDL đang chờ
ERROR:  canceling statement due to statement timeout
== vòng 2: DDL có lock_timeout
ERROR:  canceling statement due to lock timeout
đọc chạy ngay
== vòng 3: retry với lock_timeout ngắn
18861 | active | 00:00:01.189596 | BEGIN; SELECT count(*) FROM wiki_lab.orders; SELECT pg_sleep
số lần retry 5, đọc lỗi 0
retry không chặn đọc

Vòng 1 là điều bảng mức khóa không cho thấy: yêu cầu ACCESS EXCLUSIVE đã vào hàng đợi khóa, và truy vấn đến sau nó xếp phía sau, kể cả SELECT vốn không xung đột với phiên đang giữ khóa. Một migration chạy “vài giây” mà trúng một giao dịch dài có thể làm ứng dụng đứng cho tới khi giao dịch dài đó xong, hoặc cho tới khi DDL tự bỏ vì lock_timeout. Vòng 2 là đối chứng: phiên đọc dài vẫn còn đó, nhưng DDL đã rút khỏi hàng đợi và SELECT chạy ngay, nên thủ phạm làm kẹt truy vấn là DDL đang xếp hàng chứ không phải phiên đọc dài.

Tài liệu PostgreSQL mô tả lock_timeout là giới hạn thời gian một câu lệnh chờ lấy khóa, áp riêng cho mỗi lần xin khóa; 0 (mặc định) là chờ vô hạn. Tài liệu không khuyên đặt nó trong postgresql.conf vì sẽ ảnh hưởng mọi phiên; hãy đặt trong phiên chạy migration, như lab. Vòng 2 cho thấy tác dụng: DDL tự rút lui và hàng đợi được dọn. Vòng 3 ghép thêm retry: mỗi lần xin khóa chỉ chặn người khác tối đa 200 ms, và phiên thăm dò không lỗi lần nào.

Phép kiểm long-txns.sql ở trên đáng giữ trong quy trình migration. Lab dùng ngưỡng 500 ms vì phiên đọc chỉ giữ vài giây; trên hệ thống thật ngưỡng do bạn chọn theo thời gian giao dịch bình thường. Phiên ở trạng thái idle in transaction đáng ngờ nhất vì nó giữ khóa mà không làm gì. Nếu có giao dịch dài thì chờ nó xong hoặc xử lý với người sở hữu nó trước, thay vì trông vào lock_timeout để cứu.

Index và khóa ngoại: bản chặn ghi và bản không chặn

Hai lab nữa kiểm điều bảng ở đầu bài nói về SHARE, SHARE ROW EXCLUSIVE và SHARE UPDATE EXCLUSIVE: ba mức này khác nhau ở việc có chặn ghi hay không. Mỗi lab dùng một phiên giữ DDL trong giao dịch mở vài giây (thay cho thời gian quét hoặc dựng index của một bảng lớn thật) và một phiên ghi INSERT với lock_timeout = 500ms.

set -eu
. ./lab-local.sh
. ./lab-wait.sh
lab_seed
echo '== CREATE INDEX thường'
( lab_psql -At -c "BEGIN; CREATE INDEX ix_plain ON wiki_lab.orders(status); SELECT pg_sleep(4); COMMIT;" > p.log 2>&1 ) &
p=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'" 1
lab_psql -At -F ' ' -c "SELECT l.mode FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE a.wait_event = 'PgSleep' AND l.relation = 'wiki_lab.orders'::regclass AND l.mode <> 'AccessShareLock' ORDER BY 1"
echo "đọc: $(lab_psql -At -c "SET statement_timeout = '1500ms'" -c "SELECT count(*) FROM wiki_lab.orders" | tail -n 1)"
out=$(lab_psql -v VERBOSITY=terse -At -c "SET lock_timeout = '500ms'" -c "INSERT INTO wiki_lab.orders VALUES (900001, 1, 'pending', 100, timestamp '2026-01-01')" 2>&1 || true)
echo "$out" | grep -q 'lock timeout'
echo "ghi: $(echo "$out" | grep -o 'canceling statement due to lock timeout')"
wait "$p"
echo '== CREATE INDEX CONCURRENTLY'
( lab_psql -At -c "BEGIN; SELECT count(*) FROM wiki_lab.orders; SELECT pg_sleep(6); COMMIT;" > old.log 2>&1 ) &
old=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'" 1
( lab_psql -At -c "CREATE INDEX CONCURRENTLY ix_conc ON wiki_lab.orders(status)" > c.log 2>&1 ) &
c=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE query LIKE 'CREATE INDEX CONCURRENTLY%' AND wait_event_type = 'Lock'" 1
lab_psql -At -F ' ' -c "SELECT a.wait_event, l.mode FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE a.query LIKE 'CREATE INDEX CONCURRENTLY%' AND l.relation = 'wiki_lab.orders'::regclass ORDER BY 2"
lab_psql -v VERBOSITY=terse -At -c "SET lock_timeout = '500ms'" -c "INSERT INTO wiki_lab.orders VALUES (900002, 1, 'pending', 100, timestamp '2026-01-01')"
echo 'ghi: insert xong, không chờ'
wait "$old" "$c"
echo "index hợp lệ: $(lab_psql -At -c "SELECT indisvalid FROM pg_index WHERE indexrelid = 'wiki_lab.ix_conc'::regclass")"

bash writers.sh

== CREATE INDEX thường
ShareLock
đọc: 100000
ghi: canceling statement due to lock timeout
== CREATE INDEX CONCURRENTLY
virtualxid ShareUpdateExclusiveLock
ghi: insert xong, không chờ
index hợp lệ: t

CREATE INDEX thường giữ ShareLock: đọc đi qua (100.000), ghi bị chặn tới hết lock_timeout. CREATE INDEX CONCURRENTLY giữ ShareUpdateExclusiveLock nên ghi vẫn chạy, và nó tự chờ ở virtualxid, tức đang đợi giao dịch cũ kết thúc, đúng như tài liệu: cần quét bảng hai lần và chờ mọi giao dịch có thể sửa dữ liệu hoặc dùng index. Tài liệu cũng ghi hai ràng buộc không đo trong lab: bản CONCURRENTLY không chạy được trong khối giao dịch, và nếu gặp lỗi giữa chừng nó để lại index ở trạng thái invalid, bị bỏ qua khi truy vấn nhưng vẫn tốn chi phí cập nhật, nên phải dọn.

Lab cuối so ADD FOREIGN KEY thường với cách tách NOT VALID rồi VALIDATE:

set -eu
. ./lab-local.sh
. ./lab-wait.sh
lab_seed
echo '== ADD FOREIGN KEY có quét'
( lab_psql -At -c "BEGIN; ALTER TABLE wiki_lab.order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id); SELECT pg_sleep(4); COMMIT;" > f.log 2>&1 ) &
f=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'" 1
out=$(lab_psql -v VERBOSITY=terse -At -c "SET lock_timeout = '500ms'" -c "INSERT INTO wiki_lab.order_items VALUES (900001, 1, 'sku-x', 1, 100)" 2>&1 || true)
echo "$out" | grep -q 'lock timeout'
echo "ghi vào bảng con: $(echo "$out" | grep -o 'canceling statement due to lock timeout')"
wait "$f"
lab_psql -c 'ALTER TABLE wiki_lab.order_items DROP CONSTRAINT fk_items_order'
echo '== NOT VALID rồi VALIDATE'
lab_psql -c 'ALTER TABLE wiki_lab.order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id) NOT VALID'
out=$(lab_psql -v VERBOSITY=sqlstate -At -c "INSERT INTO wiki_lab.order_items VALUES (900003, 999999999, 'sku-x', 1, 100)" 2>&1 || true)
echo "$out" | grep -q 23503
echo "dòng mồ côi vẫn bị từ chối: $(echo "$out" | grep -o 23503)"
( lab_psql -At -c "BEGIN; ALTER TABLE wiki_lab.order_items VALIDATE CONSTRAINT fk_items_order; SELECT pg_sleep(4); COMMIT;" > v.log 2>&1 ) &
v=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'" 1
lab_psql -v VERBOSITY=terse -At -c "SET lock_timeout = '500ms'" -c "INSERT INTO wiki_lab.order_items VALUES (900002, 1, 'sku-x', 1, 100)"
echo 'ghi vào bảng con: insert xong, không chờ'
wait "$v"

bash fkwriters.sh

== ADD FOREIGN KEY có quét
ghi vào bảng con: canceling statement due to lock timeout
== NOT VALID rồi VALIDATE
dòng mồ côi vẫn bị từ chối: 23503
ghi vào bảng con: insert xong, không chờ

Với ADD FOREIGN KEY thường, ghi vào bảng con bị chặn tới hết lock_timeout vì phiên DDL giữ ShareRowExclusiveLock suốt giao dịch của nó. Với cách tách đôi, bước ADD ... NOT VALID bỏ qua bước quét, còn bước VALIDATE chỉ giữ ShareUpdateExclusiveLock ở bảng con nên ghi vẫn chạy. Ràng buộc NOT VALID vẫn áp cho các hàng mới ngay từ lúc thêm: lab chèn một dòng con trỏ tới cha không tồn tại và nhận SQLSTATE 23503 (foreign_key_violation), nên không có khoảng hở cho dữ liệu sai chèn vào trước khi VALIDATE xong. Với các hàng đã có sẵn, tài liệu nói VALIDATE quét bảng để bảo đảm không hàng nào vi phạm; bài không đo trường hợp bảng đã có dòng mồ côi từ trước.

Mẫu migration và checklist

Template dưới đây ghép các phép đo thành một hàm chạy một DDL: đặt lock_timeout ngắn, chỉ thử lại khi mã lỗi là 55P03 (lock_not_available), bỏ ngay với mọi lỗi khác (lỗi cú pháp là 42601, syntax_error) và giới hạn số lần thử. Mã lỗi lấy bằng VERBOSITY=sqlstate của psql; các mã trên đều có trong phụ lục mã lỗi của tài liệu PostgreSQL 18.

set -eu
. ./lab-local.sh
. ./lab-wait.sh
ddl_with_retry() {
  statement="$1"
  max="${2:-30}"
  n=0
  while :; do
    if out=$(lab_psql -v VERBOSITY=sqlstate -c "SET lock_timeout = '200ms'" -c "$statement" 2>&1); then
      echo "xong sau $n lần retry"
      return 0
    fi
    case "$out" in
      *55P03*)
        n=$((n + 1))
        [ "$n" -le "$max" ] || { echo "bỏ cuộc sau $max lần retry" >&2; return 1; }
        sleep 0.3
        ;;
      *)
        echo "lỗi không phải khóa, không retry: $out" >&2
        return 1
        ;;
    esac
  done
}
lab_seed
( lab_psql -At -c "BEGIN; SELECT count(*) FROM wiki_lab.orders; SELECT pg_sleep(3); COMMIT;" > h.log 2>&1 ) &
h=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'" 1
ddl_with_retry 'ALTER TABLE wiki_lab.orders ADD COLUMN note text'
wait "$h"
if ddl_with_retry 'ALTER TABLE wiki_lab.orders ADD COLUMN'; then
  echo 'lẽ ra phải dừng' >&2
  exit 1
fi
echo 'lỗi cú pháp dừng ngay, không retry'
( lab_psql -At -c "BEGIN; SELECT count(*) FROM wiki_lab.orders; SELECT pg_sleep(4); COMMIT;" > h2.log 2>&1 ) &
h2=$!
wait_for "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'" 1
if ddl_with_retry 'ALTER TABLE wiki_lab.orders ADD COLUMN limited text' 2 2> limit.err; then
  echo 'lẽ ra phải bỏ cuộc' >&2
  exit 1
fi
cat limit.err
wait "$h2"

bash retry.sh

xong sau 5 lần retry
lỗi cú pháp dừng ngay, không retry
bỏ cuộc sau 2 lần retry
lỗi không phải khóa, không retry: ERROR:  42601

Hàm chỉ là khung: nó thiếu ghi log từng lần thử, cảnh báo khi vượt ngưỡng và quy tắc bỏ cuộc của đội bạn. Checklist dưới đây tách phần máy kiểm được khỏi phần cần người quyết:

Các dòng “Một phần” và “Không” cho thấy giới hạn của máy: nó chỉ kiểm được thứ nó đo được. Lab không thể cho biết bản sao lưu của bạn có khôi phục được hay ứng dụng có chạy được với cả hai phiên bản schema; hai điều đó phải thử thật, trên môi trường tương đương, trước ngày chạy.

Giới hạn và lỗi thường gặp

• Số đo thuộc PostgreSQL 18.6 trên một máy macOS arm64 với bảng 100.000 và 300.000 hàng. Bài chứng minh cơ chế (mức khóa, hàng đợi, ghi lại bảng), không đo độ lớn: thời gian ghi lại bảng phụ thuộc kích thước bảng và index, nên phải thử trên bản sao có kích thước gần thật trước khi tin một con số.
• Chưa đo: MySQL và DDL trực tuyến của nó, replica và độ trễ nhân bản khi DDL chạy, bảng phân vùng, DROP COLUMN và đổi tên cột (tương thích với ứng dụng), lỗi giữa chừng của CREATE INDEX CONCURRENTLY (chỉ có lời tài liệu), SET NOT NULL với ràng buộc kiểm đã có, và hệ quả của việc các dạng ALTER TABLE ghi lại bảng không an toàn với MVCC (tài liệu nói giao dịch dùng snapshot cũ có thể thấy bảng rỗng sau khi bảng được ghi lại).
• Thời gian chờ trong lab (3 đến 6 giây giữ khóa, 200 đến 500 ms lock_timeout) được chọn để chạy ổn định trên máy thử; trên máy rất chậm có thể cần nới.
• Lỗi thường gặp: đặt lock_timeout toàn cục thay vì trong phiên migration; retry mù mọi lỗi; chạy nhiều DDL trong một giao dịch rồi giữ khóa của tất cả tới cuối; thêm index thường lên bảng đang ghi nhiều; kiểm migration trên bảng nhỏ không có giao dịch dài rồi chạy production có giao dịch báo cáo kéo dài.

Dọn

. ./lab-local.sh
lab_clean
test ! -e lab.env

Học tiếp và nguồn

• Khóa ngoại: kiểm tra ngầm, khóa hàng và index cột con.
• Composite index: thứ tự cột và chi phí ghi khi thêm index.
• Lab database dùng chung: dữ liệu, reset và dấu nhận diện của lab.
• PostgreSQL 18, Explicit Locking: các mức khóa bảng, bảng xung đột và việc giữ khóa tới cuối giao dịch.
• PostgreSQL 18, ALTER TABLE: mức khóa từng dạng, ghi lại bảng, NOT VALID và VALIDATE CONSTRAINT.
• PostgreSQL 18, CREATE INDEX: CONCURRENTLY, hai lần quét, chờ giao dịch cũ và index invalid.
• PostgreSQL 18, Client Connection Defaults: lock_timeout.
• PostgreSQL 18, Error Codes: 55P03 (lock_not_available), 42601 (syntax_error), 23503 (foreign_key_violation).

Nguồn chính thức đọc ngày 2026-10-04; số đo trong bài thuộc PostgreSQL 18.6 trên macOS arm64, không có nghiệm thu Docker/Linux hay engine khác.