PostgreSQL Transactions: COMMIT, ROLLBACK and SAVEPOINT

Transactions are an important feature of PostgreSQL that help maintain data accuracy and consistency.

They allow multiple SQL statements to be executed as a single logical unit. If all operations are successful, we can save the changes using COMMIT. If something goes wrong, we can undo the changes using ROLLBACK.

Transactions are commonly used in banking systems, e-commerce applications, payment processing, and database management.

What Is a Transaction in PostgreSQL?

A transaction is a group of SQL operations executed as a single unit.

For example, imagine transferring ₹1,000 from one bank account to another.

Two operations are required:

Deduct ₹1,000 from the sender's account.
Add ₹1,000 to the receiver's account.

Both operations should succeed together. If one operation fails, the transaction should be rolled back to avoid incorrect account balances.

PostgreSQL provides commands such as BEGIN, COMMIT, ROLLBACK, and SAVEPOINT to manage transactions.

1. BEGIN – Starting a Transaction

The BEGIN command starts a transaction block.

Syntax:

BEGIN;

You can also use START TRANSACTION.

START TRANSACTION;

Both commands start a transaction in PostgreSQL.

After BEGIN, the SQL statements you execute belong to the same transaction until you issue COMMIT or ROLLBACK.

2. Create a Sample Accounts Table

Let's create a table to understand transactions with a practical banking example.

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_holder VARCHAR(100) NOT NULL,
    balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);

Insert sample records:

INSERT INTO accounts
VALUES
(1, 'Alice', 5000),
(2, 'Bob', 3000),
(3, 'Charlie', 2000);

Display the account records:

SELECT *
FROM accounts;

The table contains three accounts with their respective balances.

3. COMMIT – Saving Changes

COMMIT permanently saves the changes made during the current transaction.

Suppose Alice wants to transfer ₹1,000 to Bob.

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

COMMIT;

Check the updated records:

SELECT *
FROM accounts;

Explanation:

BEGIN starts the transaction.
The first UPDATE deducts ₹1,000 from Alice.
The second UPDATE adds ₹1,000 to Bob.
COMMIT saves both changes.

Both operations are committed together.

4. ROLLBACK – Undoing Changes

ROLLBACK cancels the changes made during the current transaction that have not been committed.

Suppose we accidentally update Charlie's balance.

BEGIN;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 3;

ROLLBACK;

Check the account balance:

SELECT *
FROM accounts
WHERE account_id = 3;

The balance remains unchanged because the transaction was rolled back.

Important: ROLLBACK only undoes changes in the current uncommitted transaction. It cannot reverse changes that were already committed.

5. SAVEPOINT – Creating a Checkpoint

A SAVEPOINT creates a checkpoint inside a transaction.

It allows you to roll back selected operations without cancelling the entire transaction.

Syntax:

SAVEPOINT savepoint_name;

Example:

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

SAVEPOINT after_debit;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

ROLLBACK TO SAVEPOINT after_debit;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 3;

COMMIT;

Explanation:

BEGIN starts the transaction.
₹500 is deducted from Alice's account.
SAVEPOINT creates a checkpoint.
₹500 is added to Bob's account.
ROLLBACK TO SAVEPOINT undoes the update made to Bob's account.
₹500 is then added to Charlie's account.
COMMIT saves the remaining changes.

The debit from Alice and the credit to Charlie are committed, while Bob's update is cancelled.

6. ROLLBACK TO SAVEPOINT

ROLLBACK TO SAVEPOINT cancels only the changes made after the specified savepoint.

Example:

BEGIN;

INSERT INTO accounts
VALUES (4, 'David', 4000);

SAVEPOINT account_created;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 4;

ROLLBACK TO SAVEPOINT account_created;

COMMIT;

Explanation:

The new account for David is created.
A savepoint is established.
The balance is increased by ₹1,000.
ROLLBACK TO SAVEPOINT undoes the balance update.
COMMIT saves the account creation.

David's account remains with a balance of ₹4,000.

7. RELEASE SAVEPOINT

RELEASE SAVEPOINT removes a previously created savepoint when you no longer need to roll back to it.

Example:

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE account_id = 1;

SAVEPOINT balance_checkpoint;

UPDATE accounts
SET balance = balance + 200
WHERE account_id = 2;

RELEASE SAVEPOINT balance_checkpoint;

COMMIT;

Explanation:

A transaction begins.
Alice's balance is reduced by ₹200.
A savepoint is created.
Bob's balance is increased by ₹200.
RELEASE SAVEPOINT removes the checkpoint.
COMMIT saves the transaction.

Releasing a savepoint does not undo the changes made after it.

8. Understanding ACID Properties

Transactions follow four important properties known as ACID.

Atomicity

All operations in a transaction succeed together or are rolled back together.

Consistency

A transaction should maintain database rules and constraints, leaving the database in a valid state.

Isolation

Concurrent transactions should not interfere with each other's intermediate, uncommitted changes.

Durability

Once a transaction is committed, its changes are preserved even after a database failure, subject to the database's durability configuration.

These properties help maintain reliable database operations.

9. Autocommit in PostgreSQL

PostgreSQL executes each SQL statement in its own transaction when you do not explicitly start a transaction block.

This behavior is commonly called autocommit.

For example:

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 1;

When this statement executes successfully outside an explicit transaction block, PostgreSQL commits it automatically.

To group multiple statements into one transaction, use BEGIN and finish with COMMIT or ROLLBACK.

Some database tools and application libraries may manage transactions automatically, so their behavior can depend on the client configuration.

10. COMMIT vs ROLLBACK vs SAVEPOINT

Command	Purpose
BEGIN	Starts a transaction
COMMIT	Saves transaction changes
ROLLBACK	Cancels uncommitted transaction changes
SAVEPOINT	Creates a checkpoint
ROLLBACK TO SAVEPOINT	Undoes changes after a checkpoint
RELEASE SAVEPOINT	Removes a checkpoint

11. Real-World Applications of Transactions

Transactions are used in several applications.

Banking Applications:

Money transfers between accounts.
Deposits and withdrawals.
Updating account balances.

E-Commerce Applications:

Placing customer orders.
Updating product stock.
Processing payments.

Employee Management Systems:

Updating employee records.
Moving employees between departments.
Maintaining related database records.

Payment Processing:

Recording payment details.
Updating order status.
Maintaining transaction history.

Common Mistakes to Avoid

Forgetting to use COMMIT after completing a transaction.
Using ROLLBACK after COMMIT and expecting already committed changes to be undone.
Creating a SAVEPOINT outside a transaction block.
Assuming RELEASE SAVEPOINT undoes changes.
Forgetting that ROLLBACK TO SAVEPOINT keeps changes made before the savepoint.
Not checking transaction status after a SQL error.

Important: If a statement fails inside a PostgreSQL transaction, the transaction enters an aborted state. You must roll back the transaction or roll back to a valid savepoint before continuing.

Practice Questions

What is a transaction in PostgreSQL?
Write a query to start a transaction using BEGIN.
Create a transaction that updates two different records and commits the changes.
Write a transaction that updates a record and then rolls back the changes.
Create a SAVEPOINT inside a transaction.
Use ROLLBACK TO SAVEPOINT to undo selected changes.
Use RELEASE SAVEPOINT inside a transaction.
Explain the difference between COMMIT and ROLLBACK.
What are the four ACID properties?
Explain how transactions are used in a banking application.

Interview Questions

1. What is a transaction in PostgreSQL?

A transaction is a group of SQL operations executed as a single logical unit.

2. What is the purpose of BEGIN?

BEGIN starts a transaction block.

3. What does COMMIT do?

COMMIT saves all changes made during the current transaction.

4. What does ROLLBACK do?

ROLLBACK cancels all uncommitted changes made during the current transaction.

5. What is a SAVEPOINT?

A SAVEPOINT creates a checkpoint inside a transaction, allowing selected operations to be rolled back.

6. What is the difference between ROLLBACK and ROLLBACK TO SAVEPOINT?

ROLLBACK cancels the entire current transaction, whereas ROLLBACK TO SAVEPOINT cancels only the changes made after a specified savepoint.

7. What are ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability.

8. Can we create multiple savepoints in one transaction?

Yes. PostgreSQL supports multiple savepoints within a transaction.

9. What is autocommit?

Autocommit means that each successful SQL statement is committed automatically when it is executed outside an explicit transaction block.

10. Can ROLLBACK undo committed changes?

No. ROLLBACK cannot undo changes that have already been committed.

Conclusion

PostgreSQL transactions help maintain data consistency and reliability when multiple SQL operations need to be performed together.

Understanding BEGIN, COMMIT, ROLLBACK, SAVEPOINT, and ACID properties is essential for working with real-world databases.

These concepts are especially important for SQL Developers and Data Analysts who work with business data, financial records, and transactional systems.

Search This Blog

Tech Courses & Job Updates