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’screate a new table calledaccounts for the demonstration:

DROP TABLE IF EXISTS accounts;CREATE TABLE accounts (    idINT GENERATED BY DEFAULT AS IDENTITY,    name VARCHAR(100)NOT NULL,    balanceDEC(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 followingINSERT statement, PostgreSQL immediately inserts a new row into theaccounts 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 theaccounts table:

BEGIN;INSERT INTO accounts(name,balance)VALUES('Alice',10000);

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

SELECT    id,    name,    balanceFROM    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,    balanceFROM    accounts;

Commit a transaction

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

COMMIT WORK;

or

COMMIT TRANSACTION;

or simply:

COMMIT;

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

SELECT    id,    name,    balanceFROM    accounts;

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

Put it all together.

-- start a transactionBEGIN;-- insert a new row into the accounts tableINSERT 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;

TheROLLBACK statement undos the changes that you made within the transaction.

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

-- start a transactionBEGIN;UPDATE accountsSET balance = balance - 1000WHERE id =  1;-- rollback the changesROLLBACK;

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 instored procedures in PostgreSQL and in the application code such asPHP,Java, andPython.

Summary

• Use theBEGIN statement to explicitly start a transaction
• Use theCOMMIT statement to apply the changes permanently to the database.
• Use theROLLBACK statement to undo the changes made to the database during the transaction.

Last updated onFebruary 13, 2024

On this page

• What is a database transaction?
• Setting up a sample table
• Begin a transaction
• Commit a transaction
• Roll back a transaction
• Summary