Transaction — ACID Transaction

ACID transaction trong Postgres: vì sao một BEGIN quên COMMIT đủ để hạ cả service

Một transaction trong Postgres là đơn vị nguyên tử bao quanh nhiều statement: hoặc tất cả thay đổi được ghi vĩnh viễn khi COMMIT, hoặc không gì xảy ra khi ROLLBACK (atomicity); trong lúc chạy nó luôn thấy một snapshot dữ liệu nhất quán theo MVCC (consistency + isolation); và sau khi COMMIT trả về thành công, thay đổi đã nằm trên WAL đã fsync xuống đĩa (durability). Lý do dev gặp nó trong việc thật không phải vì cú pháp BEGIN/COMMIT khó, mà vì một transaction quên đóng — application giữ một connection ở trạng thái idle in transaction — đủ để chặn autovacuum, kéo lock, làm dead tuple chất đống, và cuối cùng làm cả service chậm dần rồi treo.

Cơ chế hoạt động

Một transaction bắt đầu bằng BEGIN (hoặc statement đầu tiên trong autocommit-off mode). Tại thời điểm transaction lấy snapshot đầu tiên, Postgres cấp cho nó một xid (transaction ID) khi cần ghi, và một snapshot gồm xmin (xid nhỏ nhất còn đang chạy), xmax (xid kế tiếp sẽ cấp) và danh sách xip (xid đang in-progress). Mọi SELECT trong transaction này dùng đúng snapshot đó để quyết định row version nào "visible": chỉ row có xmin đã commit trước snapshot và xmax chưa commit (hoặc chưa tồn tại) là thấy được. Đây là cách MVCC tách read khỏi write mà không cần shared lock trên row.

BEGIN;
INSERT INTO orders(user_id, total) VALUES (42, 100) RETURNING id;
INSERT INTO order_items(order_id, sku, qty) VALUES (currval('orders_id_seq'), 'X1', 1);
UPDATE inventory SET qty = qty - 1 WHERE sku = 'X1' AND qty > 0;
COMMIT;
-- nếu bất kỳ statement nào fail trước COMMIT → ROLLBACK đảo toàn bộ

Bên dưới COMMIT: Postgres ghi một WAL record XLOG_XACT_COMMIT cho xid này, gọi fsync xuống đĩa (theo synchronous_commit), rồi đặt bit COMMITTED vào pg_xact (tên cũ clog) tại offset tương ứng xid. Sau lúc đó mọi backend khác đọc clog sẽ thấy xid này committed và row mà nó tạo ra thành visible. ROLLBACK ngược lại không cần làm gì với dữ liệu đã ghi: chỉ ghi XLOG_XACT_ABORT, đặt bit ABORTED trong clog, và để các row đã insert/update lại như "dead tuple" — chúng sẽ bị VACUUM dọn về sau. Đó là vì sao ROLLBACK của 10 triệu row vẫn nhanh: không có "undo log" như InnoDB, chỉ là một flip bit.

Một transaction có thể chứa SAVEPOINT name rồi ROLLBACK TO SAVEPOINT name để rollback một phần — bên trong Postgres mỗi savepoint là một subtransaction với xid riêng. Statement lỗi (vd unique_violation) sẽ đẩy toàn bộ transaction sang trạng thái aborted, mọi statement tiếp theo trả ERROR: current transaction is aborted, commands ignored until end of transaction block cho tới khi ROLLBACK (hoặc rollback tới savepoint trước lỗi).

Vấn đề gặp trong production

Failure mode 1: idle in transaction từ application không đóng connection. Đây là loại bug hay gặp nhất khi app dùng pool nhưng quên gọi commit/rollback trên một path lỗi, hoặc framework giữ transaction qua một HTTP call ngoài. Connection nằm trong pool ở trạng thái idle in transaction không trả lock đã giữ, không nhả snapshot đã lấy. Hai hậu quả:

• Mọi row version mới hơn backend_xmin của transaction này đều không được VACUUM xoá, kể cả khi đã không còn ai dùng. Dead tuple chất đống trong bảng update-nóng, table và index bloat tăng nhanh.
• Lock đã lấy (vd ROW EXCLUSIVE cho update, ACCESS EXCLUSIVE cho DDL) vẫn giữ, các transaction sau xếp hàng đợi.

# Sai: nếu external_call() ném, không có finally → connection trả về pool ở idle in transaction
def place_order(conn, user_id, items):
    cur = conn.cursor()
    cur.execute("BEGIN")
    cur.execute("INSERT INTO orders(user_id) VALUES (%s) RETURNING id", (user_id,))
    order_id = cur.fetchone()[0]
    external_call(order_id)        # HTTP call, có thể chậm hoặc timeout
    cur.execute("COMMIT")

# Đúng: kết transaction TRƯỚC khi gọi service ngoài; đẩy side-effect ra ngoài transaction
def place_order(conn, user_id, items):
    with conn:                          # psycopg2: tự COMMIT khi thoát bình thường, ROLLBACK khi ném
        with conn.cursor() as cur:
            cur.execute("INSERT INTO orders(user_id) VALUES (%s) RETURNING id", (user_id,))
            order_id = cur.fetchone()[0]
    external_call(order_id)             # ngoài transaction; nếu fail thì retry idempotent

Quy tắc: không bao giờ giữ transaction mở qua một network call ngoài Postgres. Đặt idle_in_transaction_session_timeout (vài chục giây đến vài phút tuỳ workload) để Postgres tự kill những connection lỡ rơi vào trạng thái này.

Failure mode 2: transaction dài chặn autovacuum và đẩy bảng vào bloat death-spiral. Một job phân tích chạy BEGIN; SELECT ... FROM huge_table; ...; trong vài giờ giữ snapshot cũ, tức xmin horizon toàn cluster bị kéo lùi về thời điểm job bắt đầu. autovacuum thấy không thể tuyên bố row version mới hơn horizon là "dead" → bỏ qua chúng. Bảng update-nóng (vd sessions, accounts) tiếp tục sinh dead tuple nhưng không ai dọn, size phình ra, query chậm dần, index swell theo. Triệu chứng cổ điển: pg_stat_user_tables.n_dead_tup tăng đều mà last_autovacuum không nhúc nhích; pg_stat_activity cho thấy có một backend với xact_start cách đây nhiều giờ.

Cách xử lý là chia job dài thành nhiều transaction nhỏ (mỗi batch COMMIT), hoặc chạy job analytics trên replica thay vì primary, hoặc dùng REPEATABLE READ ở mức session ngắn rồi COMMIT ngay sau khi có data.

Failure mode 3: lock chain do transaction dài giữ lock mạnh. Một ALTER TABLE cần ACCESS EXCLUSIVE lock; nó đợi sau một transaction đang chạy SELECT lâu (lock ACCESS SHARE). Trong lúc ALTER đợi, mọi SELECT mới đến sau cũng phải xếp sau ALTER vì lock queue trong Postgres là FIFO chặt — đây là cách một migration "nhẹ" làm sập service: không phải vì migration nặng, mà vì nó chôn sau một transaction dài rồi chôn theo mọi traffic mới. Cách phòng: set lock_timeout cho mọi migration (SET lock_timeout = '2s'), và kill các transaction dài trước khi chạy DDL.

Failure mode 4: rely vào auto-commit ngầm rồi mất atomicity. Code dạng "chạy hai statement mà nghĩ là cùng một transaction" trong khi connection đang ở autocommit mode:

# Sai: không có BEGIN; mỗi execute là một transaction riêng → nửa chừng fail là dữ liệu mâu thuẫn
cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")

# Đúng: gói rõ ràng, COMMIT cuối cùng — fail ở statement 2 sẽ ROLLBACK statement 1
with conn:
    with conn.cursor() as cur:
        cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")

Failure mode 5: rollback rate tăng âm thầm. Application retry sai cách (vd retry với cùng key sau unique_violation) làm xact_rollback trên pg_stat_database tăng nhanh hơn xact_commit. Mỗi rollback vẫn cấp xid, vẫn sinh WAL, vẫn để lại row version chết. Tỉ lệ rollback cao bất thường (xact_rollback / (xact_commit + xact_rollback) lớn) là dấu hiệu logic retry/concurrency có vấn đề chứ không phải lỗi DB.

Cách debug và monitor

Triệu chứng cần soi khi nghi vấn liên quan transaction:

• Bảng cụ thể có n_dead_tup tăng đều dù autovacuum được cấu hình; last_autovacuum cũ.
• Query trên bảng update-nóng chậm dần dù plan không đổi (bloat).
• Lock chain dài: pg_locks cho thấy nhiều backend ở trạng thái waiting.
• Mỗi lần deploy migration cả service treo vài chục giây.

Các view và setting cần dùng (đều có trong Postgres docs, không cần extension):

• pg_stat_activity: cột state cho thấy active / idle / idle in transaction / idle in transaction (aborted); xact_start là thời điểm transaction mở, query_start là thời điểm statement hiện tại bắt đầu, backend_xmin là horizon của backend này. Query phát hiện transaction dài:

  SELECT pid, usename, application_name, state,
         now() - xact_start  AS xact_age,
         now() - query_start AS query_age,
         backend_xmin,
         left(query, 100)    AS query
  FROM pg_stat_activity
  WHERE state IN ('idle in transaction', 'idle in transaction (aborted)', 'active')
    AND xact_start IS NOT NULL
  ORDER BY xact_start;

• pg_stat_database: xact_commit, xact_rollback là counter tích luỹ; lấy delta theo thời gian để có commit rate và rollback rate. Tỉ lệ rollback cao là alert.

• pg_locks join với pg_stat_activity để dựng lock graph, tìm "lock holder" của một transaction đang đợi:
  

  SELECT blocked.pid AS blocked_pid,
         blocking.pid AS blocking_pid,
         blocked.query AS blocked_query,
         blocking.query AS blocking_query,
         now() - blocking.xact_start AS blocker_age
  FROM pg_stat_activity blocked
  JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

• pg_stat_user_tables.n_dead_tup và n_live_tup để theo tỉ lệ bloat. Tỉ số n_dead_tup / n_live_tup tăng đều trên bảng update-nóng = horizon đang bị kéo.

• Setting cứu hộ: idle_in_transaction_session_timeout (đóng connection ngồi không trong transaction), statement_timeout (kill statement quá dài), lock_timeout (huỷ statement đợi lock quá lâu), log_min_duration_statement để log mọi statement vượt ngưỡng.

Rule phòng ngừa thực tế:

• Bật log_lock_waits = on và deadlock_timeout = '1s' để mọi lock đợi quá ngưỡng đều xuất hiện trong log.
• Đặt idle_in_transaction_session_timeout ở mức cluster (vd vài phút) — đây là hàng rào cuối ngăn application bug làm sập.
• Mọi migration script bắt đầu bằng SET lock_timeout = '2s'; SET statement_timeout = '0';.
• Alert trên: top xact_age trong pg_stat_activity vượt ngưỡng nghiệp vụ; tỉ lệ rollback của pg_stat_database lệch khỏi baseline.

Tradeoff

ACID đầy đủ trong Postgres cho consistency mạnh: developer luôn nhìn thấy snapshot nhất quán, không lo phantom read trong REPEATABLE READ, không lo write nửa chừng sau crash vì WAL đã fsync. Cái giá là concurrency thực tế giảm khi transaction kéo dài: snapshot giữ làm autovacuum không dọn được dead tuple, lock chain dài hơn, replica phải replay WAL nhiều hơn, và mỗi COMMIT phải đợi fsync (trừ khi hạ synchronous_commit = off — đổi durability lấy throughput, mất transaction gần nhất khi crash). Isolation level cũng là một trục đánh đổi: READ COMMITTED mặc định rẻ và đủ cho hầu hết workload OLTP; REPEATABLE READ tránh non-repeatable read nhưng dễ ăn serialization_failure khi conflict; SERIALIZABLE cho semantics chặt nhất qua SSI nhưng phải retry nhiều khi load tăng. Quy tắc thực tế: transaction phải càng ngắn càng tốt — chỉ chứa SQL, không chứa I/O ngoài; mọi side-effect ra ngoài Postgres (gửi mail, gọi API, ghi queue) đẩy ra ngoài transaction; mọi service production phải set idle_in_transaction_session_timeout và statement_timeout.

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

Transaction trong PostgreSQL hoạt động thế nào, và vì sao một transaction "ngắn nhưng quên đóng" lại đủ làm chậm cả cluster?

Transaction bắt đầu khi backend thực thi BEGIN (hoặc statement đầu tiên trong non-autocommit), Postgres cấp xid khi cần ghi và lấy một snapshot gồm xmin/xmax/xip để mọi SELECT thấy đúng phiên bản dữ liệu nhất quán theo MVCC. COMMIT ghi XLOG_XACT_COMMIT xuống WAL, fsync theo synchronous_commit, rồi set bit committed trong pg_xact — đó là moment thay đổi thành visible cho mọi backend khác và là moment durability được bảo đảm. ROLLBACK ngược lại không "undo" gì cả: chỉ set bit aborted, các tuple đã insert/update trở thành dead tuple để VACUUM dọn sau, nên rollback luôn nhanh. Vì transaction giữ một snapshot, backend_xmin của nó kéo lùi xmin horizon toàn cluster: autovacuum không được phép tuyên bố row version mới hơn horizon là dead, nên trên bảng update-nóng dead tuple chất đống mà không ai dọn — đó là cơ chế một transaction idle in transaction vài giờ làm bloat phình ra, query chậm dần, index swell, autovacuum chạy không tới đâu; thêm vào đó lock mà transaction đó đã giữ chặn cả các DDL/migration đến sau, và queue lock FIFO làm mọi traffic sau migration cũng bị chôn theo. Cách xử lý: không bao giờ giữ transaction qua network call ngoài Postgres; set idle_in_transaction_session_timeout + statement_timeout + lock_timeout ở mức cluster; chia long-running analytics thành nhiều transaction ngắn hoặc chạy trên replica; alert trên xact_age của pg_stat_activity và tỉ lệ rollback của pg_stat_database. Điểm ăn điểm là nêu được cả cơ chế (snapshot + xid + clog + WAL), hậu quả production cụ thể (horizon block autovacuum, lock chain), và biện pháp phòng ngừa cấp cluster — không chỉ là "nhớ gọi COMMIT".

Hands-on

Mục tiêu: tận tay thấy COMMIT vs ROLLBACK khác nhau ra sao; ép một transaction ở idle in transaction để quan sát dead tuple tăng và autovacuum đứng yên; kích lock_timeout cứu một migration khỏi lock chain.

Dựng Postgres:

docker run -d --name pg-tx \
  -e POSTGRES_PASSWORD=pw -p 5434:5432 \
  postgres:16 \
  -c log_lock_waits=on \
  -c deadlock_timeout=500ms \
  -c log_min_duration_statement=0

sleep 5
docker exec -i pg-tx psql -U postgres <<'SQL'
CREATE TABLE accounts (
  id      bigserial PRIMARY KEY,
  owner   text NOT NULL,
  balance numeric NOT NULL
);
INSERT INTO accounts(owner, balance)
SELECT 'u'||g, 1000 FROM generate_series(1, 10000) g;

CREATE TABLE sessions (
  id           bigserial PRIMARY KEY,
  user_id      bigint NOT NULL,
  last_seen_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO sessions(user_id) SELECT g FROM generate_series(1, 200000) g;
SQL

Bước 1 — Atomicity bằng COMMIT vs ROLLBACK. Mở session A:

docker exec -it pg-tx psql -U postgres

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SELECT id, balance FROM accounts WHERE id IN (1,2);  -- A thấy thay đổi

Trong session B (mở terminal mới, cùng container):

-- Session B
SELECT id, balance FROM accounts WHERE id IN (1,2);  -- vẫn thấy giá trị cũ (snapshot)

Trong session A:

ROLLBACK;
SELECT id, balance FROM accounts WHERE id IN (1,2);  -- quay về giá trị cũ, atomicity giữ

Bước 2 — idle in transaction block autovacuum. Session A mở transaction rồi để yên:

-- Session A
BEGIN;
SELECT 1;        -- snapshot đã lấy, backend đang ở 'idle in transaction'

Session B đẩy churn vào sessions để sinh nhiều dead tuple:

-- Session B
SELECT pg_stat_reset();
DO $$
BEGIN
  FOR i IN 1..50 LOOP
    UPDATE sessions SET last_seen_at = now() WHERE user_id <= 200000;
  END LOOP;
END $$;

Quan sát dead tuple và autovacuum không dọn được:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables WHERE relname='sessions';

SELECT pid, state, now() - xact_start AS age, backend_xmin, query
FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' OR state='active';

n_dead_tup lớn nhưng last_autovacuum không nhúc nhích — vì xmin horizon bị session A giữ. Đóng session A:

-- Session A
COMMIT;

Đợi vài giây, query lại pg_stat_user_tables — autovacuum sẽ chạy và n_dead_tup giảm.

Bước 3 — idle_in_transaction_session_timeout cứu hộ. Set timeout rồi tái hiện:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10s';
SELECT pg_reload_conf();

-- Session A
BEGIN; SELECT 1;
-- chờ > 10s, server tự ngắt connection với:
-- FATAL: terminating connection due to idle-in-transaction timeout

Bước 4 — Lock chain và lock_timeout. Session A giữ một transaction động vào bảng:

-- Session A
BEGIN;
UPDATE accounts SET balance = balance WHERE id = 1;   -- giữ row lock

Session B chạy migration không có lock_timeout (sẽ treo):

-- Session B
ALTER TABLE accounts ADD COLUMN currency text;  -- treo đợi ACCESS EXCLUSIVE

Session C đến sau (mọi SELECT mới cũng treo sau ALTER, vì queue FIFO):

-- Session C
SELECT count(*) FROM accounts;  -- treo

Quan sát lock graph từ một session thứ tư:

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       left(blocked.query, 60) AS blocked_q,
       left(blocking.query, 60) AS blocking_q,
       now() - blocking.xact_start AS blocker_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Cancel migration; chạy lại với lock_timeout:

-- Session B
SET lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN currency text;
-- ERROR: canceling statement due to lock timeout

Session C không bị chôn theo. Session A COMMIT, rồi chạy lại ALTER, lần này thành công ngay.

Bước 5 — Commit/rollback rate. Reset stats, chạy hỗn hợp commit/rollback:

SELECT pg_stat_reset();
DO $$ BEGIN
  FOR i IN 1..1000 LOOP
    BEGIN
      INSERT INTO accounts(owner, balance) VALUES ('x'||i, 0);
    EXCEPTION WHEN unique_violation THEN
      NULL;
    END;
  END LOOP;
END $$;

SELECT datname, xact_commit, xact_rollback,
       round(100.0*xact_rollback/NULLIF(xact_commit+xact_rollback,0), 2) AS rollback_pct
FROM pg_stat_database WHERE datname='postgres';

Dọn dẹp:

docker rm -f pg-tx