Begin Transaction Commit in PostgreSQL

Introduction

This tutorial will discuss the transaction processes BEGIN and COMMIT on how they play a vital part in a transaction cycle.

Prerequisites

• Ensure that PostgreSQL server is properly installed, configured and running on the background.

For Linux and Windows systems you can download PostgreSQL here

What is a Transaction.

A unit of operation performed within a database is a transaction, these operations were performed in sequences and will be completed in a logical order manually or via automated database program. We can say transaction is the operations CREATE, UPDATE or DELETE that we perform against a specific table. It is essential that we know how to control the flow of the transaction to ensure the integrity of the data and be able to handle errors properly.

Transaction in PostgreSQL is ACID – atomic, consistent, isolated and durable. Now let’s discuss the four component that made up an ACID transaction.

• Atomic – This guarantees that every transaction will be completed in an all-or-noting passion.
• Consistent – This ensures that predefined rules for database operations will always be executed.
• Isolation – This will determine the on what manner does the integrity of the transaction is transparent to other database transaction.
• Durability – This ensures that committed transaction will be permanently stored.

BEGIN Transaction in PostgreSQL

In the previous section, we define what a transaction is and discuss why PostgreSQL transactions are ACID in nature. Before we dive into our demo, we have to create a sample dataset to work with. Let’s use the below statement to create a table named financial_details.

Then we put one record in there:

Now we have a table named financial_details with the following records.

Now let’s begin a simple transaction by inserting another record in our table.

In the above statement, we are not aware of what is happening in the background as to when it begins the transaction or when it ended and ultimately we don’t have any chance of undoing it.

Therefore, in order to control the flow of the transaction, we will be using the BEGIN TRANSACTION statement, see below statement.

Now let’s query our table if we are able to insert the record successfully, using a new session. So open up a new terminal then execute a new SELECT statement.

Output:

We can see that the values ‘risa’, 69000 was not stored in the database because we did not commit the records to the table. We will see that process in the upcoming

COMMIT Transaction in PostgreSQL

In the previous section we learned about the BEGIN transaction, now we will discuss what COMMIT transaction is in PostgreSQL and how to use it.

The command COMMIT command will save all the changes we invoked during the transaction to the current database.

The basic syntax of the commit command is as follows:

Taking the previous INSERT statement, let’s use the COMMIT command to store the record in the database permanently.

To verify, end your current session using \q command then create a new session by logging in to PostgreSQL server again and execute the SELECT command.

We can see now that commit did store the record in the database permanently.

Conclusion

In this tutorial, you have learned how to monitor and control PostgreSQL transactions using the PostgreSQL transactions BEGIN and COMMIT command.