How to execute Transactions in PostgreSQL?

What is a Transaction?

Transactions, like any other database, are a key component of PostgreSQL. A transaction is a sequence of one or more database operations that are executed as a single unit of work. These operations can be queries (e.g. SELECT, INSERT, UPDATE and DELETE) that modify the database’s state.

Need Help?

A transaction’s main purpose is to combine multiple statements into an atomic, all-or-nothing process. It ensures that either all operations within a transaction are fully completed, or none of them are executed at all. Concurrent transactions cannot see each other’s unfinished changes. Updates from ongoing transactions remain hidden until completion, at which point all changes become visible simultaneously.

Starting a Transaction

In PostgreSQL, every statement is actually treated as a transaction and is automatically started and committed if no explicit transaction commands are used. However, for executing multiple statements as a single transaction you must use the BEGIN command:

BEGIN;

Once the BEGIN command is executed, all database operations inside BEGIN and COMMIT command are considered as a transaction block.

Executing SQL Operations in a Transaction

Within a transaction, you can execute any SQL operations that you would typically perform in PostgreSQL, such as SELECT, INSERT, UPDATE and DELETE.

For example, let’s consider a simple scenario where we want to insert data into a table called “bank_records”:

BEGIN;

INSERT INTO bank_records (account_number, account_holder_name, account_type, balance) VALUES ('2345', 'John', 'Savings', 1500);

INSERT INTO bank_records (account_number, account_holder_name, account_type, balance) VALUES ('4512', 'Alice', 'Current', 2500); 

COMMIT;

In this scenario, the two INSERT statements are executed as part of the same transaction. If any of the INSERT operations fail due to any reason, the entire transaction will be rolled back, and no changes will be made to the database. These two insert statements can be executed without transactions which wouldn’t cause much problems, but the following statements when John transfers 1000 to Alice’s account must be executed in transaction because if one query succeeds and other fails, 1000 will be deducted from John’s account but won’t be added to Alice’s account.

BEGIN;

UPDATE bank_records SET balance = balance - 1000 WHERE account_number = '2345';

UPDATE bank_records SET balance = balance + 1000 WHERE account_number = '4512'; 

COMMIT;

Committing a Transaction

To make the changes performed within a transaction permanent, you need to commit the transaction using the COMMIT command:

COMMIT;

When the COMMIT command is executed, all the changes made during the transaction are applied to the database. The database locks are released, and changes made by the transaction are visible.

Rolling Back a Transaction

When you want to discard the changes made within a transaction and revert the database to its state before the transaction started, you can use the ROLLBACK command:

ROLLBACK;

The ROLLBACK command reverts all the operations performed within the current transaction, ensuring that no changes are applied to the database.

Savepoints

Savepoints are a useful feature in PostgreSQL which allow you to create intermediate stages within a transaction, which can be rolled back to independently without affecting the entire transaction. Savepoints provide control over the transaction in a more granular manner. This can be useful when you want to apply partial changes and handle errors gracefully.
To create a savepoint, use the SAVEPOINT command:

SAVEPOINT my_savepoint;

If you encounter an issue within the transaction, you can roll back to the savepoint:

ROLLBACK TO SAVEPOINT my_savepoint;

This will undo all operations made after the specified savepoint, while keeping the rest of the transaction intact.
Let’s look at a scenario where bank account records are being inserted inside a transaction using SAVEPOINT.

BEGIN;

INSERT INTO bank_records (account_number, account_holder_name, account_type, balance) VALUES ('1345', 'James', 'Savings', 3500);

SAVEPOINT savepoint1;

INSERT INTO bank_records (account_number, account_holder_name, account_type, balance) VALUES ('2122', 'Robert', 'Current', 1200);

ROLLBACK TO SAVEPOINT savepoint1;

INSERT INTO bank_records (account_number, account_holder_name, account_type, balance) VALUES ('2122', 'Robert', 'Saving', 9 000);

COMMIT;

In the example mentioned above, a SAVEPOINT has been created after inserting the bank account record of James. Now when the bank account record of Robert is being inserted and then it is realized that the information of Robert is incorrect, now instead of rolling back the entire transaction we can ROLLBACK to the SAVEPOINT, enter Robert’s record again and then COMMIT.

Conclusion

PostgreSQL transactions are essential for maintaining data integrity and consistency in the database. This blog discusses the basics of how to start, end and rollback a transaction, furthermore this blog also discusses how to control statements within a transaction in a more granular way. Utilizing transactions in PostgreSQL ensures data integrity when multiple queries need to be executed as one unit.

Blog Details