Lock Monitoring — Production Lock Analysis

A developer outlines a method for monitoring lock contention in PostgreSQL by joining pg_stat_activity and pg_locks views and using pg_blocking_pids() to build a blocking tree. The approach helps quickly identify root causes of production freezes, such as idle-in-transaction sessions holding AccessExclusiveLock, reducing incident resolution from 30 minutes to 3 minutes.

pg stat activity một mình không đủ, và join với pg locks mới ra root cause Lock contention trong Postgres hiếm khi báo bằng error — nó báo bằng wait event type = 'Lock' ở pg stat activity và bằng latency tăng từ phía application. Khi một incident xảy ra "API treo, không ai biết tại sao" , thứ team cần trong 60 giây đầu là một bức tranh: PID nào đang đợi, đợi lock loại gì trên object nào, bị block bởi PID nào, PID block đó đang chạy query gì và đã giữ transaction bao lâu . pg stat activity một mình chỉ trả lời được nửa câu hỏi "ai đang đợi" ; pg locks một mình chỉ trả lời nửa còn lại "ai giữ gì" . Phải join hai view này — và bám theo pg blocking pids — để dựng được blocking tree. Không có dashboard cho luồng dữ liệu này là lý do điển hình một production freeze kéo dài 30 phút thay vì 3 phút: incident commander phải mò ad-hoc bằng psql , gõ sai query, miss idle in transaction đang giữ AccessExclusiveLock của một migration nửa đời trước. pg locks là một view phơi nội dung trực tiếp của shared lock manager trong shared memory. Mỗi dòng là một lock request đã granted hoặc đang chờ thuộc một backend. Theo Postgres docs phần "System Views → pg locks", các column then chốt: locktype relation , transactionid , tuple , virtualxid , advisory ... , relation OID — join pg class , transactionid , virtualtransaction , pid backend PID , mode AccessShareLock , RowExclusiveLock , ShareUpdateExclusiveLock , AccessExclusiveLock ... , granted bool , fastpath lock đi qua fast-path tránh shared lock manager , và waitstart timestamp bắt đầu chờ — bổ sung sau v14, hữu ích để đo lock wait time mà không cần snapshot diff . pg stat activity là view phơi trạng thái runtime của mỗi backend: pid , usename , datname , application name , client addr , backend start , xact start , query start , state active , idle , idle in transaction , idle in transaction aborted , wait event type , wait event , backend xid , backend xmin , query , backend type . Đây là nguồn duy nhất biết một PID đang chạy query gì và transaction sống bao lâu . Function pg blocking pids pid docs phần "System Information Functions → Session Information" trả về array PID đang block PID truyền vào. Nó là wrapper trên lock manager: cho mỗi lock request granted=false của PID đó, tìm các lock granted xung đột theo lock conflict matrix và trả về PID giữ chúng. Function này quan trọng vì self-join thủ công trên pg locks rất dễ sai cho transactionid lock, advisory lock, hay tuple lock — pg blocking pids đã encode đúng conflict matrix. Query nền tảng để dựng blocking tree gần như identical với mẫu nổi tiếng trong Postgres Wiki "Lock Monitoring" : SELECT blocked.pid AS blocked pid, blocked.usename AS blocked user, blocked.application name AS blocked app, blocked.wait event type, blocked.wait event, now - blocked.query start AS blocked for, left blocked.query, 200 AS blocked query, blocking.pid AS blocking pid, blocking.usename AS blocking user, blocking.application name AS blocking app, blocking.state AS blocking state, now - blocking.xact start AS blocking xact age, left blocking.query, 200 AS blocking query FROM pg stat activity AS blocked JOIN LATERAL unnest pg blocking pids blocked.pid AS bpid ON true JOIN pg stat activity AS blocking ON blocking.pid = bpid WHERE blocked.wait event type = 'Lock' ORDER BY blocked for DESC; Mỗi dòng output là một cạnh trong wait-for graph live. Sort theo blocked for để đẩy người đợi lâu nhất lên đầu — gần như luôn là root cause cần kill hoặc cần điều tra. Failure mode 1: idle in transaction giữ lock vô thời hạn. Một backend BEGIN; UPDATE ...; -- quên COMMIT rồi để connection nằm im trong pool. Row-level lock xmax + heavyweight transactionid lock nó nắm không bao giờ nhả; mọi UPDATE / DELETE chạm row đó đều treo. Triệu chứng phía app: vài endpoint cụ thể timeout, các endpoint khác vẫn ổn. Không có dashboard lock thì khó tìm — connection đứng ở state = 'idle in transaction' không xuất hiện trong query log, không có error nào ném. Code điển hình gây ra: python Sai: workflow phía app gọi external API trong transaction def update order order id, payload : conn = pool.getconn try: conn.execute "BEGIN" conn.execute "UPDATE orders SET status='processing' WHERE id=%s", order id, Lock row order id, sau đó gọi external API có thể treo vài phút resp = http client.post "https://payment.example/charge", json=payload, timeout=120 conn.execute "UPDATE orders SET status=%s WHERE id=%s", resp.status, order id conn.execute "COMMIT" finally: pool.putconn conn Khi http client.post treo vì DNS chậm hay upstream rate-limit, transaction sống suốt thời gian đó, row orders.id = order id lock chặt. Sửa: tách network I/O ra khỏi transaction commit trạng thái intermediate 'pending' rồi mở transaction mới sau khi external call xong , và bật idle in transaction session timeout mặc định 0 = không bao giờ kill ở cấp database hoặc role: ALTER ROLE app user SET idle in transaction session timeout = '30s'; ALTER ROLE app user SET statement timeout = '10s'; ALTER ROLE app user SET lock timeout = '3s'; Failure mode 2: DDL kẹt giữa hai DML, block toàn bộ traffic trên bảng. Postgres lock queue là FIFO: khi ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ... xin AccessExclusiveLock trên một bảng mà một SELECT dài đang giữ AccessShareLock , DDL nằm trong queue chờ. Đây là chỗ đau: mọi SELECT mới đến sau DDL cũng phải xếp hàng vì conflict với AccessExclusiveLock pending , kể cả khi pending lock chưa được granted. Một transaction analytics 5 phút trên bảng orders + một migration vô tình chạy giữa peak = toàn bộ traffic chạm orders treo 5 phút. Phía app thấy "tất cả endpoint dùng orders cùng đơ" — dễ nhầm với DB down. -- Sai: chạy migration không có lock timeout vào giờ peak ALTER TABLE orders ADD COLUMN refund reason text; -- Treo vì SELECT analytics đang chạy, đồng thời block mọi request mới Sửa: trước mọi DDL, set lock timeout ngắn để fail nhanh thay vì giữ queue: SET lock timeout = '2s'; SET statement timeout = '30s'; ALTER TABLE orders ADD COLUMN refund reason text; -- Nếu không lấy được lock trong 2s, ném ERROR 55P03 lock not available -- queue được giải phóng, DML mới tiếp tục Pattern này được nhắc trong nhiều engineering blog Braintree, GitLab, PayPal như "zero-downtime migration" rule số một: không bao giờ chạy DDL không có lock timeout trên production. Failure mode 3: lock contention không ai biết vì không monitor. Workload "chỉ chậm chứ không lỗi" — p99 latency tăng từ vài chục ms lên gần một giây, nhưng error rate vẫn 0. Team chỉ thấy "API hơi chậm". Không có dashboard lock thì nguyên nhân thường bị quy nhầm cho "CPU cao", "network chậm", "GC pause". Sau hàng tuần, root cause hoá ra là một hot row counter, balance, inventory bị tất cả request UPDATE theo kiểu SELECT ... FOR UPDATE rồi update — backend xếp hàng tuần tự trên row đó. pg stat activity.wait event = 'transactionid' chờ lock chuyển sang granted khi transaction kia commit là dấu hiệu — không có dashboard thì không thấy. Failure mode 4: autovacuum bị cancel/skip vì DDL pending. Autovacuum xin ShareUpdateExclusiveLock trên bảng để vacuum. Khi gặp một DDL pending AccessExclusiveLock , autovacuum tự cancel để nhường — nhưng dưới load DDL chạy đều, autovacuum không bao giờ chạy được, bloat dồn, query plan chậm dần. Triệu chứng: pg stat user tables.last autovacuum ngày càng cũ trên các bảng nóng. Đây là class incident không "freeze" tức thì nhưng vỡ chậm — chỉ có lock dashboard kết hợp vacuum dashboard mới bắt được. Bật log trước. Trên production luôn bật: log lock waits = on deadlock timeout = 1s log line prefix = '%m %p %a %u@%d ' log min duration statement = 1000 Khi một backend đợi lock vượt deadlock timeout , Postgres in một dòng LOG: process N still waiting for ... after X ms ... DETAIL: Process holding the lock: M . Đây là nguồn truth nhất cho post-mortem khi không có ai snapshot pg locks đúng thời điểm. Query blocking tree xem trong "Cơ chế hoạt động" là query xương sống. Lưu nó thành view trong schema ops để incident commander chỉ cần SELECT FROM ops.blocking tree . Variant tổng hợp theo PID giữ lock — đếm số PID bị block và tổng thời gian chờ — phơi ra như Grafana panel "top blockers". Snapshot pg locks thô khi cần độ sâu : SELECT l.locktype, l.mode, l.granted, l.pid, c.relname, l.transactionid, l.virtualtransaction, a.state, a.wait event type, a.wait event, now - a.xact start AS xact age, left a.query, 120 AS query FROM pg locks l LEFT JOIN pg class c ON c.oid = l.relation LEFT JOIN pg stat activity a ON a.pid = l.pid WHERE l.database = SELECT oid FROM pg database WHERE datname = current database ORDER BY l.granted, c.relname; Xem cả granted lẫn pending trên cùng object để hiểu thứ tự queue. Metric phơi ra Grafana. postgres exporter Prometheus Community phơi sẵn các series quan trọng: pg locks count gauge theo mode , database , pg stat activity count theo state , pg stat database xact commit / rollback , pg stat database deadlocks . Bổ sung custom query trong file YAML của exporter để phơi blocking tree counter — ví dụ một metric đếm số session đang đợi lock, và max thời gian chờ. Hai metric này lên Grafana làm cảnh báo: idle in transaction vượt ngưỡng → leak transaction từ app. Rule phòng ngừa. Mọi role app set statement timeout , lock timeout , idle in transaction session timeout mặc định. Mọi migration tự động set lock timeout ngắn. Mọi connection pool config idle timeout < idle in transaction session timeout của Postgres để pool reset connection trước khi Postgres kill. Lock dashboard luôn-on, không phải "dựng khi cần" — vì khi cần thì đã trễ. pg locks được phơi từ shared lock manager — đọc nó tốn một short-lived shared lock trên lock manager partition, không free. Polling 1Hz từ exporter là rẻ; polling tần số cao từ nhiều exporter song song có thể tạo contention nhẹ trên chính lock manager trên cluster lớn có hàng chục nghìn lock đồng thời. Trade: tần số scrape thấp = phát hiện trễ; tần số cao = overhead nhỏ nhưng có. Rule thực tế: 5–15s scrape interval cho cluster production size vừa, không cần dưới 1s trừ khi đang truy một incident. log lock waits = on viết thêm vào server log mỗi lần lock wait vượt deadlock timeout . Trên cluster hot, log có thể phình nhanh; nhưng tắt nó = mất khả năng post-mortem. Cách giữ là kèm log rotation/pipeline rsyslog → Loki/CloudWatch thay vì để file local phình. Đừng giảm deadlock timeout xuống vài chục ms chỉ để có nhiều log hơn — Postgres docs cảnh báo: mọi lock wait vượt ngưỡng kích hoạt graph algorithm dò deadlock, CPU lãng phí. Có dashboard không bằng có alert đúng. Một dashboard đẹp mà không ai nhìn lúc 3 giờ sáng vô nghĩa. Ngược lại, alert quá nhạy mỗi lock wait vài trăm ms cũng page tạo alert fatigue, ops mute alert, incident thật miss. Quy tắc: alert trên sustained blocking đợi N giây trong M giây , không alert trên transient lock wait — Postgres luôn có lock wait ngắn dưới load bình thường. Quy tắc thực tế: dashboard lock + alert blocking tree là bắt buộc cho mọi production Postgres, ngang với CPU/disk/replication lag — đặt statement timeout/lock timeout/idle in transaction session timeout mặc định ở mọi role app, bật log lock waits = on, scrape pg locks qua exporter mỗi 5–15s, và alert theo blocking duration chứ không theo lock count tức thời . Một service Postgres production bỗng treo, latency mọi endpoint tăng vọt nhưng CPU và disk vẫn thấp. Debug thế nào, dùng view/function nào, và sửa ra sao? Triệu chứng "latency tăng đều mà CPU thấp" gần như chắc chắn là lock contention chứ không phải resource saturation. Bước đầu: vào psql với superuser, chạy query join pg stat activity với pg blocking pids để dựng blocking tree — mỗi backend có wait event type = 'Lock' được unnest qua pg blocking pids pid để lấy danh sách PID block nó, rồi join lại pg stat activity để biết PID block đang chạy query gì, state , và xact start cách bây giờ bao lâu. Sort theo now - query start DESC để root cause ở đầu. Các pattern hay gặp: 1 PID block ở state = 'idle in transaction' với xact age nhiều phút — một backend BEGIN ... UPDATE ... rồi gọi external API treo, không bao giờ commit, lock row hot bị mọi request mới đụng vào treo theo. Fix tức thì: SELECT pg terminate backend pid cái idle-in-tx; fix lâu dài: bật idle in transaction session timeout ở role, không bao giờ gọi network I/O trong transaction. 2 Một DDL pending AccessExclusiveLock trên bảng nóng làm mọi SELECT mới đến sau cũng phải xếp hàng dù DDL chưa granted Postgres lock queue FIFO — kill DDL hoặc đợi nó xong; sau đó luôn chạy DDL với SET lock timeout = '2s' . 3 Hot row contention — nhiều backend đợi transactionid của một transaction đang UPDATE cùng row; fix bằng chia hot row counter sharding , batch update, hoặc đổi data model. Để không lặp lại incident, dashboard lock phải tồn tại sẵn: postgres exporter scrape pg locks count by mode , pg stat activity count by state , và custom query đếm số session đang chờ lock + thời gian chờ dài nhất. Alert trên idle in transaction count N , longest blocked seconds 30s , không alert trên lock count tức thời luôn dao động . Bật log lock waits = on để mọi lock wait deadlock timeout đẩy vào log, ship sang Loki/CloudWatch để post-mortem. Điểm ăn điểm là gọi đúng tên pg blocking pids , phân biệt được wait event = 'transactionid' đợi xmax vs wait event = 'relation' đợi heavyweight relation lock , nêu được lock queue FIFO làm DDL pending block luôn DML mới, và nhớ idle in transaction session timeout / lock timeout là defense in depth bắt buộc. Mục tiêu: dựng Postgres + postgres exporter + Prometheus + Grafana, tái hiện ba kịch bản idle-in-transaction giữ row lock, DDL block traffic, hot row contention , viết view ops.blocking tree , scrape qua exporter, dựng Grafana panel + alert. Bước 1 — dựng stack monitoring đầy đủ với docker-compose. Tạo thư mục lab: mkdir -p ~/pg-lock-lab && cd ~/pg-lock-lab File docker-compose.yml : services: postgres: image: postgres:16 environment: POSTGRES PASSWORD: pw command: - "postgres" - "-c" - "log lock waits=on" - "-c" - "log line prefix=%m %p %a %u@%d " - "-c" - "log min duration statement=500" - "-c" - "deadlock timeout=1s" ports: - "5440:5432" exporter: image: quay.io/prometheuscommunity/postgres-exporter environment: DATA SOURCE NAME: "postgresql://postgres:pw@postgres:5432/postgres?sslmode=disable" ports: - "9187:9187" depends on: - postgres prometheus: image: prom/prometheus volumes: - "./prometheus.yml:/etc/prometheus/prometheus.yml" ports: - "9090:9090" grafana: image: grafana/grafana environment: GF AUTH ANONYMOUS ENABLED: "true" GF AUTH ANONYMOUS ORG ROLE: Admin ports: - "3000:3000" File prometheus.yml : global: scrape interval: 5s scrape configs: - job name: postgres static configs: - targets: - "exporter:9187" Chạy stack: docker compose up -d && sleep 8 . Bước 2 — schema và view monitoring. Vào psql: docker compose exec postgres psql -U postgres , rồi: CREATE SCHEMA ops; CREATE OR REPLACE VIEW ops.blocking tree AS SELECT blocked.pid AS blocked pid, blocked.application name AS blocked app, blocked.wait event type, blocked.wait event, EXTRACT EPOCH FROM now - blocked.query start AS blocked for s, left blocked.query, 200 AS blocked query, blocking.pid AS blocking pid, blocking.application name AS blocking app, blocking.state AS blocking state, EXTRACT EPOCH FROM now - blocking.xact start AS blocking xact age s, left blocking.query, 200 AS blocking query FROM pg stat activity blocked JOIN LATERAL unnest pg blocking pids blocked.pid AS bpid ON true JOIN pg stat activity blocking ON blocking.pid = bpid WHERE blocked.wait event type = 'Lock'; CREATE TABLE accounts id bigint PRIMARY KEY, owner text, balance numeric 12,2 NOT NULL DEFAULT 0 ; INSERT INTO accounts SELECT g, 'u'||g, 1000 FROM generate series 1, 10000 g; Bước 3 — tái hiện idle-in-transaction giữ lock. Terminal A mở session, BEGIN + UPDATE, không commit : docker compose exec postgres psql -U postgres Trong psql: BEGIN; UPDATE accounts SET balance = balance - 1 WHERE id = 42; -- để psql nằm đây, không gõ gì thêm Terminal B cùng lúc, request mới đụng row 42 : docker compose exec postgres psql -U postgres -c "SET lock timeout = '5s'; UPDATE accounts SET balance = balance + 1 WHERE id = 42;" Treo, sau 5s ném ERROR 55P03 lock not available Terminal C — quan sát blocking tree live: docker compose exec postgres psql -U postgres -c "SELECT FROM ops.blocking tree;" Thấy blocked pid terminal B , blocking pid terminal A với blocking state = idle in transaction Bước 4 — fix bằng idle in transaction session timeout. docker compose exec postgres psql -U postgres -c "ALTER ROLE postgres SET idle in transaction session timeout = '10s';" Tạo lại session A như Bước 3, đợi 10s — Postgres tự terminate, lock nhả docker compose logs postgres 2 &1 | grep -i idle-in-transaction Bước 5 — tái hiện DDL block traffic. Terminal A — chạy SELECT dài giả lập analytics: docker compose exec postgres psql -U postgres -c "BEGIN; SELECT pg sleep 60 , count FROM accounts;" Terminal B — chạy DDL không có lock timeout: docker compose exec postgres psql -U postgres -c "ALTER TABLE accounts ADD COLUMN note text;" Treo — đợi terminal A Terminal C — request mới đến sau DDL cũng kẹt: docker compose exec postgres psql -U postgres -c "SELECT count FROM accounts;" Cũng treo, dù chỉ cần AccessShareLock — vì AccessExclusiveLock của DDL đang pending phía trước Terminal D — xem blocking tree để confirm: docker compose exec postgres psql -U postgres -c "SELECT FROM ops.blocking tree;" Sửa: kill DDL terminal B Ctrl-C và chạy lại đúng cách: docker compose exec postgres psql -U postgres -c "SET lock timeout='2s'; ALTER TABLE accounts ADD COLUMN note text;" Fail nhanh sau 2s với ERROR 55P03, queue được giải phóng, traffic chạy tiếp Bước 6 — Grafana dashboard. Mở http://localhost:3000 , add datasource Prometheus http://prometheus:9090 . Tạo dashboard với các panel: sum by mode pg locks count — stack chart, thấy spike AccessExclusiveLock khi có DDL. sum by state pg stat activity count — thấy idle in transaction leak ngay. rate pg stat database deadlocks total 5m . SELECT FROM ops.blocking tree ORDER BY blocked for s DESC .Alert quan trọng nhất là longest blocked seconds . Thêm vào file queries.yml rồi mount vào exporter với flag --extend.query-path=/etc/postgres exporter/queries.yml : pg blocking: query: | SELECT count AS blocked sessions, COALESCE max EXTRACT EPOCH FROM now - query start , 0 AS longest blocked seconds FROM pg stat activity WHERE wait event type = 'Lock' metrics: - blocked sessions: usage: GAUGE description: Sessions waiting on a lock - longest blocked seconds: usage: GAUGE description: Longest lock wait in seconds Alert pg blocking longest blocked seconds 30 cho ra page khi có session kẹt 30s — đây là alert có giá trị nhất trong toàn bộ stack lock monitoring. Dọn dẹp: docker compose down -v .