SELECT ... FOR UPDATE
là cách rõ ràng nhất để nói với Postgres "tao sẽ sửa row này, đừng cho ai khác đụng vào cho tới khi tao commit". Nó là một row-level lock thật sự — khác SELECT
thường (chỉ chụp snapshot MVCC, không ngăn ai update song song). Lý do dev gặp nó trong việc thật là class bug lost update: hai transaction cùng đọc một row, cùng tính giá trị mới dựa trên giá trị đọc được, rồi cùng UPDATE
— bản ghi cuối đè bản trước, một nửa thay đổi biến mất không log lỗi gì. FOR UPDATE
ép hai bên xếp hàng tại bước đọc, một bên đợi bên kia commit rồi tự đọc lại bản mới. Đổi lại, nếu nhiều code path khoá nhiều row theo thứ tự khác nhau, Postgres sẽ bắn ERROR: deadlock detected
và một bên transaction bay theo.
Khi một transaction chạy SELECT ... FOR UPDATE
, Postgres không ghi row lock vào lock table chính (như cách nó làm với relation-level lock). Thay vào đó, nó ghi xid của transaction hiện tại vào ** xmax** của chính tuple đó trên heap, kèm cờ infomask đánh dấu "đây là lock chứ chưa phải delete". Hệ quả: ôm row lock cho hàng triệu row gần như không tốn shared memory. Khi một transaction khác chạm cùng row (qua
UPDATE
, DELETE
, hay một SELECT ... FOR UPDATE
nữa), nó đọc xmax
, thấy transaction kia còn sống, và transactionid
pg_locks
để đợi xid đó kết thúc. Đó là cơ chế "đợi xid" lộ ra qua wait_event = 'transactionid'
ở pg_stat_activity
. Khi bên giữ COMMIT
hoặc ROLLBACK
, bên đợi được đánh thức, đọc lại tuple (visibility check theo isolation level), rồi mới chạy tiếp.
-- Session A
BEGIN;
SELECT id, balance FROM accounts WHERE id = 42 FOR UPDATE;
-- giữ row lock trên id=42, chưa commit
-- Session B (terminal khác)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
-- treo, đợi xid của Session A
FOR UPDATE
có bốn biến thể, mạnh dần ngược lại: FOR KEY SHARE
(yếu nhất, chỉ chặn thay đổi key — đây là lock mà foreign key check tự áp khi INSERT
row con), FOR SHARE
, FOR NO KEY UPDATE
(như FOR UPDATE
nhưng không chặn các FOR KEY SHARE
song song — UPDATE
không đụng key tự dùng mode này), và FOR UPDATE
(mạnh nhất, mọi mode khác chặn lẫn nhau). Khi nhiều transaction cùng ôm shared mode trên một row (FOR SHARE
/FOR KEY SHARE
), xmax
không đủ chỗ — Postgres tạo một multixact id đại diện cho tập transaction đang ôm row đó, ghi multixact id vào xmax
. Multixact có table riêng (pg_multixact
), được vacuum riêng, và là nguồn gốc của class incident "multixact wraparound" khi workload có nhiều shared row lock kéo dài.
Hai modifier quan trọng: NOWAIT
báo lỗi ngay nếu không lấy được lock (ERROR: could not obtain lock on row in relation
), SKIP LOCKED
bỏ qua row đang bị lock và đọc row tiếp theo. SKIP LOCKED
là cơ chế gốc cho job queue pattern.
Failure mode 1: lost update vì đọc-tính-ghi không khoá. Đây là incident kinh điển trên mọi domain có counter, balance, inventory, vote count. Read Committed (mặc định Postgres) không chặn được nó — mỗi statement chụp snapshot mới, hai UPDATE
cuối cùng đều thấy row tồn tại và đều chạy:
-- Sai: read-modify-write trên application code, không lock
BEGIN;
SELECT balance INTO v_balance FROM accounts WHERE id = 42;
-- application tính: v_new = v_balance - 100
UPDATE accounts SET balance = v_new WHERE id = 42;
COMMIT;
-- Hai transaction song song cùng đọc balance = 1000, cùng ghi 900.
-- Kết quả đáng lẽ phải là 800, nhưng còn 900. 100 đồng biến mất.
Có hai cách sửa. Cách thẳng: gộp tính toán vào một statement nguyên tử, không cần lock vì UPDATE
tự đọc lại row theo Read Committed re-check:
UPDATE accounts SET balance = balance - 100 WHERE id = 42 AND balance >= 100
RETURNING balance;
Cách thứ hai khi application bắt buộc phải đọc rồi tính giữa chừng (ví dụ gọi service ngoài, format message): khoá hẳn bằng FOR UPDATE
:
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- transaction B chạm cùng row sẽ đợi tới khi mình COMMIT
-- application làm việc gì đó với balance...
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;
Lưu ý: dùng Repeatable Read
/Serializable
không thay được FOR UPDATE
ở pattern này — chúng sẽ phát hiện conflict và ném ERROR: could not serialize access
(SQLSTATE 40001
), bên gọi phải retry. FOR UPDATE
ở Read Committed thì transaction đợi rồi đọc lại, không cần retry layer.
Failure mode 2: deadlock vì lock row không cùng thứ tự. Hai code path khoá hai row theo thứ tự ngược nhau là công thức kinh điển:
-- Transaction A: chuyển tiền 42 -> 99
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 42; -- lock 42
UPDATE accounts SET balance = balance + 100 WHERE id = 99; -- lock 99
-- Transaction B: chuyển tiền 99 -> 42 (cùng lúc)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 99; -- lock 99
UPDATE accounts SET balance = balance + 100 WHERE id = 42; -- đợi A
-- A giờ đợi B ở row 99 → cycle → sau deadlock_timeout (mặc định 1s)
-- Postgres ném ERROR: deadlock detected, một bên rollback
Sửa ở tầng application: luôn lock row theo thứ tự id tăng dần, bất kể nghiệp vụ muốn debit hay credit trước. Cụ thể:
BEGIN;
-- sort id trước khi lock
SELECT id FROM accounts WHERE id IN (42, 99) ORDER BY id FOR UPDATE;
-- sau đó UPDATE theo thứ tự nghiệp vụ
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
UPDATE accounts SET balance = balance + 100 WHERE id = 99;
COMMIT;
Một cách khác: dồn cả nghiệp vụ vào một CTE/function để Postgres tự quyết thứ tự lock theo plan, không bị application chen vào.
Failure mode 3: ôm FOR UPDATE rồi rớt qua boundary application (idle in transaction). Code lấy row, lock, gọi service ngoài, service treo, transaction vẫn open, row vẫn bị lock — mọi worker khác đụng row đó queue lại. Triệu chứng: latency tăng vọt trên một subset row "nóng",
pg_stat_activity
đầy wait_event = 'transactionid'
. Xử: SET idle_in_transaction_session_timeout
cấp cluster để Postgres tự cắt; mọi external call phải có timeout cứng; pattern "lock → gọi service ngoài → update" cần đổi thành "gọi service ngoài → lock → quick update".Failure mode 4: queue table dùng FOR UPDATE thiếu SKIP LOCKED. N worker cùng poll, mỗi worker chạy
SELECT ... FOR UPDATE LIMIT 1
— worker 2 đợi worker 1 nhả, parallel hoá serial. Sửa bằng SKIP LOCKED
:
-- Đúng: nhiều worker pick job song song, mỗi worker một batch riêng
WITH next_jobs AS (
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 50
)
UPDATE jobs SET status = 'processing', picked_at = now()
WHERE id IN (SELECT id FROM next_jobs)
RETURNING *;
SKIP LOCKED
thay đổi semantic: hai worker không bao giờ pick cùng một row, nhưng nếu mọi row top đều đang lock, worker có thể nhận về 0 row dù bảng còn job — đó là cái giá để parallel.
Triệu chứng row lock contention: latency tăng tập trung vào một subset row "nóng" (account chính, inventory bestseller), wait_event_type = 'Lock'
với wait_event = 'transactionid'
hoặc 'tuple'
, CPU server không cao. Khác với relation lock (FIFO trên cả bảng) — row lock chỉ ảnh hưởng transaction đụng đúng row đó.
Query "ai đang đợi row của ai":
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 = 'transactionid'
OR a.wait_event = 'tuple'
OR pg_blocking_pids(a.pid) <> '{}')
ORDER BY xact_age DESC NULLS LAST;
Để thấy row lock cụ thể (lock trên xid của transaction đang ôm row):
SELECT l.pid,
l.locktype,
l.transactionid,
l.mode,
l.granted,
a.application_name,
left(a.query, 120) AS query
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype IN ('transactionid', 'tuple')
ORDER BY l.granted, l.pid;
locktype = 'transactionid'
xuất hiện ở bên đợi (granted = false, đợi xid bên giữ row). locktype = 'tuple'
ít gặp hơn — là lock ngắn trên tuple header khi nhiều backend cùng đua update một row (Postgres dùng nó để serialize giai đoạn cập nhật xmax
).
GUC cần bật:
log_lock_waits = on
— Postgres log mọi lock chờ vượt deadlock_timeout
(mặc định 1s). Cùng với log_line_prefix
chứa %a
, đủ để truy ngược application gây block.deadlock_timeout
— giữ mặc định 1s; giảm thấp hơn làm Postgres tốn CPU dò cycle, cao hơn làm deadlock kéo dài.idle_in_transaction_session_timeout
— chống transaction ôm row lock vì code rớt qua boundary.lock_timeout
— set ở session-level cho job nhạy cảm, tránh transaction đợi vô hạn.Metric tối thiểu phải có:
deadlocks
từ pg_stat_database
— tăng đều = code đang lock không cùng thứ tự, phải sửa.wait_event = 'transactionid'
— vượt baseline = hot row.max(now() - xact_start)
cho session ở state = 'idle in transaction'
) — nguồn gốc hầu hết row lock incident.pg_locks
locktype = 'transactionid'
join với pg_stat_activity.query
để gắn vào endpoint/application_name) — postgres_exporter
(Prometheus) phơi sẵn pg_locks
series.Rule phòng ngừa: mọi code path lock nhiều row phải sort id trước; mọi FOR UPDATE
đi kèm lock_timeout
hoặc statement_timeout
; alert đồng thời trên deadlock counter + transactionid wait count + xact_age.
FOR UPDATE
đổi throughput lấy consistency: tuần tự hoá truy cập tới cùng row, transaction đụng row nóng xếp hàng thay vì chạy song song. Cái giá thấy rõ nhất trên hot row (account VIP, inventory bestseller, settings global) — throughput cap bằng 1 / (thời gian giữ lock trung bình)
. Cái lợi là semantic rõ: không lost update, không cần retry layer như optimistic locking. So với optimistic (UPDATE ... WHERE version = $1
rồi check rows-affected), FOR UPDATE
thắng khi contention thấp tới trung bình và transaction ngắn (đỡ retry, đỡ kéo workflow nghiệp vụ); optimistic thắng khi contention cao nhưng phần lớn lần đọc không dẫn tới ghi (đa số transaction không cần đợi). So với raw atomic UPDATE
(gộp đọc-tính-ghi thành một statement): atomic luôn rẻ hơn nếu logic biểu diễn được trong SQL — FOR UPDATE
chỉ dùng khi phải có một quãng application logic giữa đọc và ghi. Quy tắc thực tế: ưu tiên một UPDATE nguyên tử (SET col = col + delta) nếu được; cần đọc-tính-ghi với logic application thì dùng FOR UPDATE (lock càng hẹp càng tốt, FOR NO KEY UPDATE thay FOR UPDATE khi không sửa key, SKIP LOCKED cho queue, NOWAIT cho real-time path); và luôn sort id trước khi lock nhiều row để tránh deadlock.
SELECT ... FOR UPDATE
dùng khi nào? Nó khácUPDATE
thường ở chỗ nào, và vì sao chỉ tăng isolation lên Repeatable Read không thay thế được nó?
FOR UPDATE
là pessimistic row-level lock: nó ghi xid hiện tại vào xmax
của tuple và ép mọi transaction khác chạm cùng row (UPDATE
, DELETE
, hay SELECT ... FOR UPDATE/SHARE
khác) đăng ký một heavyweight transactionid
lock để đợi tới khi mình COMMIT
/ROLLBACK
. Dùng nó khi có pattern read-modify-write mà phần "modify" phải nằm trên application code — đọc balance, gọi service ngoài hay format message, rồi mới ghi — và không gộp được vào một UPDATE
nguyên tử kiểu SET balance = balance - 100
. Class bug nó chặn là lost update: hai transaction Read Committed cùng đọc balance = 1000, cùng tính 900, cùng UPDATE
, kết quả còn 900 trong khi đáng lẽ phải là 800; cả hai đều "thành công" và không có lỗi. Khác UPDATE
thường: UPDATE
cũng lock row trước khi ghi (cùng cơ chế xmax
), nhưng nó chỉ lock tại thời điểm ghi và Postgres tự đọc lại row (re-check visibility) — nếu logic tính giá trị mới nằm ngoài SQL, UPDATE
thường không bảo vệ được. Vì sao Repeatable Read không thay thế: ở Repeatable Read, transaction giữ snapshot từ statement đầu tiên, khi UPDATE
chạm row đã bị transaction khác commit, Postgres không "đọc bản mới" mà ném ERROR: could not serialize access
(SQLSTATE 40001
) — application phải có retry layer; FOR UPDATE
ở Read Committed thay vào đó đợi rồi tự đọc lại bản mới, transaction tiếp tục chạy bình thường, đơn giản hơn ở tầng code. Hậu quả production đi kèm phải nêu hai cái: (1) hot row contention — throughput cap bằng 1 / thời gian giữ lock
, hot account/inventory thành bottleneck; (2) deadlock khi nhiều code path lock nhiều row theo thứ tự khác nhau, xử bằng quy tắc "luôn sort id tăng dần trước khi lock" (SELECT ... WHERE id IN (...) ORDER BY id FOR UPDATE
). Điểm ăn điểm là gọi tên xmax
transactionid
wait, phân biệt FOR UPDATE
vs Repeatable Read về cơ chế xử lý conflict (đợi vs lỗi serialize), và gắn được FOR NO KEY UPDATE
/ SKIP LOCKED
/ NOWAIT
vào đúng use case.
Mục tiêu: dựng Postgres, tái hiện lost update khi không khoá, sửa bằng FOR UPDATE
, rồi tái hiện deadlock vì lock row sai thứ tự và sửa bằng quy tắc sort id.
Dựng container và seed dữ liệu:
docker run -d --name pg-forupd \
-e POSTGRES_PASSWORD=pw -p 5438:5432 \
postgres:16 \
-c log_lock_waits=on -c deadlock_timeout=1s \
-c log_line_prefix='%m [%p] %a '
sleep 5
docker exec -i pg-forupd psql -U postgres <<'SQL'
CREATE TABLE accounts (
id bigint PRIMARY KEY, owner text NOT NULL,
balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (42,'alice',1000.00),(99,'bob',1000.00);
SQL
Mở hai session psql (A và B), đặt application_name
để query pg_stat_activity
rõ ràng:
docker exec -it pg-forupd psql "postgresql://postgres:pw@localhost/postgres?application_name=sessA"
docker exec -it pg-forupd psql "postgresql://postgres:pw@localhost/postgres?application_name=sessB"
Bước 1 — tái hiện lost update (không FOR UPDATE). Chạy xen kẽ:
-- A: BEGIN; SELECT balance FROM accounts WHERE id=42; -- 1000
-- B: BEGIN; SELECT balance FROM accounts WHERE id=42; -- 1000
-- B: UPDATE accounts SET balance=900 WHERE id=42; COMMIT;
-- A: UPDATE accounts SET balance=900 WHERE id=42; COMMIT;
-- check: SELECT balance FROM accounts WHERE id=42; -- 900, đáng lẽ 800
100 đồng biến mất, không lỗi gì — đó là lost update.
Bước 2 — sửa bằng FOR UPDATE. Reset rồi lặp lại:
UPDATE accounts SET balance=1000 WHERE id=42;
-- A: BEGIN; SELECT balance FROM accounts WHERE id=42 FOR UPDATE; -- ôm row lock
-- B: BEGIN; SELECT balance FROM accounts WHERE id=42 FOR UPDATE; -- treo
Trong khi B đợi, mở session watcher quan sát:
SELECT a.pid, a.application_name, a.wait_event_type, a.wait_event,
pg_blocking_pids(a.pid) AS blocked_by
FROM pg_stat_activity a
WHERE a.application_name IN ('sessA','sessB');
SELECT pid, locktype, transactionid, mode, granted
FROM pg_locks WHERE locktype IN ('transactionid','tuple');
sessB
ở wait_event = 'transactionid'
, blocked_by
chứa pid của sessA
; pg_locks
có một entry locktype='transactionid'
, granted=false
. Tiếp:
-- A: UPDATE accounts SET balance=balance-100 WHERE id=42; COMMIT;
-- B (tự chạy tiếp): UPDATE accounts SET balance=balance-100 WHERE id=42; COMMIT;
-- check: SELECT balance FROM accounts WHERE id=42; -- 800, đúng
Bước 3 — deadlock vì lock sai thứ tự. Reset balance về 1000 cho cả hai row, rồi:
-- A: BEGIN; UPDATE accounts SET balance=balance-50 WHERE id=42; -- lock 42
-- B: BEGIN; UPDATE accounts SET balance=balance-50 WHERE id=99; -- lock 99
-- B: UPDATE accounts SET balance=balance+50 WHERE id=42; -- đợi A
-- A: UPDATE accounts SET balance=balance+50 WHERE id=99; -- đợi B → cycle
-- Sau ~1s: một bên nhận ERROR: deadlock detected, rollback.
Xem log để thấy format chuẩn cho alert (process X waits for ... blocked by process Y):
docker logs pg-forupd 2>&1 | grep -i 'deadlock\|still waiting' | tail
Bước 4 — sửa bằng quy tắc sort id tăng dần. Cả hai code path cùng lock theo ORDER BY id
:
-- A (chuyển 50 từ 42 → 99):
BEGIN;
SELECT id FROM accounts WHERE id IN (42,99) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance=balance-50 WHERE id=42;
UPDATE accounts SET balance=balance+50 WHERE id=99;
COMMIT;
-- B (chuyển 50 từ 99 → 42): cùng pattern lock id tăng dần
BEGIN;
SELECT id FROM accounts WHERE id IN (42,99) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance=balance+50 WHERE id=42;
UPDATE accounts SET balance=balance-50 WHERE id=99;
COMMIT;
Cả hai đua lock theo cùng thứ tự (42 trước, 99 sau): bên nào lấy được 42 trước thì bên kia đợi tại 42, không có cycle.
Bước 5 — SKIP LOCKED cho job queue. Tạo bảng
jobs
và bắn hai worker:
CREATE TABLE jobs (
id bigserial PRIMARY KEY,
status text NOT NULL DEFAULT 'pending',
payload jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO jobs(payload) SELECT jsonb_build_object('n',g) FROM generate_series(1,100) g;
-- Chạy ở cả A và B, không COMMIT:
BEGIN;
WITH next_jobs AS (
SELECT id FROM jobs WHERE status='pending'
ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 10
)
UPDATE jobs SET status='processing'
WHERE id IN (SELECT id FROM next_jobs) RETURNING id;
Hai worker pick id khác nhau. Bỏ SKIP LOCKED
, worker thứ hai treo đợi worker thứ nhất — đó là khác biệt giữa queue parallel và queue serial.
Dọn dẹp: docker rm -f pg-forupd
.