Row Lock — FOR UPDATE

PostgreSQL's SELECT ... FOR UPDATE provides a row-level lock that prevents lost updates by blocking concurrent transactions from modifying the same row until the lock holder commits. The lock is implemented by storing the transaction ID in the tuple's xmax field, avoiding shared memory overhead. Variants include FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, and FOR UPDATE, with modifiers NOWAIT and SKIP LOCKED for different concurrency needs.

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: php -- 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ác UPDATE 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 .