Imagine a student pays a university fee of Rs. 50,000.
The university’s database needs to perform two operations:
Both operations must be completed successfully.
What happens if the first operation succeeds but the second operation fails?
The database may contain incomplete or incorrect information.
To prevent such problems, PostgreSQL provides Transactions.
A transaction allows us to group multiple SQL operations into one logical unit of work.
A Transaction is a group of database operations that are treated as one unit.
Either all operations are completed successfully, or the changes are rolled back.
For example, a bank transfer involves:
Both operations must succeed together.
If one operation fails, the transaction can be cancelled.
Transactions help us:
PostgreSQL transactions follow four important properties called ACID.
Atomicity
All operations succeed, or none of their changes are committed.
Consistency
A transaction must preserve database rules and constraints.
Isolation
Concurrent transactions should not interfere in ways that violate the selected isolation level.
Durability
Once a transaction is committed, its changes are preserved, including across a database crash, subject to PostgreSQL’s durability configuration.
PostgreSQL provides three basic transaction commands.
| Command | Purpose |
|---|---|
BEGIN |
Starts a transaction |
COMMIT |
Saves transaction changes permanently |
ROLLBACK |
Cancels uncommitted changes |
Let’s understand each command with practical examples.
We will create a table named accounts.
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_holder VARCHAR(50),
balance NUMERIC(10, 2)
);
Insert sample records:
INSERT INTO accounts
VALUES
(1, 'Ali', 50000),
(2, 'Sara', 30000);
Display the records:
SELECT * FROM accounts;
Expected output:
| account_id | account_holder | balance |
|---|---|---|
| 1 | Ali | 50000.00 |
| 2 | Sara | 30000.00 |
The BEGIN command starts a new transaction.
Syntax:
BEGIN;
Example:
BEGIN;
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1;
We have deducted Rs. 5,000 from Ali’s account.
However, the transaction has not yet been committed.
The changes can still be rolled back.
The COMMIT command permanently saves the changes made during the current transaction.
Syntax:
COMMIT;
Example:
BEGIN;
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1;
COMMIT;
Check the updated records:
SELECT * FROM accounts;
Expected output:
| account_id | account_holder | balance |
|---|---|---|
| 1 | Ali | 45000.00 |
| 2 | Sara | 30000.00 |
The changes are now committed.
The ROLLBACK command cancels all uncommitted changes in the current transaction.
Syntax:
ROLLBACK;
Example:
BEGIN;
UPDATE accounts
SET balance = balance - 10000
WHERE account_id = 1;
ROLLBACK;
Now check the records:
SELECT * FROM accounts;
Expected output:
| account_id | account_holder | balance |
|---|---|---|
| 1 | Ali | 45000.00 |
| 2 | Sara | 30000.00 |
The balance remains Rs. 45,000 because the update was rolled back.
Remember: ROLLBACK only cancels changes that have not already been committed.
Let’s transfer Rs. 10,000 from Ali’s account to Sara’s account.
Two operations are required:
Both operations should succeed together.
BEGIN;
UPDATE accounts
SET balance = balance - 10000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 10000
WHERE account_id = 2;
COMMIT;
Check the account balances:
SELECT * FROM accounts;
Expected output:
| account_id | account_holder | balance |
|---|---|---|
| 1 | Ali | 35000.00 |
| 2 | Sara | 40000.00 |
The transaction successfully transferred Rs. 10,000.
Important: This example demonstrates the basic transaction concept. A production banking application should also validate balances, check that both accounts exist, and handle errors before committing.
Suppose we execute an invalid SQL statement inside a transaction.
BEGIN;
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1;
UPDATE accounts
SET unknown_column = 100
WHERE account_id = 2;
COMMIT;
The second UPDATE generates an error because unknown_column does not exist.
PostgreSQL marks the transaction as failed.
The final COMMIT will not save the earlier update.
We must execute:
ROLLBACK;
This returns the transaction to its state before BEGIN.
Sometimes we want to cancel only part of a transaction instead of cancelling the entire transaction.
PostgreSQL provides SAVEPOINT for this purpose.
A savepoint creates a point inside a transaction to which we can return.
Syntax:
SAVEPOINT savepoint_name;
To return to a savepoint:
ROLLBACK TO SAVEPOINT savepoint_name;
Example:
BEGIN;
UPDATE accounts
SET balance = balance - 2000
WHERE account_id = 1;
SAVEPOINT first_update;
UPDATE accounts
SET balance = balance + 2000
WHERE account_id = 2;
ROLLBACK TO SAVEPOINT first_update;
COMMIT;
Understand the operations:
Final result: Ali’s balance decreases by Rs. 2,000, while Sara’s balance remains unchanged.
After creating a savepoint, we can remove it using RELEASE SAVEPOINT.
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
SAVEPOINT my_savepoint;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;
RELEASE SAVEPOINT my_savepoint;
COMMIT;
The transaction is committed successfully.
Releasing a savepoint does not commit the transaction. It only removes that savepoint.
| Normal SQL Execution | Transaction |
|---|---|
| A statement normally runs in its own transaction when using autocommit | Multiple statements can be grouped together |
| Successful statement changes are committed automatically in autocommit mode | Changes remain uncommitted until COMMIT |
| Cannot roll back an already committed statement | Can roll back uncommitted changes |
| Suitable for simple operations | Suitable for related operations that must succeed together |
Note: PostgreSQL automatically executes statements in transactions. When using tools such as pgAdmin with autocommit enabled, each successful statement is committed automatically unless you explicitly start a transaction.
| Command | Description |
|---|---|
BEGIN |
Starts a transaction |
START TRANSACTION |
Alternative way to start a transaction |
COMMIT |
Saves changes |
ROLLBACK |
Cancels uncommitted changes |
SAVEPOINT |
Creates a recovery point |
ROLLBACK TO SAVEPOINT |
Returns to a recovery point |
RELEASE SAVEPOINT |
Removes a savepoint |
Use the accounts table created in this lesson.
Transaction workflow
BEGIN
Start transaction
Execute SQL statements
INSERT, UPDATE, DELETE
COMMIT
Save changes
ROLLBACK
Cancel changes
Remember:
Functions in PostgreSQL
You will learn how to create and execute reusable SQL functions, pass parameters, return values, and use built-in PostgreSQL functions.