BEGIN, COMMIT, ROLLBACK, and SAVEPOINT

1. Introduction

Transaction control commands define the boundaries of atomic work units. BEGIN starts a transaction, COMMIT makes all changes permanent, ROLLBACK undoes all changes, and SAVEPOINT creates partial rollback points within a transaction. These commands are the primary interface for managing data integrity in application code.

2. Why It Matters

Without proper transaction boundaries, multi-step operations can leave the database in an inconsistent state. Applications that auto-commit every statement lose the ability to roll back related changes together. SAVEPOINTs enable sophisticated error recovery — rolling back only the failed sub-operation while keeping the rest of the transaction intact.

3. Real-World Analogy

Think of filling out a multi-page application form. BEGIN is picking up the pen, each page you fill is a SQL statement, SAVEPOINT is photocopying a completed page, COMMIT is submitting the form, and ROLLBACK is throwing it all away. If page 3 has a mistake, you can ROLLBACK TO SAVEPOINT (the page 2 photocopy) and redo just page 3 without losing pages 1-2.

4. How It Works

• BEGIN (or START TRANSACTION): Opens a new transaction block. All subsequent statements share the same snapshot and XID.
• COMMIT: Flushes WAL entries to disk and marks the XID as committed in pg_xact (commit log). Changes become visible to other transactions.
• ROLLBACK: Marks the XID as aborted. Modified tuples are ignored by other transactions via MVCC visibility rules.
• SAVEPOINT name: Creates a named restore point within the transaction. ROLLBACK TO SAVEPOINT name undoes changes after that point without aborting the whole transaction.
• RELEASE SAVEPOINT name: Destroys a savepoint, freeing resources.

5. Internal Architecture

PostgreSQL tracks transaction state in shared memory via the ProcArray. Each backend process holds its XID and snapshot. Savepoints are tracked in a stack within the backend's memory — each savepoint records the current subtransaction state and the set of modified tuples. On ROLLBACK TO SAVEPOINT, the backend marks tuples modified after the savepoint as aborted using their xmax system column.

6. Visual Explanation

The diagram shows a transaction timeline with BEGIN, three SQL statements, a SAVEPOINT after statement 2, an error on statement 3, ROLLBACK TO SAVEPOINT (preserving statements 1-2), and final COMMIT.

7. Practical Example

8. Common Mistakes

• Forgetting BEGIN: Without explicit BEGIN, each statement auto-commits independently, losing atomicity for multi-step operations.
• Continuing after an error without SAVEPOINT: In PostgreSQL, a failed statement inside a transaction puts the entire transaction in an "aborted" state — all subsequent statements fail until ROLLBACK.
• Nesting SAVEPOINTs too deeply: Each savepoint adds overhead to the subtransaction stack. Use them for error recovery, not as a general undo mechanism.
• Committing too late: Long-running transactions hold locks and bloat tables with dead tuples visible to MVCC.

9. Quick Quiz

10. Scenario-Based Challenge

Write a transaction that bulk-inserts 1000 rows from a staging table into a production table. For each row that fails a CHECK constraint, log the failure to an errors table and continue processing the remaining rows — without aborting the entire batch. Use SAVEPOINTs to isolate each row insert.

11. Debugging Exercise

12. Interview Questions

• Q: What is the difference between ROLLBACK and ROLLBACK TO SAVEPOINT?
  A: ROLLBACK aborts the entire transaction and undoes all changes. ROLLBACK TO SAVEPOINT undoes only changes made after the named savepoint, keeping the transaction alive and earlier changes intact.
• Q: Why does PostgreSQL require ROLLBACK after any error inside a transaction block?
  A: PostgreSQL marks the transaction as "aborted" to prevent partial inconsistency. Unlike some databases that auto-rollback just the failed statement, PostgreSQL enforces explicit error handling.

13. Production Considerations

• idle_in_transaction_session_timeout: Set this to kill forgotten transactions that hold locks indefinitely (e.g., 30 seconds).
• Statement timeout inside transactions: Use SET LOCAL statement_timeout = '5s' to prevent runaway queries within a transaction.
• Connection pooler interaction: PgBouncer in transaction mode assigns connections per-transaction. Ensure your application doesn't span a BEGIN/COMMIT across multiple pool checkouts.