Transaction in PostgreSQL | ROLLBACK | SAVEPOINT
The transaction is defined as a single unit of task that can contain multiple operations. A transaction only marks completed when all of its operations are performed properly. It rollbacked when any of its operations fail at any point.
A transaction must hold the properties of atomicity, consistency, durability, and isolation which are commonly known as ACID properties.
Atomicity: A transaction is a single unit of a task. It should either executed completely or none. None mean, it can not be in the partially executed state.
Consistency: It ensures the data change after the transaction is in a valid state and following the defined rules.
Durability: It means data changes after the transaction completion will be persistent even if the system fails.
Isolation: A transaction should be carried out in isolation from other transactions. Other executing transactions should not affect it.
In PostgreSQL transaction syntax is
[ BEGIN TRANSACTION | BEGIN ]
defined operations
[ COMMIT TRANSACTION | COMMIT ]To start a transaction you can use BEGIN TRANSACTION or just BEGIN and to commit the transaction either useCOMMIT TRANSACTION or COMMIT.
Let’s suppose two users (David and John)are playing a mobile game and David win the game. Now John gems will be transferred to David. Let’s see how gems will be transferred through transactions.
Create a user table in the database,
DROP TABLE IF EXISTS users;CREATE TABLE users(
id INT GENERATED BY DEFAULT AS IDENTITY,
name VARCHAR(50),
gems DEC (10),
PRIMARY KEY (id));
Populate user table with the default number of gems,
INSERT INTO users (name, gems) VALUES ('David', 1000);
INSERT INTO users (name, gems) VALUES ('John', 1000); To transfer gems transaction will be written as
BEGIN;UPDATE users
SET gems = gems — 100
WHERE id=2;UPDATE users
SET gems = gems+100
WHERE id=1;COMMIT;
Now to get the updated record just write the SELECT statement, and you will see David owns 1100 gems and John has 900 gems.
SELECT * FROM users;To undo the changes made by the transaction, use one of the following,
[ ROLLBACK TRANSACTION | ROLLBACK ];Let's roll back a transaction in the following query,
BEGIN;UPDATE users
SET gems = gems — 100
WHERE id=2;UPDATE users
SET gems = gems+100
WHERE id=1;ROLLBACK;
After running the above query you will see no gems transferred from one user to another in the users table. ROLLBACK revert all the changes made by a transaction.
Savepoints are special points inside the transaction that save the state of previously executed commands and establish Rollback for commands which are executed after savepoint.
Savepoint is declared inside the transaction with its name,
SAVEPOINT savepoint_name;To Rollback to the specific savepoint use the following command,
ROLLBACK TO SAVEPOINT savepoint_name;Let’s add another user of Joe in users table,
INSERT INTO users (name, gems) VALUES ('Joe', 1000);Now in the transaction, after 100 gems deduction from David, a savepoint will be marked. Actually, David’s gems will be transferred to Joe but accidentally transferred to John. Next, a Rollback will be established to the marked savepoint, and after that David’s gems will be transferred to Joe and finally, the transaction will be committed.
BEGIN;
UPDATE users
SET gems = gems — 100
WHERE id=1;SAVEPOINT point1;UPDATE users
SET gems = gems+100
WHERE id=2; ROLLBACK TO SAVEPOINT point1;UPDATE users
SET gems = gems+100
WHERE id=3;COMMIT;
To destroy a savepoint at any stage inside the transaction use the following statement,
RELEASE SAVEPOINT savepoint_name;Release savepoint destroys a previously defined specific savepoint at any stage in the current transaction. It is not possible to release a savepoint when the transaction is in an aborted state. If multiple savepoints have the same name then only the most recent savepoint will be released.
