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 which may consist of one or more operations.
A classical example of a transaction is a transfer from one bank account to another. A complete transaction must ensure subtracting an amount from the sender’s account and adding that same amount to the receiver’s account.
A transaction in PostgreSQL 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 which have been committed will be stored permanently.
Setting up a sample table
Let’s create a new table named accounts for the demonstration:
1 2 3 4 5 6 | CREATE TABLE accounts ( id INT GENERATED BY DEFAULT AS IDENTITY, name VARCHAR(100) NOT NULL, balance DEC(15,2) NOT NULL, PRIMARY KEY(id) ); |
Beginning a transaction
When you execute the following INSERT statement:
1 2 | INSERT INTO accounts(name,balance) VALUES('Bob',10000); |
PostgreSQL inserted a new row into the accounts table immediately. In this case, you did not know when the transaction began and had no chance to intercept the change such as undoing it.
To start a transaction, you use the following statement:
1 | BEGIN TRANSACTION; |
or
1 | BEGIN WORK; |
or just:
1 | BEGIN; |
For example, the following statements start a new transaction and insert a new account into the accounts table:
1 2 3 4 | BEGIN; INSERT INTO accounts(name,balance) VALUES('Alice',10000); |
From the current session, you can see the change by querying the accounts table:
1 2 3 4 5 6 | SELECT id, name, balance FROM accounts; |
However, if you start a new session and execute the query above, you will not see the change.
1 2 3 4 5 6 | SELECT id, name, balance FROM accounts; |
Committing a transaction
To make the change become visible to other sessions (or users) you need to commit the transaction by using the following statement:
1 | COMMIT WORK; |
or
1 | COMMIT TRANSACTION; |
or simply:
1 | COMMIT; |
Let’s execute the COMMIT statement to insert Alice’s account to the accounts table:
1 | COMMIT; |
From any transaction, you can view this change by querying the accounts table:
1 2 3 4 5 6 | SELECT id, name, balance FROM accounts; |
After executing the COMMIT statement, PostgreSQL also guarantees that the change will be durable if a crash happens.
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, we start a new transaction:
1 | BEGIN; |
and subtracting 1000USD from Bob’s account with id 1:
1 2 3 | UPDATE accounts SET balance = balance - 1000 WHERE id = 1; |
In the second session, we check the account balance of both accounts:
1 2 3 4 5 6 | SELECT id, name, balance FROM accounts; |
Here is the output:
As you can see, the change is not visible in other sessions.
Next, we add the same amount (1000USD ) to Alice’s account:
1 2 3 | UPDATE accounts SET balance = balance + 1000 WHERE id = 2; |
This change also is not visible to the second session until we commit it:
1 | COMMIT; |
Now, you can view the change from any session:
1 2 3 4 5 6 | SELECT id, name, balance FROM accounts; |
Rolling back a transaction
To roll back or undo the change of the current transaction, you use any of the following statement:
1 | ROLLBACK WORK; |
or
1 | ROLLBACK TRANSACTION; |
or in short:
1 | ROLLBACK; |
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 Alice’s. And you want to rollback the whole transaction.
First, add Jack’s account to the accounts table:
1 2 | INSERT INTO accounts(name, balance) VALUES('Jack',0); |
Next, subtract an amount from Bob’s account:
1 2 3 4 5 | BEGIN; UPDATE accounts SET balance = balance - 1500 WHERE id = 1; |
Then, adding the same amount to Alice’s account:
1 2 3 | UPDATE accounts SET balance = balance + 1500 WHERE id = 3; |
However, Alice’s account has id 2. So this was a mistake.
To undo the change, you execute the ROLLBACK statement:
1 | ROLLBACK; |
Finally, check the balances of all accounts:
1 2 3 4 5 6 | SELECT id, name, balance FROM accounts; |
As shown clearly in the output, the account balances remain the same as they were before the transaction.
In this tutorial, you have learned how to manipulate PostgreSQL transactions via BEGIN, COMMIT, and ROLLBACK statements.