PostgreSQL Transactions

The Database Transactions are the fundamental concepts in the database that include multiple operations such as insert, update, and delete on the database in a logical order. The transaction contains multiple steps in a single operation that behave like all operations or no operation. If there are any failure occurs in any of the logical steps then none of the steps affect any change in the database. The Database Operations that are satisfying the ACID(atomicity, consistency, isolation, durability) properties are called the transactions.

The Example of a Database Transaction is performing insert, update, and delete operations on a table and making sure that all these operations are getting successful without any error.

The Database Transactions should follow the below ACID properties.

Database Transactions ACID Characteristics

The characteristics of ACID properties that were defined by Andreas Reuter and Theo Härder are as below.


1. Atomicity

The Atomicity in a transaction guarantees that each transaction will be taken as a single unit of task that will be completed or failed. If the transaction is complete then the changes will be applied to the database and if any error occurs then the database will be left unchanged. The Atomicity will be applied in each condition that includes, a power cut issue, hardware issue, or any system crash issue. An example of Atomicity is transferring money from Bank account A to Bank account B that will contain only two operations, the first operation is withdrawing money from Bank A and the second operation is transferring money to Bank B. In case any of these two operations fails then money will not be debited nor credited.


2. Consistency

Consistency ensures that any transaction changes that are written on the database are valid and have followed all rules of constraints, triggers, and cascade. For example, if there is a successful insert, update, or delete operation, and any read operation request is made then the latest data should be available to the user.


3. Isolation

The Isolation ensures that concurrent operations can be performed on the database. The concurrent operations include multiple operations such as reading and writing a table at the same time and not hampering each other operations. For example, if there are two users A and B, now user A is performing read operation on table emp and getting the required data and user B is updating the same table emp and able to do so. This is achieved because of the Isolation property.


4. Durability

The Durability ensures that the transaction which is committed in the database is stored permanently even if there is any hardware issue or system failure issue occurs. Durability guarantees that the data is stored on disk and available.


PostgreSQL Transaction Control Command

The following are the three PostgreSQL Transaction Control Command that is used to control the transactions. A transaction contains the Insert, Update and Delete operation. We should not use the Create SQL because those SQLs are auto-commit.

  • BEGIN TRANSACTION
  • COMMIT (END TRANSACTION)
  • ROLLBACK

Let us see each command in detail in the following section.


1. BEGIN TRANSACTION

The BEGIN TRANSACTION command is used to start the execution of the command in a single transaction unless there is a commit or rollback command submitted. PostgreSQL executes commands in auto-commit mode by default if the command is executed successfully if the command is failed then the rollback is applied. Executing statements in a transaction block is very quick because the CPU and disk activity required by the transaction block is significant.

The syntax of BEGIN TRANSACTION command is as below.

postgres=# BEGIN TRANSACTION;

OR

postgres=# BEGIN;


2. COMMIT (END TRANSACTION)

The COMMIT command is used to save the current transaction on the disk permanently. Once the transaction is committed it is visible to all users and it ensures that in case of a system crash also the data will be durable.

The syntax of the COMMIT command is as below.

postgres=# COMMIT;


3. ROLLBACK

The ROLLBACK aborts the current transaction and brings the database to it its previous committed state. Any changes made to the database are discarded.

The syntax of the ROLLBACK command is as below.

postgres=# ROLLBACK;



PostgreSQL Transactions Example

Now let us see an example of a Database Transaction. We have the table name PRODUCTS_DETAIL that contains the following records. In the first example, we will delete a row from this table in the Begin Transaction block and run the rollback to see the changes, and in the second example, we will delete the row from the same table and run the commit command to see the changes.

Let us run the delete operation on the table PRODUCTS_DETAIL post that we will supply the rollback command.


Command:

postgres=# SELECT * FROM products_detail;
postgres=#  BEGIN;
postgres=# DELETE FROM products_detail WHERE product_number=101;
postgres=# ROLLBACK;
postgres=# SELECT * FROM products_detail;


Output:

In the following output, we can see that the changes were aborted after running the rollback command and the data is still there.


postgresql transaction example

Now let us run the same delete operation on the table PRODUCTS_DETAIL and this time we will supply the commit command.


Command:

postgres=# SELECT * FROM products_detail;
postgres=#  BEGIN;
postgres=# DELETE FROM products_detail WHERE product_number=101;
postgres=# COMMIT;
postgres=# SELECT * FROM products_detail;


From the below output, we can see that the row has been deleted where the PRODUCT_NUMBER=101.


postgresql transaction example with commit