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.

This implies that if the sender account transfers X amount, the receiver receives exactly X amount, neither more nor less.

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

  • Atomicity guarantees that the transaction is completed in an all-or-nothing manner.
  • Consistency ensures that changes to data written to the database are valid and adhere to predefined rules.
  • Isolation determines how the integrity of a transaction is visible to other transactions.
  • Durability ensures that transactions that have been committed are permanently stored in the database.

Setting up a sample table

Let’s create a new table called 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 CHECK(balance >= 0),
    PRIMARY KEY(id)
);Code language: SQL (Structured Query Language) (sql)

Begin a transaction

When you execute a statement, PostgreSQL implicitly wraps it in a transaction.

For example, when you execute the following INSERT statement, PostgreSQL immediately inserts a new row into the accounts table:

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

To start a transaction explicitly, you execute either one of the following statements:

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

Or

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

Or

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 retrieving data from the accounts table:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;Code language: SQL (Structured Query Language) (sql)
PostgreSQL Transaction - from current session

However, you will not see the change if you connect to the PostgreSQL server in a new session and execute the query above:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;Code language: SQL (Structured Query Language) (sql)
PostgreSQL Transaction - from another session

Commit a transaction

To permanently apply the change to the database, you 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)

Other sessions can view the change by retrieving data from the accounts table:

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

After executing the COMMIT statement, PostgreSQL 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)

Roll back a transaction

If you want to undo the changes to the database, you can use the ROLLBACK statement:

ROLLBACK;

Or more clear:

ROLLBACK TRANSACTION;

Or:

ROLLBACK WORK;

The ROLLBACK statement undos the changes that you made within the transaction.

For example, the following example uses the ROLLBACK statement to roll back the changes made to the account 1:

-- start a transaction
BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE id =  1;

-- rollback the changes
ROLLBACK;

If you retrieve data from the accounts table, you’ll won’t see the changes because it was rolled back.

SELECT * FROM accounts;

Output:

 id | name  | balance
----+-------+----------
  1 | Bob   | 10000.00
  2 | Alice | 10000.00
(2 rows)

In practice, you’ll use transactions in stored procedures in PostgreSQL and in the application code such as PHP, Java, and Python.

Summary

  • Use the BEGIN statement to explicitly start a transaction
  • Use the COMMIT statement to apply the changes permanently to the database.
  • Use the ROLLBACK statement to undo the changes made to the database during the transaction.
Was this tutorial helpful ?