Transaction State — Idle in Transaction

Idle in transaction: connection ngồi không nhưng vẫn giữ snapshot, VACUUM đứng hình và bảng phình

idle in transaction là trạng thái mà pg_stat_activity.state đặt cho một connection đã BEGIN, đã chạy xong statement gần nhất, và đang ngồi đợi statement kế tiếp hoặc COMMIT/ROLLBACK. Trên giấy nó "rảnh"; trong thực tế nó vẫn cầm một snapshot, vẫn giữ backend_xmin, vẫn ôm mọi lock đã claim từ đầu transaction. Một connection trong state này chỉ vài chục giây không nguy hiểm; cùng connection đó ngồi vài giờ trên cluster có write traffic là kịch bản kinh điển khiến bảng update-nóng bloat, autovacuum đứng yên, replica conflict, và DBA on-call bị page lúc 3 giờ sáng. Đây không phải bug Postgres mà là application code path đã lỡ rời transaction mở rồi đi làm việc khác (đợi user input, gọi HTTP ra ngoài, ngủ trong queue worker) — Postgres chỉ trung thực phản chiếu lại.

Cơ chế hoạt động

Khi một connection gửi BEGIN, Postgres ghi nhận transaction nhưng chưa cấp xid (chỉ cấp khi câu lệnh ghi đầu tiên xuất hiện) và lập tức gán cho backend này một snapshot: tập hợp các xid mà transaction sẽ coi là visible. Snapshot này được phản chiếu ra cột backend_xmin trong pg_stat_activity. Ngay sau BEGIN, state của connection là idle in transaction. Mỗi câu lệnh sau đó đẩy connection sang active trong lúc chạy, rồi trả về idle in transaction ngay khi statement hoàn tất — và nó ở đó vô thời hạn, cho tới khi client gửi câu lệnh kế tiếp, gửi COMMIT/ROLLBACK, hoặc connection bị terminate.

-- Session A
BEGIN;
-- pg_stat_activity.state = 'idle in transaction'
-- pg_stat_activity.backend_xmin = <xid horizon ngay tại thời điểm BEGIN>

SELECT count(*) FROM orders;
-- trong lúc chạy: state = 'active'
-- xong: state = 'idle in transaction', snapshot KHÔNG bị nhả

-- (client đi gọi HTTP ngoài, hoặc đơn giản là quên gửi COMMIT)
-- nhiều giờ trôi qua. state vẫn 'idle in transaction'.
-- backend_xmin vẫn pin xmin horizon của cluster.

Điểm cốt tử là backend_xmin: VACUUM (và autovacuum) chỉ được phép thu hồi một dead tuple khi xmax của tuple đó già hơn mọi backend_xmin đang sống trong cluster. Nói cách khác, transaction đang sống của bất kỳ backend nào — kể cả backend đang ngồi idle in transaction không làm gì — đều kéo lùi xmin horizon toàn cluster về thời điểm transaction đó bắt đầu. Mọi row đã chết sau thời điểm đó vẫn được giữ lại "đề phòng" transaction kia muốn đọc, dù trên thực tế transaction kia chẳng làm gì cả. Đây là lý do một backend idle in transaction không cần ghi gì, không cần đọc gì, vẫn đủ sức làm autovacuum vô hiệu trên các bảng update-nóng.

Hai trường khác trong pg_stat_activity bổ sung để định lượng: xact_start là lúc transaction bắt đầu (tuổi transaction = now() - xact_start), state_change là lúc chuyển vào state hiện tại (thời gian "ngồi không trong transaction" = now() - state_change khi state = 'idle in transaction'). Khi cần đếm và liệt kê thủ phạm, đây là hai cột cần soi cùng với state và backend_xmin.

Postgres cũng phân biệt idle in transaction (aborted) — một statement trong transaction đã ném lỗi, và Postgres không chấp nhận statement nào nữa cho tới ROLLBACK. Snapshot vẫn còn, backend_xmin vẫn pin, hậu quả cluster giống hệt idle in transaction thường — nhưng dấu vết trong log thường lộ ra dưới dạng cụm current transaction is aborted, commands ignored until end of transaction block, giúp khoanh vùng nhanh hơn.

Vấn đề gặp trong production

Failure mode 1: giữ transaction qua chờ user input. Đây là dạng kinh điển trong các UI có "edit mode". Ai đó mở form chỉnh sửa, app mở transaction để khoá row, rồi đi ăn trưa. Connection ngồi idle in transaction đủ lâu để autovacuum đứng yên trên đúng bảng đó:

# Sai: transaction mở từ lúc render form tới lúc user submit (có thể nhiều phút/giờ)
def edit_invoice(pool, invoice_id):
    conn = pool.getconn()
    conn.autocommit = False
    cur = conn.cursor()
    cur.execute("SELECT * FROM invoices WHERE id=%s FOR UPDATE", (invoice_id,))
    invoice = cur.fetchone()
    render_edit_form(invoice)        # trả HTML về client, đợi user submit
    new_data = wait_for_user_submit()# có thể vô thời hạn
    cur.execute("UPDATE invoices SET ... WHERE id=%s", (...,))
    conn.commit()

# Đúng: không giữ DB transaction qua biên client. Dùng optimistic locking.
def edit_invoice_load(pool, invoice_id):
    with pool.connection() as conn:
        row = conn.execute(
            "SELECT id, version, payload FROM invoices WHERE id=%s", (invoice_id,)
        ).fetchone()
    return row  # transaction đóng ngay, version trả về cho client

def edit_invoice_save(pool, invoice_id, version, new_payload):
    with pool.connection() as conn, conn.transaction():
        updated = conn.execute(
            "UPDATE invoices SET payload=%s, version=version+1 "
            "WHERE id=%s AND version=%s",
            (new_payload, invoice_id, version),
        ).rowcount
        if updated == 0:
            raise StaleVersionError()

Nguyên tắc: không bao giờ giữ transaction mở qua một biên không kiểm soát được thời gian (user, network, queue, sleep). Trạng thái lock phải nằm trong row (column version), không nằm trong transaction.

Failure mode 2: ORM session bám theo request lifetime + một step chậm. Nhiều framework dựng pattern "session bắt đầu khi request đến, commit khi response trả". Lý thuyết tốt; thực tế hỏng khi trong request có một bước chậm — đợi cache miss, đọc S3, gọi service ngoài. Transaction vô tình kéo dài bằng cả request, và mỗi request chậm là một backend idle in transaction trên cluster:

# Sai: middleware tự BEGIN ở đầu request, COMMIT ở cuối
@app.route("/checkout")
def checkout():
    cart = db.session.query(Cart).get(current_user.cart_id)   # mở transaction
    cart.totals = recompute(cart)
    receipt = pdf_renderer.render(cart)                        # I/O nặng, vài giây
    s3.put_object(Bucket=B, Key=k, Body=receipt)               # network ra ngoài
    return "ok"  # framework gọi COMMIT ở đây

# Đúng: chỉ mở transaction quanh đoạn cần atomicity; I/O ngoài ra ngoài transaction.
@app.route("/checkout")
def checkout():
    with db.begin():
        cart = db.session.query(Cart).get(current_user.cart_id)
        cart.totals = recompute(cart)
    # commit ở đây, connection trả về pool ở 'idle' chứ không 'idle in transaction'
    receipt = pdf_renderer.render(cart)
    s3.put_object(Bucket=B, Key=k, Body=receipt)
    return "ok"

Triệu chứng đặc trưng: ở traffic thấp không thấy gì, traffic cao một chút thì dead tuple ramp lên và query đột nhiên chậm dần dù plan không đổi.

Failure mode 3: exception path không rollback, connection trả pool ở idle in transaction (aborted). Driver/ORM bị bypass (raw cursor.execute("BEGIN") thủ công), một câu lệnh ném, code path lỗi không gọi ROLLBACK, pool nhận lại connection và giao cho request kế. Request kế gửi statement đầu tiên và nhận ERROR: current transaction is aborted, commands ignored until end of transaction block cho mọi câu — log đầy lỗi đó là dấu chỉ. Cách phòng: connection pool phải có "reset on return" (PgBouncer server_reset_query, hoặc trình quản lý connection của driver tự gửi ROLLBACK khi exit context với exception). Không bao giờ dùng raw BEGIN/COMMIT qua chuỗi statement; luôn dùng context manager.

Failure mode 4: queue worker mở transaction trước khi BLPOP/poll. Một worker code cẩu thả mở transaction để đảm bảo "khi xử lý job thì atomicity", rồi đợi job đến từ Redis/RabbitMQ. Khi không có job, worker nằm idle in transaction cả vài phút giữa các job:

# Sai: BEGIN trước khi đợi job → giữa hai job worker nằm idle in transaction
while True:
    conn.execute("BEGIN")
    job = redis.blpop("jobs", timeout=300)  # có thể đợi 5 phút
    if job:
        process(job, conn)
        conn.execute("COMMIT")
    else:
        conn.execute("ROLLBACK")

# Đúng: đợi job trước (ngoài DB), chỉ BEGIN khi đã có job để xử lý
while True:
    job = redis.blpop("jobs", timeout=300)
    if not job:
        continue
    with conn.transaction():
        process(job, conn)

Failure mode 5: VACUUM bị chặn → bloat → query nhanh thành query chậm. Đây là failure mode tổng — hợp lưu của bốn cái trên. Một backend nào đó ngồi idle in transaction đủ lâu; autovacuum chạy đúng lịch, log của nó ghi tuples: ... removed, ... remain, X dead but not yet removable với X tăng dần; n_dead_tup trên bảng tăng đều dù last_autovacuum mới; query trên bảng đó chậm dần vì index và heap phình ra; pg_stat_user_tables.n_dead_tup có thể vượt n_live_tup. Đây là kịch bản đã được viết lại nhiều lần trong engineering blog của các công ty chạy Postgres lớn (các bài về long-running transactions từ Heap, Citus, GitLab về tác động lên autovacuum và xmin horizon). Cách sửa không nằm trong VACUUM — phải tìm và giết backend_xmin đang pin horizon.

Cách debug và monitor

Triệu chứng cần soi khi nghi idle in transaction:

• n_dead_tup trên một bảng update-nóng tăng đều nhưng last_autovacuum vừa chạy gần đây — dấu hiệu autovacuum không thu hồi được dead tuple vì horizon bị pin.
• Trong log autovacuum xuất hiện cụm dead but not yet removable với số tăng dần qua các lần chạy.
• Query latency tăng dần trên một bảng dù plan không đổi và data volume không đổi đáng kể.
• pg_database_size tăng nhanh hơn tốc độ ghi mới.

Query khoanh vùng — đếm theo state và tìm backend_xmin cổ nhất:

SELECT state,
       count(*)                             AS conns,
       max(now() - state_change)            AS oldest_in_state,
       max(now() - xact_start)              AS oldest_xact,
       min(backend_xmin::text::bigint)      AS oldest_xmin
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY conns DESC;

Liệt kê cụ thể các backend đang idle in transaction, kèm application_name để biết service nào:

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

last_query cho thấy statement cuối cùng đã chạy ngay trước khi connection ngồi không — đó chính là dòng code mà sau nó application "quên" COMMIT/ROLLBACK. application_name (set qua connection string application_name=... hoặc SET application_name = ...) chỉ thẳng vào service thủ phạm.

So sánh backend_xmin cổ nhất với xmin horizon toàn cluster để khẳng định backend kia đúng là kẻ chặn VACUUM:

SELECT datname, age(datfrozenxid) AS frozen_age FROM pg_database;

SELECT now() - xact_start AS xact_age,
       application_name, pid, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin ASC
LIMIT 5;

Khi cần can thiệp khẩn cấp: SELECT pg_cancel_backend(pid) để huỷ statement hiện tại (vô tác dụng với idle in transaction vì không có statement đang chạy); SELECT pg_terminate_backend(pid) để cắt hẳn connection — Postgres sẽ implicit ROLLBACK và nhả snapshot, xmin horizon tiến lên, autovacuum thu hồi được dead tuple ở lần chạy kế.

Hàng rào cấp cluster — các GUC có sẵn trong Postgres:

• idle_in_transaction_session_timeout — server tự terminate connection ngồi quá lâu ở idle in transaction. Khi kích, log ghi FATAL: terminating connection due to idle-in-transaction timeout. Đây là chiếc lưới cuối; mọi cluster production nên bật ở mức vài chục giây tới vài phút tuỳ workload.
• statement_timeout, lock_timeout — không trực tiếp xử idle in transaction nhưng cắt được các nguyên nhân liên đới.
• log_min_duration_statement để có dấu vết của statement chậm; log_line_prefix chứa %a để mọi log lộ application_name.

Metric cần đẩy lên monitoring (Prometheus exporter cho Postgres phơi ra các view này):

• pg_idle_in_transaction_count — đếm state = 'idle in transaction'. Baseline thường rất thấp; tăng đều là leak.
• pg_oldest_xact_age_seconds — max(now() - xact_start) toàn cluster. Vượt ngưỡng nghiệp vụ (vài phút) là alert cứng.
• pg_stat_user_tables.n_dead_tup per table — vọt lên không kèm last_autovacuum cập nhật là dấu hiệu autovacuum bị chặn.

Rule phòng ngừa: bật idle_in_transaction_session_timeout cấp cluster; set application_name rõ ràng theo service trong mọi connection string; cấm raw BEGIN/COMMIT trong code (chỉ dùng context manager); alert đồng thời trên count + oldest age.

Tradeoff

idle in transaction không phải bug mà là state hợp lệ — Postgres phải hỗ trợ nó để cho phép interactive session (psql), pipelining, và transaction nhiều statement do client điều khiển nhịp. Cái giá là application phải tự chịu trách nhiệm không để state này kéo dài: snapshot và backend_xmin đã cấp thì còn pin cho tới khi transaction kết thúc, và Postgres không có cách nào tự "đoán" rằng client đã quên đóng. Bật idle_in_transaction_session_timeout thấp (vài giây) giải quyết bloat triệt để nhưng đánh sập các use case hợp lệ có nhịp dài (vd workflow đa-step có suy nghĩ giữa các statement, hoặc job admin lớn dạng psql chạy nhiều câu thủ công); bật quá cao (hàng giờ) thì mất luôn ý nghĩa hàng rào. Cản trở duy nhất ở phía application — context manager bao đúng phạm vi atomic, không bao I/O ngoài — không có cost runtime nào đáng kể. Quy tắc thực tế: mọi cluster production phải có idle_in_transaction_session_timeout bật ở mức vài chục giây tới vài phút khớp với SLA của workload; application code không bao giờ giữ transaction qua một biên mà thời gian không kiểm soát được; alert trên idle in transaction count + oldest xact_age từ pg_stat_activity, không đợi tới khi user kêu chậm.

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

idle in transaction là gì, vì sao một connection ở state này lại đủ làm chậm cả cluster, và xử lý thế nào?

idle in transaction là pg_stat_activity.state của một connection đã BEGIN, chạy xong statement gần nhất, và đang đợi statement kế hoặc COMMIT/ROLLBACK. Nó "rảnh" về CPU nhưng vẫn cầm snapshot và vẫn ôm backend_xmin đã cấp lúc BEGIN; Postgres trung thực coi transaction đó còn sống. Hệ quả production nằm ở xmin horizon: VACUUM (và autovacuum) chỉ thu hồi được dead tuple già hơn mọi backend_xmin sống trong cluster, nên một connection ngồi idle in transaction vài giờ kéo lùi horizon vài giờ, dead tuple sau thời điểm đó không dọn được dù autovacuum vẫn chạy đúng lịch; log autovacuum hiện cụm dead but not yet removable; bảng update-nóng phình, index phình, query chậm dần dù plan không đổi. Cộng thêm: lock đã claim từ đầu transaction chưa nhả, mọi DDL/migration đợi ACCESS EXCLUSIVE xếp hàng sau nó, queue FIFO chôn cả các SELECT thường tới sau migration. Xử lý có ba lớp: (1) cấp application — không giữ transaction qua user input, không bao I/O ngoài Postgres trong transaction, queue worker đợi job trước khi BEGIN, ORM session không trùng request lifetime; (2) cấp cluster — bật idle_in_transaction_session_timeout để server tự terminate connection ngồi quá lâu, set application_name cho mọi service để truy thủ phạm trong pg_stat_activity, log có %a trong log_line_prefix; (3) cấp on-call — alert trên count idle in transaction + oldest xact_age + n_dead_tup không giảm sau autovacuum; xử lý nóng bằng pg_terminate_backend(pid) lên backend pin horizon cổ nhất. Điểm ăn điểm là nối được cơ chế (backend_xmin pin horizon) với hậu quả production cụ thể (autovacuum bất lực, bloat, lock chain block migration) và biện pháp đủ ba tầng — không chỉ trả lời "nó là connection chưa COMMIT".

Hands-on

Mục tiêu: dựng một Postgres, sinh một backend ngồi idle in transaction, churn dead tuple trên một bảng update-nóng, xác nhận VACUUM không thu hồi được, rồi gỡ bằng pg_terminate_backend và bằng idle_in_transaction_session_timeout — so sánh n_dead_tup trước/sau.

Dựng container:

docker run -d --name pg-iit \
  -e POSTGRES_PASSWORD=pw -p 5436:5432 \
  postgres:16 \
  -c log_min_duration_statement=0 \
  -c log_autovacuum_min_duration=0 \
  -c log_line_prefix='%m [%p] %a %u@%d '

sleep 5
docker exec -i pg-iit psql -U postgres <<'SQL'
CREATE TABLE counters (
  id     bigserial PRIMARY KEY,
  bucket int NOT NULL,
  value  bigint NOT NULL
);
INSERT INTO counters(bucket, value)
SELECT g % 1000, 0 FROM generate_series(1, 50000) g;
SQL

Bước 1 — Sinh một backend idle in transaction. Trong terminal 1:

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

-- Session A (leaker)
BEGIN;
SELECT 1;
-- Không gõ gì thêm. state = 'idle in transaction'.

Bước 2 — Trong terminal 2, quan sát state và xmin:

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

-- Session B (watcher)
SELECT pid, application_name, state,
       now() - state_change AS idle_for,
       now() - xact_start   AS xact_age,
       backend_xmin
FROM pg_stat_activity
WHERE application_name = 'leaker';

Bước 3 — Churn bảng counters để sinh dead tuple, ép autovacuum chạy:

-- Session B
DO $$
BEGIN
  FOR i IN 1..50 LOOP
    UPDATE counters SET value = value + 1 WHERE bucket = (i % 1000);
  END LOOP;
END $$;

VACUUM (VERBOSE) counters;

Đọc output VACUUM VERBOSE: dòng tuples: X removed, Y remain, Z dead but not yet removable — Z chính là số tuple đã chết nhưng vacuum không dám thu hồi vì backend_xmin của session A vẫn pin horizon ở quá khứ.

-- Session B
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'counters';

n_dead_tup cao và không giảm dù vừa VACUUM xong.

Bước 4 — Gỡ bằng pg_terminate_backend. Trong session B:

-- Session B
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'leaker'
  AND state = 'idle in transaction';

-- Session A bị disconnect: log thấy 'FATAL: terminating connection due to administrator command'

VACUUM (VERBOSE) counters;

Lần này dead but not yet removable về 0; n_dead_tup xuống ~0.

Bước 5 — Dựng lại scenario và để idle_in_transaction_session_timeout xử lý tự động. Trong session B:

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

Mở lại session A:

-- Session A
BEGIN;
SELECT 1;
-- Đợi >10s. Server tự ngắt:
-- FATAL: terminating connection due to idle-in-transaction timeout

Trong session B xem log:

docker logs pg-iit 2>&1 | grep -i 'idle-in-transaction'

Bước 6 — Đo metric như production sẽ scrape:

-- 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,
  EXTRACT(EPOCH FROM max(now() - xact_start))                          AS oldest_xact_seconds
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Bốn counter + một gauge phía trên là đúng tập metric tối thiểu cần export lên Prometheus/Datadog. Alert rule thực tế: idle_in_tx > baseline trong 5 phút hoặc oldest_xact_seconds > 300 → page on-call.

Bước 7 (tuỳ chọn) — Tái hiện idle in transaction (aborted):

-- Session A
BEGIN;
INSERT INTO counters(bucket, value) VALUES (NULL, 0);
-- ERROR: null value in column "bucket" ...
-- state = 'idle in transaction (aborted)'
SELECT 1;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block

Trong session B query pg_stat_activity xác nhận state, rồi ROLLBACK để release.

Dọn dẹp:

docker rm -f pg-iit