PostgreSQL – Transactions

The Transaction is not a new word we are hearing. We heard that word many times like “Cash Transaction”. Banks usually deal with cash i.e sending or receiving cash, hence we coin the term as a cash transaction. So simply transaction is a unit of work. In this article, we are going to learn about transactions in the PostgreSQL database language. 

Transactions are important in any database language, whenever we want to add, delete and update then transactions are used for keeping the integrity of data and several other reasons. Even without the transactions we can add, delete and update the database but there are so high chances to data gets corrected due to loss of data integrity.

Properties of Transaction

Now let’s see the ACID properties of a transaction:

• Atomicity – This property ensures that all the transactions are complete. It follows all or none property i.e the transaction should not be partially completed.
• Consistency – This property ensures that all the transactions are consistent i.e after committing the transaction those changes are properly updated in the database or not.
• Isolation – When two transactions are running then both the transactions will have their own privacy i.e one transaction won’t disturb another transaction.
• Durability – This property ensures that even at the time of system failures the committed data in database is secure i.e permanently.

Commands in a Transaction

There are three main commands in a transaction block. They are:

1. BEGIN
2. COMMIT
3. ROLLBACK

General Syntax:

BEGIN;

// set of statements

[COMMIT | ROLLBACK];

Now we will understand the importance of each and every transaction control command, for that, we have to set up a table in the database first.

CREATE TABLE BankStatements (
    customer_id serial PRIMARY KEY,
    full_name VARCHAR NOT NULL,
    balance INT
);

As the database schema is ready now we will insert some values in it.

INSERT INTO BankStatements (
    customer_id ,
    full_name,
    balance
)
VALUES
    (1, 'Sekhar rao', 1000),
    (2, 'Abishek Yadav', 500),
    (3, 'Srinivas Goud', 1000);

1. BEGIN

BEGIN command is used to initiate a transaction. To start a transaction we should give BEGIN command at first if we don’t give it like that then the database cant able recognizes the transaction.

Example 1

BEGIN;

 INSERT INTO BankStatements (
     customer_id,
    full_name,
        balance

)
    VALUES(
       4, 'Priya chetri', 500
        )
;
        
COMMIT;

Output

2. COMMIT

COMMIT command is used to save changes and reflect them in the database whenever we display the required data. For suppose we updated data in the database but we didn’t give COMMIT then the changes are not reflected in the database. To save the changes done in a transaction, we should COMMIT that transaction for sure.

Example 2

BEGIN;

    UPDATE BankStatements
        SET balance = balance - 500
        WHERE 
        customer_id = 1;
      

    SELECT customer_id, full_name, balance
        FROM BankStatements;
      
    UPDATE BankStatements
        SET balance = balance + 500
        WHERE 
        customer_id = 2;
 
      
COMMIT;


SELECT customer_id, full_name, balance
    FROM BankStatements;
    

Output:

3. ROLLBACK

ROLLBACK command is used to undo the changes done in transactions. As we know transactions in database languages are used for purpose of large computations, for example in banks. For suppose, the employee of the bank incremented the balance record of the wrong person mistakenly then he can simply rollback and can go to the previous state.

Example 3

BEGIN;

    DELETE FROM BankStatements
        WHERE 
        customer_id = 1;
      

    SELECT customer_id, full_name, balance
        FROM BankStatements;
      

ROLLBACK;


SELECT customer_id, full_name, balance
    FROM BankStatements;

Output: