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)
);

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);

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

BEGIN TRANSACTION;

Or

BEGIN WORK;

Or

BEGIN;

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);

From the current session, you can see the change by retrieving data from the accounts table:

SELECT
    id,
    name,
    balance
FROM
    accounts;

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;

Commit a transaction

To permanently apply the change to the database, you commit the transaction by using the COMMIT WORK statement:

COMMIT WORK;

or

COMMIT TRANSACTION;

or simply:

COMMIT;

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

SELECT
    id,
    name,
    balance
FROM
    accounts;

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;

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 undoes 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 won’t see the changes because they were 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.