Transaction
-
A PostgreSQL transaction 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 that have been committed will be stored in the database permanently.
-
Starting a transaction:
BEGIN TRANSACTION;
BEGIN WORK;
BEGIN;
- Commit a transaction:
COMMIT TRANSACTION;
COMMIT WORK;
COMMIT;
- Rollback a transaction:
ROLLBACK TRANSACTION;
ROLLBACK WORK;
ROLLBACK;
- Savepoint : define a new savepoint within the current transaction
SAVEPOINT savepoint_name;
Example
mydb=# select * from product order by id;
id | name | price | country
----+----------------------------------------------------+--------+---------------------------
1 | Iphone | 10000 | USA
2 | Realme | 1000 | INDIA
3 | Vivo | 2000 | CHINA
4 | CG | 12000 | NEPAL
5 | Tesla | 11500 | USA
6 | Jio | 8000 | INDIA
7 | SAMSUNG | 120000 | KOREA
(7 rows)
mydb=# Begin transaction;
BEGIN
mydb=*# Insert into product Values (8, 'Apple', 120000, 'USA');
INSERT 0 1
mydb=*# SAVEPOINT S1;
SAVEPOINT
mydb=*# Insert into product Values (9, 'Banana', 130000, 'USA');
INSERT 0 1
mydb=*# Rollback to s1;
ROLLBACK
mydb=*# Commit transaction;
COMMIT
mydb=# select * from product order by id;
id | name | price | country
----+----------------------------------------------------+--------+---------------------------
1 | Iphone | 10000 | USA
2 | Realme | 1000 | INDIA
3 | Vivo | 2000 | CHINA
4 | CG | 12000 | NEPAL
5 | Tesla | 11500 | USA
6 | Jio | 8000 | INDIA
7 | SAMSUNG | 120000 | KOREA
8 | Apple | 120000 | USA
(8 rows)