PostgreSQL - Commit

PostgreSQL: Managing Transactions with COMMIT, ROLLBACK, and SAVEPOINT

Transactions are a fundamental concept in relational databases, ensuring data integrity. They allow you to group a series of SQL statements into a single, all-or-nothing operation. This is guaranteed by the ACID properties (Atomicity, Consistency, Isolation, Durability).

Autocommit vs. Explicit Transactions

By default, PostgreSQL operates in autocommit mode. This means every single SQL statement you execute (like an INSERT, UPDATE, or DELETE) is treated as its own transaction and is automatically committed upon successful completion. This is simple for single operations.

For multi-step operations that must succeed or fail as a single unit, you must use an explicit transaction block. You start it with BEGIN and end it with either COMMIT or ROLLBACK.

The Core Transaction Commands

BEGIN: Starting a Transaction

The BEGIN (or START TRANSACTION) command starts a new transaction block. Any statements executed after BEGIN are part of this transaction and are not visible to other users until you commit.

COMMIT: Saving Your Changes

The COMMIT command permanently saves all changes made within the current transaction. Once committed, the changes are durable and cannot be undone with a ROLLBACK.

COMMIT [WORK | TRANSACTION]; -- WORK and TRANSACTION are optional keywords.

ROLLBACK: Undoing Your Changes

The ROLLBACK command discards all changes made within the current transaction, returning the database to the state it was in before the transaction began.

Practical Example: A Bank Transfer

A bank transfer is the classic example for transactions. Transferring $100 from Account A to Account B requires two UPDATE statements. Both must succeed, or both must fail. If the debit succeeds but the credit fails, money is lost!

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    balance NUMERIC(10, 2) NOT NULL
);

INSERT INTO accounts (id, name, balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00);

Successful Transaction

BEGIN;

-- 1. Debit $100 from Alice's account
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 2. Credit $100 to Bob's account
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT; -- Both changes are now saved permanently.

Failed Transaction (using ROLLBACK)

Imagine we realize we made a mistake before committing.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 99; -- Error: account 99 does not exist!

-- Let's say we notice the error. We can manually undo everything.
ROLLBACK; -- All changes within this transaction are discarded. Alice's balance is back to 1000.

Advanced Control: SAVEPOINT

For long, complex transactions, you can set SAVEPOINTs. This allows you to roll back to a specific point within the transaction without discarding the entire thing.

BEGIN;

INSERT INTO accounts (id, name, balance) VALUES (3, 'Charlie', 200.00);
SAVEPOINT new_user_created;

-- Now, attempt a risky operation
UPDATE accounts SET balance = balance - 5000 WHERE id = 3; -- Fails check constraint (not shown)

-- An error occurred. Instead of a full rollback, go back to the savepoint.
ROLLBACK TO SAVEPOINT new_user_created;

-- Charlie's account still exists. Now we can try a different operation.
UPDATE accounts SET balance = balance - 50 WHERE id = 3;

COMMIT; -- Commits the creation of Charlie and the final update.

Key Takeaways and Best Practices

• Atomicity is Key: Use transactions for any operation involving multiple related steps.
• Application Logic: Most application frameworks and database libraries provide helpers to manage transactions, often automatically rolling back if an error or exception occurs in your code.
• DDL is Transactional: A unique feature of PostgreSQL is that Data Definition Language (DDL) commands like CREATE TABLE and ALTER TABLE can be included in a transaction block and rolled back. This is incredibly useful for complex schema migrations.
• Keep Transactions Short: Long-running transactions can hold locks on tables, blocking other users. Design your applications to keep transactions as brief as possible.