BEGIN, COMMIT, SAVEPOINT — kiểm soát transaction thủ công

psql mặc định chạy ở chế độ autocommit — mỗi câu SQL là một transaction độc lập, commit tức thì. Điều này tiện khi thử nhanh, nhưng là bẫy lớn khi bạn cần nhiều thao tác DML thành công hoặc thất bại cùng nhau. INSERT một user xong, rồi INSERT profile của họ thất bại? Nếu autocommit, user đã tồn tại mà không có profile — data inconsistent, không có lỗi nào báo trước.

BEGIN mở ra một transaction rõ ràng: từ đây mọi statement thuộc về một đơn vị công việc duy nhất, chỉ commit khi bạn nói COMMIT, huỷ toàn bộ khi bạn nói ROLLBACK. SAVEPOINT tinh chỉnh thêm một bước: rollback một phần transaction mà không mất công đoạn trước. Bài này đi qua từng cơ chế, demo thực tế trên TaskFlow, và kết với pattern node-postgres hay bị implement sai nhất trong production.

1. Analogy — Drafts trong Google Docs

Google Docs tự lưu sau mỗi thay đổi — mỗi chữ gõ được lưu ngầm, không cần bấm Save. Đó chính xác là autocommit: mỗi statement một transaction, commit ngay, không có cơ hội xem lại.

Khi cần review kỹ trước khi công bố, bạn mở "Draft mode" — gõ thoải mái, thay đổi bất kỳ, chỉ bấm "Publish" khi đã hài lòng toàn bộ. Nếu không ưng, bạn đóng draft mà không lưu. Đó là BEGIN/COMMIT/ROLLBACK.

Version lịch sử trong Google Docs cho phép bạn đánh dấu một mốc tạm, thử thêm nội dung, rồi quay về mốc đó nếu thấy phần mới sai. Đó là SAVEPOINT.

2. Autocommit — mặc định và demo BEGIN/ROLLBACK

Trong psql (và hầu hết database client), mỗi statement tự động được bọc trong một transaction và commit ngay:

-- Autocommit mac dinh: moi statement la 1 tx rieng biet
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice');
-- Commit ngay lap tuc. Row da ton tai vinh vien.

-- Gio vao explicit transaction
BEGIN;
INSERT INTO users (email, name) VALUES ('[email protected]', 'Bob');
INSERT INTO users (email, name) VALUES ('[email protected]', 'Carol');
ROLLBACK;
-- 0 row insert (bob va carol khong ton tai)
-- alice van con tu cau INSERT truoc BEGIN

-- Kiem tra:
SELECT email FROM users WHERE email IN ('[email protected]', '[email protected]', '[email protected]');
-- Ket qua: chi co [email protected]

Đây là điểm mấu chốt: ROLLBACK huỷ mọi thứ từ sau BEGIN. Statement chạy trước BEGIN (autocommit) không bị ảnh hưởng.

3. BEGIN, COMMIT, ROLLBACK — cú pháp SQL standard

PostgreSQL chấp nhận cả hai dạng mở transaction:

-- Hai cach mo transaction, ket qua giong nhau
BEGIN;
-- hoac:
START TRANSACTION;

Trong transaction, mọi DML nhìn thấy kết quả của nhau ngay lập tức — nhưng chỉ trong session của bạn. Session khác không thấy thay đổi cho đến khi COMMIT:

-- Session A:
BEGIN;
UPDATE tasks SET status = 'done' WHERE id = 42;
-- Session A thay doi status = 'done' ngay lap tuc trong tx cua no.
-- Session B van thay status cu (chua commit).

COMMIT;
-- Bay gio session B moi thay status = 'done'.

Nếu có lỗi xảy ra trong transaction (constraint violation, runtime error…), PostgreSQL đặt transaction vào trạng thái aborted — mọi statement tiếp theo trong cùng transaction sẽ thất bại với thông báo ERROR: current transaction is aborted, commands ignored until end of transaction block. Phải ROLLBACK để thoát trạng thái đó:

BEGIN;
INSERT INTO tasks (project_id, title, status)
  VALUES (999, 'T', 'todo');
-- ERROR: insert or update on table "tasks" violates foreign key constraint
-- Transaction vao trang thai aborted

-- Moi lenh tiep theo deu that bai:
SELECT 1;
-- ERROR: current transaction is aborted...

ROLLBACK;
-- Gio moi session binh thuong tro lai

4. SAVEPOINT — rollback một phần

SAVEPOINT đặt một điểm đánh dấu trong transaction. ROLLBACK TO savepoint_name huỷ mọi thay đổi từ savepoint đó trở về sau mà không huỷ phần trước. Transaction vẫn còn mở — tiếp tục hoặc COMMIT/ROLLBACK toàn bộ sau.

BEGIN;
INSERT INTO tasks (project_id, title, status)
  VALUES (1, 'Task 1', 'todo');  -- T1

SAVEPOINT sp1;

INSERT INTO tasks (project_id, title, status)
  VALUES (1, 'Task 2', 'todo');  -- T2

SAVEPOINT sp2;

INSERT INTO tasks (project_id, title, status)
  VALUES (1, 'Task 3 - du lieu sai', 'invalid_status');  -- T3 loi

ROLLBACK TO sp2;
-- T3 bi huy. T1 va T2 van con trong transaction.

COMMIT;
-- Final: T1 va T2 duoc commit, T3 khong ton tai.

RELEASE SAVEPOINT sp_name xoá savepoint khỏi stack khi không cần nữa — tiết kiệm một chút overhead, không bắt buộc.

PL/pgSQL EXCEPTION block tạo implicit savepoint. Mỗi BEGIN...EXCEPTION...END block trong PL/pgSQL function tự động tạo một savepoint ngầm — khi exception xảy ra, PG rollback về savepoint đó và chạy EXCEPTION handler. Nhờ vậy function có thể bắt lỗi mà không làm hỏng cả transaction bên ngoài:

-- Exception trong function khong lam hong transaction ngoai
CREATE OR REPLACE FUNCTION safe_insert_task(
  p_project_id BIGINT,
  p_title TEXT
) RETURNS BOOLEAN AS $$
BEGIN
  INSERT INTO tasks (project_id, title, status)
    VALUES (p_project_id, p_title, 'todo');
  RETURN TRUE;
EXCEPTION
  WHEN foreign_key_violation THEN
    -- PL/pgSQL tu rollback ve implicit savepoint.
    -- Transaction ngoai van nguyen ven.
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

5. SET TRANSACTION — isolation level và READ ONLY

SET TRANSACTION phải đứng ngay sau BEGIN, trước câu DML/query đầu tiên trong transaction:

-- Set isolation level cho transaction hien tai
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Tat ca query trong tx nay thay cung 1 snapshot tu luc BEGIN
-- Chi tiet ve isolation levels: bai 3 cua module nay
SELECT COUNT(*) FROM tasks WHERE project_id = 1;
COMMIT;

READ ONLY block mọi DML trong transaction — hữu ích cho report query trên replica:

BEGIN;
SET TRANSACTION READ ONLY;
-- Toan bo tx nay chi cho phep SELECT, khong cho phep INSERT/UPDATE/DELETE
SELECT
  p.name AS project,
  COUNT(t.id) AS total_tasks,
  COUNT(t.id) FILTER (WHERE t.status = 'done') AS done_tasks
FROM projects p
LEFT JOIN tasks t ON t.project_id = p.id
GROUP BY p.id, p.name;
COMMIT;

Dùng READ ONLY trên replica giúp PostgreSQL đảm bảo không có write nào lọt qua, đồng thời cho phép planner tối ưu tốt hơn vì biết chắc không có data mutation.

6. Pitfall — long open transaction giết production

-- Phat hien idle-in-transaction sessions lau hon 1 phut
SELECT pid, usename, state, now() - state_change AS idle_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '1 minute';

-- postgresql.conf (hoac ALTER SYSTEM)
idle_in_transaction_session_timeout = '5min'
-- PG tu dong ket thuc session neu idle in transaction qua 5 phut

7. Applied — TaskFlow bulk insert với SAVEPOINT

Scenario: import 1000 task theo batch 100. Nếu một batch có row lỗi, rollback batch đó mà giữ các batch trước:

DO $$
DECLARE
  batch_start INT;
BEGIN
  FOR batch_num IN 1..10 LOOP
    batch_start := (batch_num - 1) * 100 + 1;
    SAVEPOINT batch_checkpoint;
    BEGIN
      INSERT INTO tasks (project_id, title, status)
      SELECT 1, 'Imported task ' || g, 'todo'
      FROM generate_series(batch_start, batch_start + 99) g;
      RELEASE SAVEPOINT batch_checkpoint;
    EXCEPTION WHEN OTHERS THEN
      -- Batch co loi: rollback chi batch nay, giu cac batch truoc
      ROLLBACK TO SAVEPOINT batch_checkpoint;
      RAISE WARNING 'Batch % failed: %', batch_num, SQLERRM;
    END;
  END LOOP;
END;
$$;
-- Ket qua: toi da 900 task (9 batch thanh cong) neu 1 batch loi

Pattern phổ biến trong data migration và ETL: một hàng dữ liệu xấu không phá cả job, công đoạn thành công được giữ lại.

8. Applied — node-postgres pattern đúng

Pattern hay bị implement sai nhất trong production Node.js:

import pg from 'pg';
const pool = new pg.Pool();

async function bulkCreateTasks(projectId, taskTitles) {
  // QUAN TRONG: pool.connect() de co 1 client co dinh cho toan bo tx.
  // KHONG dung pool.query('BEGIN') -- pool co the chon client khac cho moi lenh.
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const results = [];
    for (const title of taskTitles) {
      const res = await client.query(
        `INSERT INTO tasks (project_id, title, status)
         VALUES ($1, $2, 'todo')
         RETURNING id, title`,
        [projectId, title]
      );
      results.push(res.rows[0]);
    }

    await client.query('COMMIT');
    return results;
  } catch (err) {
    // Bat ky loi nao: rollback toan bo, khong co partial insert
    await client.query('ROLLBACK');
    throw err;
  } finally {
    // Luon tra client lai pool, du COMMIT hay ROLLBACK
    client.release();
  }
}

Common bug — tại sao pool.query('BEGIN') sai:

pool.query() lấy client từ pool, chạy xong trả lại ngay. Ba lần gọi liên tiếp có thể dùng ba client khác nhau. BEGIN trên client A, UPDATE trên client B (autocommit — commit ngay), COMMIT trên client C (transaction rỗng). Nếu có lỗi sau UPDATE, không có gì để rollback vì UPDATE đã autocommit ở client B.

9. Deep Dive

10. Tóm tắt

• Autocommit mặc định: mỗi statement trong psql là một transaction độc lập — commit ngay sau khi chạy xong.
• BEGIN / START TRANSACTION: mở explicit transaction; mọi DML tiếp theo thuộc về một đơn vị, chỉ có hiệu lực khi COMMIT.
• ROLLBACK: huỷ toàn bộ thay đổi từ BEGIN — không ảnh hưởng statement trước BEGIN.
• SAVEPOINT: đặt điểm đánh dấu trong transaction; ROLLBACK TO sp huỷ chỉ phần sau sp, giữ nguyên phần trước.
• PL/pgSQL EXCEPTION block tự tạo implicit savepoint — bắt lỗi trong function không làm hỏng transaction bên ngoài.
• SET TRANSACTION READ ONLY: block DML trong transaction, an toàn cho report query trên replica.
• Long open transaction: giữ MVCC snapshot → block VACUUM → table bloat tích lũy. Luôn đặt idle_in_transaction_session_timeout và xử lý COMMIT/ROLLBACK trong finally block.
• node-postgres: bắt buộc dùng pool.connect() cho transaction — không dùng pool.query('BEGIN') vì pool có thể chọn client khác cho mỗi lệnh.

11. Tự kiểm tra

Bài tiếp theo: Isolation levels & 4 anomalies — chỗ DB hay sai nhất