Transaction Control in PostgreSQL
In PostgreSQL, the execution of transactional operations is governed by a few essential commands:
BEGIN TRANSACTIONCOMMITROLLBACK
These commands control the start, commit, and undo actions of transactions, respectively.
The BEGIN TRANSACTION Command
When managing transactions in PostgreSQL, the BEGIN TRANSACTION command is your starting point. You can start a transaction with this command, which is usually followed by a series of data manipulation operations such as INSERT, UPDATE, or DELETE.
When you initiate a transaction with the BEGIN or BEGIN TRANSACTION command, the system enters a transaction block, where all the subsequent database operations become a part of the transaction. This transaction persists until it encounters the next COMMIT or ROLLBACK command. The transaction will also automatically rollback if the database connection is closed or if an error occurs.
BEGIN;
or
BEGIN TRANSACTION;
The COMMIT Command
Once you have made the necessary changes in your transaction, the COMMIT command is used to save these changes to the database. It ensures all the modifications made in the transaction are stored and visible to other transactions. It also marks the end of the transaction block.
When you issue a COMMIT or END TRANSACTION command, all changes made to the database since the last COMMIT or ROLLBACK command are saved.
COMMIT;
or
END TRANSACTION;
The ROLLBACK Command
Sometimes, you may need to cancel a transaction due to an error or other reasons. In such cases, the ROLLBACK command is useful. This command undoes all the changes made by the transaction that have not already been saved to the database.
The ROLLBACK command only undoes transactions since the last COMMIT or ROLLBACK command was issued.
ROLLBACK;
Transactional Control Limitations
While transaction control commands in PostgreSQL offer extensive control over your database transactions, they do come with certain limitations.
Most notably, transaction control commands can only be used with data manipulation language (DML) commands such as INSERT, UPDATE, and DELETE. They are not applicable when executing data definition language (DDL) commands like CREATE TABLE or DROP TABLE. The reason behind this is that in PostgreSQL, DDL commands are automatically committed, meaning they cannot be rolled back.
Example Demonstrations
Consider we have a COMPANY table with the following records:
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
ROLLBACK
Now, let's start a transaction and delete records from the table having age = 25. We will then use the ROLLBACK command to undo all the changes.
postgres=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;
If you check the COMPANY table again, you'll find it still contains the following records:
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
This is because we have rolled back the transaction, effectively undoing the deletion operation we performed earlier.
COMMIT
Let's now start another transaction, delete records from the table having age = 25 again, but this time we use the COMMIT command to save all the changes.
postgres=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;
Upon checking the COMPANY table again, you'll see the following records:
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(5 rows)
References
Ryusei Kakujo
Focusing on data science for mobility
Bench Press 100kg!