PostgreSQL Transaction

Summary: in this tutorial, you will learn how to handle PostgreSQL transactions using the BEGIN, COMMIT, and ROLLBACK statements.

What is a database transaction

A database transaction is a single unit of work that consists of one or more operations.

A classical example of a transaction is a bank transfer from one account to another. A complete transaction must ensure a balance between the sender and receiver accounts. It means that if the sender account transfers X amount, the receiver receives X amount, no more or no less.

A PostgreSQL transaction is atomic, consistent, isolated, and durable. These properties are often referred to as ACID:

• Atomicity guarantees that the transaction completes in an all-or-nothing manner.
• Consistency ensures the change to data written to the database must be valid and follow predefined rules.
• Isolation determines how transaction integrity is visible to other transactions.
• Durability makes sure that transactions that have been committed will be stored in the database permanently.

Setting up a sample table

Let’s create a new table named accounts for the demonstration:

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(100) NOT NULL,
    balance DEC(15,2) NOT NULL,
    PRIMARY KEY(id)
);
Code language: SQL (Structured Query Language) (sql)

Begin a transaction

When you execute the following INSERT statement:

INSERT INTO accounts(name,balance)
VALUES('Bob',10000);
Code language: SQL (Structured Query Language) (sql)

PostgreSQL inserted a new row into the accounts table immediately. In this case, you do not know when the transaction begins and cannot intercept the modification such as rolling it back.

To start a transaction, you use the following statement:

BEGIN TRANSACTION;
Code language: SQL (Structured Query Language) (sql)

or

BEGIN WORK;
Code language: SQL (Structured Query Language) (sql)

or just:

BEGIN;
Code language: SQL (Structured Query Language) (sql)

For example, the following statements start a new transaction and insert a new account into the accounts table:

BEGIN;

INSERT INTO accounts(name,balance)
VALUES('Alice',10000);
Code language: SQL (Structured Query Language) (sql)

From the current session, you can see the change by querying the accounts table:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;
Code language: SQL (Structured Query Language) (sql)

However, if you start a new session and execute the query above, you will not see the change.

SELECT 
    id,
    name,
    balance
FROM 
    accounts;
Code language: SQL (Structured Query Language) (sql)

Commit a transaction

To make the change become visible to other sessions (or users) you need to commit the transaction by using the COMMIT WORK statement:

COMMIT WORK;
Code language: SQL (Structured Query Language) (sql)

or

COMMIT TRANSACTION;
Code language: SQL (Structured Query Language) (sql)

or simply:

COMMIT;
Code language: SQL (Structured Query Language) (sql)

The following COMMIT statement inserts Alice’s account to the accounts table:

COMMIT;
Code language: SQL (Structured Query Language) (sql)

From other sessions, you can view the change by querying the accounts table:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;
Code language: SQL (Structured Query Language) (sql)

After executing the COMMIT statement, PostgreSQL also guarantees that the change will be durable if a crash happens.

Put it all together.

-- start a transaction
BEGIN;

-- insert a new row into the accounts table
INSERT INTO accounts(name,balance)
VALUES('Alice',10000);

-- commit the change (or roll it back later)
COMMIT;
Code language: SQL (Structured Query Language) (sql)

PostgreSQL COMMIT: Bank account transfer example

In this demonstration, we will show you how to transfer 1000USD from Bob’s account to Alice’s account. We will use two sessions for viewing the change of each operation.

In the first session, start a new transaction:

BEGIN;
Code language: SQL (Structured Query Language) (sql)

and subtracting 1000USD from Bob’s account with id 1:

UPDATE accounts 
SET balance = balance - 1000
WHERE id = 1;
Code language: SQL (Structured Query Language) (sql)

In the second session, check the account balance of both accounts:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;
Code language: SQL (Structured Query Language) (sql)

Output:

As you can see, the change is not visible in other sessions.

Next, add the same amount (1000USD ) to Alice’s account:

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2; 
Code language: SQL (Structured Query Language) (sql)

This change also is not visible to the second session until we commit it:

COMMIT;
Code language: SQL (Structured Query Language) (sql)

Now, you can view the change from any session:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;
Code language: SQL (Structured Query Language) (sql)

Put it all together.

-- start a transaction
BEGIN;

-- deduct 1000 from account 1
UPDATE accounts 
SET balance = balance - 1000
WHERE id = 1;

-- add 1000 to account 2
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2; 

-- select the data from accounts
SELECT id, name, balance
FROM accounts;

-- commit the transaction
COMMIT;
Code language: SQL (Structured Query Language) (sql)

Rolling back a transaction

To roll back or undo the change of the current transaction, you use any of the following statement:

ROLLBACK WORK;
Code language: SQL (Structured Query Language) (sql)

or

ROLLBACK TRANSACTION;
Code language: SQL (Structured Query Language) (sql)

or in short:

ROLLBACK;
Code language: SQL (Structured Query Language) (sql)

Suppose, you want to transfer 1500USD from Bob’s account to Alice’s account. However, you accidentally send the money to Jack’s account instead of Alice’s. And you want to roll back the whole transaction.

First, add Jack’s account to the accounts table:

INSERT INTO accounts(name, balance)
VALUES('Jack',0);    
Code language: SQL (Structured Query Language) (sql)

Next, subtract an amount from Bob’s account:

BEGIN;

UPDATE accounts 
SET balance = balance - 1500
WHERE id = 1;
Code language: SQL (Structured Query Language) (sql)

Then, adding the same amount to Alice’s account:

UPDATE accounts
SET balance = balance + 1500
WHERE id = 3; 
Code language: SQL (Structured Query Language) (sql)

However, Alice’s account has id 2. So this was a mistake.

To undo the change, you execute the ROLLBACK statement:

ROLLBACK;
Code language: SQL (Structured Query Language) (sql)

Finally, check the balances of all accounts:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;    
Code language: SQL (Structured Query Language) (sql)

As shown clearly in the output, the account balances remain the same as they were before the transaction.

Put it all toegher.

-- begin the transaction
BEGIN;

-- deduct the amount from the account 1
UPDATE accounts 
SET balance = balance - 1500
WHERE id = 1;

-- add the amount from the account 3 (instead of 2)
UPDATE accounts
SET balance = balance + 1500
WHERE id = 3; 

-- roll back the transaction
ROLLBACK;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to manipulate PostgreSQL transactions via BEGIN, COMMIT, and ROLLBACK statements.