BEGIN/COMMIT — Transaction Lifecycle

Transaction lifecycle trong Postgres: BEGIN mở state machine, COMMIT đóng — quên đóng là dò mìn

Một transaction trong Postgres không phải chỉ là cặp BEGIN ... COMMIT cú pháp; nó là một state machine sống cùng connection. BEGIN đẩy connection từ idle sang active, mỗi statement kết thúc đẩy nó về idle in transaction đợi statement kế tiếp, một statement lỗi đẩy sang idle in transaction (aborted), và chỉ COMMIT / ROLLBACK mới trả connection về idle. Dev gặp lifecycle này trong việc thật không phải vì cú pháp khó mà vì một BEGIN quên COMMIT trong một code path lỗi: connection nằm trong pool ở idle in transaction vô thời hạn, giữ snapshot và lock, chặn autovacuum, kéo lock chain, làm bảng update-nóng bloat dần rồi cả service chậm chết.

Cơ chế hoạt động

Mặc định mỗi connection ở autocommit mode: mỗi statement là một transaction tự đóng. BEGIN (hoặc START TRANSACTION) tắt autocommit cho tới khi gặp COMMIT / ROLLBACK. Trong khoảng đó connection có một xid (cấp khi cần ghi) và một snapshot, và lifecycle của nó đi qua các trạng thái mà Postgres phơi ra trong pg_stat_activity.state:

• idle — connection mở, không có transaction nào đang chạy.
• active — đang thực thi một statement (kể cả ngoài transaction block).
• idle in transaction — đang trong transaction block, vừa chạy xong một statement, đợi statement kế tiếp hoặc COMMIT/ROLLBACK.
• idle in transaction (aborted) — đang trong transaction, một statement đã ném lỗi, mọi statement tiếp theo trả ERROR: current transaction is aborted, commands ignored until end of transaction block cho tới khi ROLLBACK.
• fastpath function call / disabled — ít gặp, không phải mục tiêu của bài này.

-- t0: state = 'idle'
BEGIN;
-- t1: state = 'idle in transaction' (vừa thực thi xong BEGIN, đợi statement kế)

INSERT INTO orders(user_id, total) VALUES (42, 100);
-- trong lúc chạy: state = 'active'
-- sau khi statement xong: state = 'idle in transaction' lại

INSERT INTO orders(user_id, total) VALUES (NULL, 100);
-- ERROR: null value in column "user_id" violates not-null constraint
-- state = 'idle in transaction (aborted)' — mọi statement tiếp theo bị refuse

SELECT 1;  -- ERROR: current transaction is aborted...
ROLLBACK;
-- state = 'idle' trở lại

Hai cột nữa trong pg_stat_activity đi cùng state để hiểu tuổi của transaction: xact_start (lúc transaction mở) và query_start (lúc statement hiện tại bắt đầu). now() - xact_start là tuổi transaction; now() - state_change là thời gian đã ngồi trong state hiện tại — đặc biệt hữu ích khi state = 'idle in transaction' để biết connection đã treo bao lâu mà không làm gì.

COMMIT ghi một WAL record commit, gọi fsync theo synchronous_commit, đặt bit committed trong pg_xact; xong, snapshot được nhả, mọi lock của transaction được release, connection trả về idle. ROLLBACK không undo dữ liệu mà chỉ đặt bit aborted trong pg_xact; row đã ghi trở thành dead tuple chờ VACUUM — đó là lý do ROLLBACK của 10 triệu row vẫn nhanh. Đóng connection (driver gửi Terminate) trong khi đang ở idle in transaction cũng được Postgres coi như implicit ROLLBACK.

Vấn đề gặp trong production

Failure mode 1: quên COMMIT do exception path không có finally. Đây là dạng cổ điển nhất khi dùng pool. Code happy path gọi COMMIT, code lỗi không gọi gì — connection trả về pool ở idle in transaction:

# Sai: external_call() ném → không ROLLBACK, không COMMIT → connection trả pool ở 'idle in transaction'
def place_order(pool, user_id):
    conn = pool.getconn()
    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)             # nếu raise: bỏ qua COMMIT
    cur.execute("COMMIT")
    pool.putconn(conn)

# Đúng: context manager đảm bảo COMMIT khi thoát bình thường, ROLLBACK khi ném;
# side-effect external_call đẩy RA KHỎI transaction.
def place_order(pool, user_id):
    with pool.connection() as conn:     # tự trả về pool
        with conn.transaction():        # tự BEGIN, tự COMMIT/ROLLBACK
            order_id = conn.execute(
                "INSERT INTO orders(user_id) VALUES (%s) RETURNING id", (user_id,)
            ).fetchone()[0]
    external_call(order_id)             # ngoài transaction, retry idempotent nếu fail

Connection bị "thiu" trong pool ở idle in transaction không trả lock đã giữ, không nhả backend_xmin. autovacuum không được phép tuyên bố row version mới hơn xmin horizon là dead, dead tuple chất đống trên bảng update-nóng. Đây không phải bug Postgres — đây là application bug, nhưng nó phơi bày dưới dạng "DB chậm dần".

Failure mode 2: giữ transaction qua network call ngoài. Một transaction "ngắn" trên giấy thành transaction dài thực tế vì nó bao một HTTP call ra service ngoài:

# Sai: nếu API thanh toán chậm/timeout, transaction giữ snapshot và lock suốt thời gian đó
with conn.transaction():
    conn.execute("UPDATE inventory SET qty = qty - 1 WHERE sku = %s", (sku,))
    charge = payment_api.charge(user_id, amount)   # mạng chậm, có thể nhiều giây
    conn.execute("INSERT INTO payments(...) VALUES (...)", (...))

Cách đúng là cắt transaction thành hai pha: pha 1 ghi intent ở trạng thái pending và COMMIT ngay; gọi service ngoài; pha 2 trong một transaction khác cập nhật trạng thái. Nguyên tắc: không bao giờ giữ transaction mở qua một I/O không phải Postgres.

Failure mode 3: transaction-per-request kéo dài cả request lifetime. Web framework (ORM với "session lifetime = request lifetime") mở transaction ở đầu request, đóng ở cuối. Nếu request có việc lâu (đọc file lớn, render template nặng, đợi cache miss), transaction kéo theo. Ở traffic thấp không ai nhận ra; khi traffic tăng, snapshot horizon bị kéo lùi đồng loạt từ tất cả request, autovacuum hết hiệu lực, bloat ra. Fix: chỉ mở transaction quanh đoạn cần atomicity (vài câu SQL liên tiếp), không bao cả request.

Failure mode 4: aborted transaction "thây ma". Một statement lỗi đẩy connection sang idle in transaction (aborted). Nếu driver/ORM không tự gọi ROLLBACK, connection ở trạng thái này vẫn giữ xid và mọi tài nguyên đã claim. Một số ORM ghi đè cẩu thả: thay vì ROLLBACK khi gặp exception, chúng cố "tiếp tục" và mỗi statement sau đó lại ném current transaction is aborted. Triệu chứng là log đầy lỗi đó nhưng service vẫn "chạy" — chỉ là chạy bằng cách ném và retry. Cách phòng: kiểm tra driver có auto-rollback khi exit context manager với exception; trong pool có loại "test connection" gửi RESET ALL hoặc ROLLBACK trước khi tái phát connection.

Failure mode 5: long transaction → lock chain. Transaction càng dài, càng có cơ hội đụng với các transaction khác trên cùng row/table. Một UPDATE đang chạy giữ ROW EXCLUSIVE; một migration ALTER TABLE đợi ACCESS EXCLUSIVE sẽ chôn sau nó, và mọi SELECT mới đến cũng chôn sau migration vì lock queue FIFO. Một transaction "vô tội" dài vài chục giây có thể làm một deploy migration đáng lẽ chớp nhoáng trở thành downtime cho mọi client trong khoảng đó.

Cách debug và monitor

Triệu chứng cần soi khi nghi transaction lifecycle hỏng:

• Số connection ở idle in transaction tăng đều theo thời gian → application không gọi COMMIT/ROLLBACK ở một path nào đó.
• n_dead_tup trên bảng update-nóng tăng đều, last_autovacuum không nhúc nhích.
• Log Postgres có cụm current transaction is aborted, commands ignored → driver/ORM không rollback khi exception.
• Mỗi lần deploy migration, một loạt request timeout cùng lúc → lock chain sau long transaction.

Công cụ chính là pg_stat_activity. Query để đếm và xếp loại theo state — đây là cách lấy ra idle transaction count mà ta muốn alert:

SELECT state,
       count(*)                                         AS conns,
       max(now() - state_change)                        AS oldest_in_state,
       max(now() - xact_start) FILTER (WHERE xact_start IS NOT NULL) AS oldest_xact
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY conns DESC;

Khi thấy idle in transaction > 0 và tăng đều, list cụ thể những con đang treo:

SELECT pid, usename, application_name, client_addr,
       state,
       now() - state_change AS idle_for,
       now() - xact_start   AS xact_age,
       backend_xmin,
       left(query, 120)     AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start NULLS LAST;

application_name thường lộ ra service nào quên đóng (set qua connection string application_name=orders-api). last_query cho biết statement cuối cùng đã chạy trước khi connection ngồi không — chính là đoạn code sau đó quên COMMIT.

Setting cấp cluster làm hàng rào cuối (đều có trong Postgres docs):

• idle_in_transaction_session_timeout — server tự terminate connection ngồi quá lâu ở idle in transaction. Đặt theo workload (vd vài chục giây tới vài phút). Khi kích, log ghi FATAL: terminating connection due to idle-in-transaction timeout.
• idle_session_timeout — terminate connection idle nói chung (không trong transaction); hữu ích để chống pool grow vô hạn.
• statement_timeout — kill statement chạy quá lâu.
• lock_timeout — huỷ statement đợi lock quá ngưỡng (đặt cho migration script).
• log_min_duration_statement, log_lock_waits = on, deadlock_timeout — để mọi anomaly có dấu vết.

Metric cần alert (đẩy từ pg_stat_activity lên monitoring system, vd exporter của Prometheus):

• pg_idle_in_transaction_count — đếm rows có state = 'idle in transaction'. Baseline thường là 0 hoặc rất thấp; tăng đều = leak.
• pg_oldest_xact_age_seconds — max(now() - xact_start) toàn cluster. Vượt ngưỡng nghiệp vụ (vd vài phút) là cảnh báo cứng.
• Tỉ lệ xact_rollback / (xact_commit + xact_rollback) lấy delta từ pg_stat_database — lệch khỏi baseline là dấu hiệu retry logic hỏng hoặc constraint violation tăng.

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

• Mọi service production set application_name rõ ràng (theo service name + instance) để khi soi pg_stat_activity biết ngay thủ phạm.
• Bật idle_in_transaction_session_timeout cấp cluster — đây là chiếc lưới cuối ngăn một path lỗi của một service làm sập cluster.
• Trong code, dùng context manager / with / using thay vì BEGIN/COMMIT thủ công.
• Alert trên oldest_xact_age chứ không chỉ trên count: một transaction nhiều phút nguy hiểm hơn nhiều transaction vài giây.

Tradeoff

Bao một nhóm statement vào transaction cho atomicity và isolation: hoặc tất cả ghi xuống, hoặc không gì cả; mọi SELECT bên trong thấy snapshot nhất quán. Cái giá tăng theo độ dài transaction. Transaction càng dài thì: (1) backend_xmin càng kéo lùi xmin horizon toàn cluster, autovacuum càng khó dọn dead tuple → bloat; (2) lock đã lấy giữ càng lâu, queue lock càng dài, mọi waiter (đặc biệt DDL cần ACCESS EXCLUSIVE) càng dễ chôn theo cả traffic sau; (3) cơ hội đụng serialization conflict / deadlock càng cao, đặc biệt ở REPEATABLE READ/SERIALIZABLE; (4) khi crash hoặc terminate, cleanup càng phải gánh nhiều việc dồn lên. Ngược lại nếu xé một nghiệp vụ cần atomicity ra nhiều transaction nhỏ "cho ngắn", mất luôn semantics — phải tự cài compensating logic ở application, phức tạp gấp bội. Quy tắc thực tế: bao đúng cái cần atomic vào transaction, không hơn không kém; transaction chỉ chứa SQL, không chứa I/O ngoài Postgres; mọi service production phải set idle_in_transaction_session_timeout + statement_timeout + lock_timeout ở mức cluster.

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

BEGIN và COMMIT ảnh hưởng gì tới connection và tới cluster? Vì sao một transaction "quên đóng" lại đủ làm chậm cả DB?

BEGIN chuyển connection từ autocommit sang transaction mode: lấy một snapshot (xmin/xmax/xip) và sẽ được cấp xid khi có ghi đầu tiên; trong pg_stat_activity connection chuyển từ idle sang active khi đang chạy statement và idle in transaction khi statement xong nhưng chưa COMMIT. COMMIT ghi WAL record commit, fsync theo synchronous_commit, đặt bit committed trong pg_xact, nhả snapshot và mọi lock của transaction, đưa connection về idle — đây là moment thay đổi thành visible cho mọi backend khác và moment durability được bảo đảm. ROLLBACK không undo gì, chỉ đặt bit aborted; row đã ghi thành dead tuple cho VACUUM dọn sau, nên rollback luôn nhanh. Vì snapshot của transaction còn sống kéo lùi xmin horizon toàn cluster, autovacuum không dọn được dead tuple mới hơn horizon đó — một connection nằm idle in transaction vài giờ trên bảng update-nóng đủ làm dead tuple chất đống, bloat phình, query chậm dần dù plan không đổi; song song lock mà transaction đó giữ chặn các DDL/migration đến sau, và queue lock FIFO làm mọi SELECT mới sau migration cũng bị chôn theo. Cách xử lý: code dùng context manager để COMMIT/ROLLBACK luôn được gọi; không bao transaction qua network call ngoài Postgres; set idle_in_transaction_session_timeout, statement_timeout, lock_timeout ở mức cluster; alert trên idle in transaction count và oldest xact_age từ pg_stat_activity. Điểm ăn điểm là gắn được state machine của connection (idle → active → idle in transaction → idle in transaction (aborted)) với hậu quả production cụ thể (horizon block autovacuum, lock chain block migration) và biện pháp phòng cấp cluster — không chỉ trả lời "BEGIN mở, COMMIT đóng".

Hands-on

Mục tiêu: tận mắt thấy connection đi qua từng state, ép idle in transaction để quan sát metric tăng, kích idle_in_transaction_session_timeout cứu hộ, và đo lock chain do long transaction.

Dựng Postgres trong container, bật log đủ để thấy lock wait và timeout:

docker run -d --name pg-lifecycle \
  -e POSTGRES_PASSWORD=pw -p 5435:5432 \
  postgres:16 \
  -c log_lock_waits=on \
  -c deadlock_timeout=500ms \
  -c log_min_duration_statement=0 \
  -c log_line_prefix='%m [%p] %a %u@%d '

sleep 5
docker exec -i pg-lifecycle 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, 5000) 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, 100000) g;
SQL

Bước 1 — Quan sát state transitions. Mở session A với application_name rõ ràng:

docker exec -it pg-lifecycle psql "postgresql://postgres:pw@localhost/postgres?application_name=demo-A"

-- Session A
BEGIN;
-- ngay sau BEGIN: state = 'idle in transaction'
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
-- trong lúc chạy: state = 'active'; sau khi xong: 'idle in transaction'

Từ session B (terminal khác, container cùng):

docker exec -it pg-lifecycle psql "postgresql://postgres:pw@localhost/postgres?application_name=watcher"

-- Session B: nhìn trạng thái session A
SELECT pid, application_name, state,
       now() - state_change AS in_state_for,
       now() - xact_start   AS xact_age,
       left(query, 80)      AS last_query
FROM pg_stat_activity
WHERE application_name = 'demo-A';

Bước 2 — Đẩy session A vào aborted state, rồi rollback:

-- Session A
INSERT INTO accounts(owner, balance) VALUES (NULL, 0);
-- ERROR: null value in column "owner" violates not-null constraint
-- state = 'idle in transaction (aborted)'
SELECT 1;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- state = 'idle' lại

Trong session B re-run query đầu bước 1 sau mỗi câu trên session A để thấy state đổi.

Bước 3 — Quên COMMIT: dead tuple tăng, autovacuum đứng yên. Trong session A:

-- Session A
BEGIN;
SELECT 1;  -- ngồi yên, snapshot đã lấy

Trong session B, churn bảng sessions để sinh dead tuple, rồi đếm idle in transaction và soi bảng:

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

-- Metric: idle transaction count + oldest xact age
SELECT count(*) FILTER (WHERE state='idle in transaction') AS idle_in_tx,
       max(now() - xact_start) FILTER (WHERE xact_start IS NOT NULL) AS oldest_xact
FROM pg_stat_activity
WHERE backend_type = 'client backend';

-- Bloat dồn lên mà 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';

Đóng session A:

-- Session A
COMMIT;

Đợi vài giây, query lại pg_stat_user_tables — last_autovacuum sẽ mới và n_dead_tup giảm dần.

Bước 4 — idle_in_transaction_session_timeout cứu hộ. Set timeout cấp cluster:

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

Trong session A, mở transaction rồi chờ:

-- Session A
BEGIN;
SELECT 1;
-- không gõ gì thêm; sau ~10s server tự ngắt:
-- FATAL: terminating connection due to idle-in-transaction timeout

Trong session B kiểm tra: connection demo-A đã biến mất khỏi pg_stat_activity, và log Postgres có dòng terminating connection due to idle-in-transaction timeout:

docker logs pg-lifecycle | grep -i 'idle-in-transaction'

Bước 5 — Long transaction → lock chain block migration. Reset timeout về 0 (tắt) để demo:

-- Session B
ALTER SYSTEM SET idle_in_transaction_session_timeout = 0;
SELECT pg_reload_conf();

Session A giữ một transaction động vào accounts:

-- Session A
BEGIN;
UPDATE accounts SET balance = balance WHERE id = 1;
-- không COMMIT

Session C (terminal mới) chạy migration:

docker exec -it pg-lifecycle psql "postgresql://postgres:pw@localhost/postgres?application_name=migration"

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

Session D (terminal nữa) đến sau:

-- Session D
SELECT count(*) FROM accounts;
-- treo: chôn sau session C vì FIFO

Quay lại session B dựng lock graph:

-- Session B
SELECT blocked.pid AS blocked_pid, blocked.application_name AS blocked_app,
       blocking.pid AS blocking_pid, blocking.application_name AS blocking_app,
       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 (session C Ctrl-C), chạy lại với lock_timeout:

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

Session D không bị chôn. Session A COMMIT, chạy lại ALTER, thành công ngay.

Bước 6 — Đo idle_in_transaction_count như một metric. Mở vài session ngồi idle in transaction rồi lấy snapshot:

-- Session B
SELECT
  count(*) FILTER (WHERE state = 'idle in transaction')           AS idle_in_tx,
  count(*) FILTER (WHERE state = 'idle in transaction (aborted)') AS idle_in_tx_aborted,
  count(*) FILTER (WHERE state = 'active')                        AS active,
  count(*) FILTER (WHERE state = 'idle')                          AS idle
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Đây chính là các counter cần export lên Prometheus/Datadog để alert. Khi idle_in_tx vượt ngưỡng baseline hoặc oldest_xact vượt vài phút, page on-call.

Dọn dẹp:

docker rm -f pg-lifecycle