Lock Monitoring — Production Lock Analysis

Production lock analysis: vì sao 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.

Cơ chế hoạt động

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.

Vấn đề gặp trong production

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:

# 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.

Cách debug và monitor

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:

• Alert "sustained blocking": số session đang đợi lock vượt ngưỡng trong > 1 phút → có contention thật, không phải spike thoáng qua.
• Alert "longest wait": session đợi lock > 30 giây → gần như chắc chắn incident.
• Alert idle-in-tx: số session ở state 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ễ.

Tradeoff

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.

Câu hỏi phỏng vấn

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.

Hands-on

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:

• "Locks by mode": sum by (mode) (pg_locks_count) — stack chart, thấy spike AccessExclusiveLock khi có DDL.
• "Sessions by state": sum by (state) (pg_stat_activity_count) — thấy idle in transaction leak ngay.
• "Deadlocks rate": rate(pg_stat_database_deadlocks_total[5m]).
• "Blocking tree" (table): cài plugin Postgres datasource, chạy 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.