How BEGIN, COMMIT, and ROLLBACK Combine Multiple Changes into a Single Operation in PostgreSQL

Introduction to Transactions in PostgreSQL

Transactions are fundamental to reliable database operations. They allow multiple SQL statements to be grouped into a single logical unit that executes as 'all or nothing'. This is critical for maintaining data integrity, especially in systems where errors could lead to inconsistent states, such as banking transfers.

PostgreSQL implements transactions according to ACID principles: atomicity, consistency, isolation, and durability. As stated in the documentation, 'a transaction groups several steps into one indivisible operation' - if a failure occurs, no intermediate changes affect the database.

Syntax of BEGIN, COMMIT, and ROLLBACK

To explicitly manage a transaction, three commands are used: BEGIN, COMMIT, and ROLLBACK. BEGIN starts a transaction block. All subsequent operations are executed within this transaction. COMMIT makes all changes permanent. ROLLBACK cancels all changes made since BEGIN.

Example: transferring $100 from Alice to Bob is performed as a single transaction:

The illustrative transfer assumes an accounts table with a unique name and a numeric balance, with existing Alice and Bob rows. The savepoint example additionally assumes a Wally row. Run the statements in a controlled demonstration database. In application code, verify affected row counts, sufficient funds and required constraints; a successful COMMIT alone does not prove that the intended business transfer occurred.

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
COMMIT;

Atomicity of Changes

Atomicity means a transaction either fully succeeds or is completely rolled back. Intermediate states are not preserved. For example, if funds are deducted from Alice’s account but a failure occurs before crediting Bob, the entire transaction is rolled back, restoring Alice’s balance.

WAL records must reach durable storage before the corresponding changed data pages are written. Data pages may be written before COMMIT; atomicity does not mean changes wait in memory until commit. Crash recovery uses the log, while visibility rules prevent other sessions from seeing uncommitted table changes.

Automatic Transactions by Default

Outside an explicit transaction block, PostgreSQL executes each statement in its own implicit transaction. Client libraries may start a transaction automatically or expose an autocommit option, so check the connection settings before assuming that two statements are independent.

This mode is convenient for simple operations, but when multiple commands must execute consistently, explicit BEGIN and COMMIT are required to avoid partial application of changes.

Error Handling: Rolling Back Changes

If a condition arises during a transaction that invalidates it (e.g., negative balance), ROLLBACK can be issued. All changes made since the start of the transaction are undone.

For instance, if after deducting funds from Alice, her balance becomes negative, the transaction can be rolled back, restoring the original state of the database.

Visibility of Changes to Other Transactions

Changes made within a transaction are not visible to other sessions until COMMIT. This ensures isolation. Other users continue to see the previous state of the tables.

Only after COMMIT do all changes become visible simultaneously, preventing scenarios where one part of an operation (e.g., deduction) is visible while another (e.g., deposit) is not.

Transaction Isolation Levels

The SQL names are Read Uncommitted, Read Committed, Repeatable Read and Serializable. PostgreSQL treats Read Uncommitted like Read Committed, so the four names provide three distinct behaviours. Read Committed is the usual default; a session or database configuration can change it.

Read Committed allows viewing only data committed before the query starts. Repeatable Read provides a consistent snapshot at the transaction start. Serializable simulates serial execution but may require handling serialization errors.

Using Savepoints for Partial Rollback

SAVEPOINT allows creating a point within a transaction to which you can roll back without canceling the entire transaction. This is useful in complex logic where only part of the operation needs to be undone.

For example, if after transferring money to Bob it is discovered that Wally should have received it, the transaction can roll back to a savepoint and redirect the funds.

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Wally';
COMMIT;