The ability to manage transactions in different ways is one of the best things any database has. Whatever happens in the database, like inserting, updating, and deleting records, can be done safely inside transactions to maintain consistency. In this blog, you will get a clear idea of how to deal with begin, commit, savepoint, and rollback within a transaction.
Why transactions matter?
Transactions exist in PostgreSQL to ensure ACID properties. You may already know what ACID properties mean. Atomic is either all or nothing, which means if any one step fails, then it should cancel the entire transaction. Consistency means the data set should be correct before and after the transaction, which ensures we have an uncorrupted data set. Isolation means one transaction does not depend on the other, which means we should not get a mixed result. Durability, once we have completed a transaction, its changes should exist in the DB. These are important things we need to have to maintain the business logic and stay safe from corruption.
In postgreSQL we have the following important commands to manage transactions.
- BEGIN - This command is used to start a transaction. It can have some values along with BEGIN, will discuss this later in this blog.
- COMMIT - This completes a transaction, and all the changes within the transaction become permanent.
- ROLLBACK - This command aborts a transaction and will also discard any changes made within the transaction.
- SAVEPOINT - This creates a point within the transaction that can be used to discard changes up to that point alone without discarding the entire transaction.
These commands are closely related to PostgreSQL’s working of MVCC, snapshots, transaction IDs, and locks.
1. COMMIT — make changes permanent
COMMIT tells PostgreSQL to complete the current transaction and make all changes within it permanent. Once committed, these changes cannot be undone using a normal transaction rollback.
For example:
BEGIN;
INSERT INTO odoo_users(id, name) VALUES (101, 'John');
UPDATE user_data SET user_count = user_count + 1 WHERE module_id = 10;
COMMIT;
In this example, the INSERT operation and the UPDATE done within the BEGIN and COMMIT cannot be rolled back.After COMMIT, the changes made by the transaction are no longer discarded as part of a normal transaction rollback. A transaction that has not yet committed will not normally provide the changes within it to other transactions.
2. What is BEGIN?
BEGIN starts a transaction. Every command between BEGIN and COMMIT belongs to a transaction and instead of BEGIN we can also use START TRANSACTION.
Let’s see an example:
BEGIN;
UPDATE sale_order set amount = 0 WHERE state = 'cancel';
COMMIT;
Without an explicit transaction block, PostgreSQL normally operates in autocommit mode from the client's perspective. For example, statements executed without BEGIN and COMMIT are normally treated as individual transactions.
You might think that a BEGIN command assigns a transaction ID, but that is not the case. A transaction ID is assigned when the transaction performs its first write operation. The BEGIN command simply establishes a transaction context.
The transaction also gets a snapshot when required for consistent reads, depending on the transaction's isolation level and when the snapshot is acquired. PostgreSQL supports commonly used isolation levels such as Read Committed, Repeatable Read, and Serializable.
3. ROLLBACK - aborts the current transaction
With ROLLBACK, a transaction is aborted, which means all changes and executions within that transaction are discarded, and the state will be as it was before the transaction. For example, if you have a transaction like:
BEGIN;
INSERT INTO employees VALUES (101, 'Peter');
ROLLBACK;
Now, this transaction is discarded, and after this, if you run:
SELECT * FROM employees WHERE id = 101;
You will not get any rows as a result because the insert was aborted.
Now, Why Do We Need ROLLBACK?
The main purpose of ROLLBACK is to maintain atomicity.
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;
COMMIT;
Let’s consider a money transfer as an example: Suppose, due to some kind of timeout issues, the second operation failed. We don't need the first update to exist committed while the second one fails.
Instead, we can:
Debit account 1
Credit account 2
ERROR
ROLLBACK
Which means the transaction is discarded as a whole.
4. SAVEPOINT
We have SAVEPOINTS to reach back to a point, within a transaction, in case we found an issue, and we don't need the entire changes in the transaction to get discarded.
Suppose we are doing multiple operations in a transaction, like given in the example:
BEGIN;
INSERT INTO customers VALUES (...);
SAVEPOINT customer_2;
INSERT INTO customers VALUES (...);
-- Something goes wrong ROLLBACK TO SAVEPOINT customer_2;
INSERT INTO customers VALUES (...);
COMMIT;
The transaction can continue after the rollback to the savepoint, as shown here. This is useful when incase:
- A transaction contains many operations.
- One operation is allowed to fail. (No business logic error occurs if we do so)
- We want to recover from an error and continue.
- We want a partial rollback without aborting the whole transaction.
Understanding these commands and knowing their purpose is important if you are working on a postgresql database. BEGIN, COMMIT, ROLLBACK, and SAVEPOINT provide the foundation for controlling how database changes are grouped, completed, or undone.