A transaction is a collection of queries that has a beginning and an end. A transaction can be seen as a single unit of work that contains multiple queries. The actions performed in PostgreSQL (PSQL) transactions start with a Begin
statement and end after the operation has been committed.
The failure of a transaction does not in any way affect the content of the database the transaction was supposed to alter. This is because multiple steps of actions are bundled into a single step, which implies that if a step fails, no change is updated in the database. The principle of all steps being successful before actions are updated in the database is known as all-or-nothing.
ID | Item | Unit_price | Quantity |
---|---|---|---|
1 | Juicy Apples | 0.99 | 100 |
2 | Mangoes | 0.50 | 20 |
Let us perform some simple PostgreSQL transactions that update the unit price (which is in USD) of Mangoes and Juicy Apples by adding $0.5 to their current price and incrementing the quantities by 50.
BEGIN;
Update Stock set Unit_price = Unit_price+0.5 where ID = 2
Update Stock set Unit_price = Unit_price+0.5 where ID = 1
Update Stock set Quantity = Quantity+50 where ID = 1
Update Stock set Quantity = Quantity+50 where ID = 2
COMMIT;
The example above represents a unit of operation (or a transaction block) that demonstrates what transaction in PostgreSQL is. Transactions serve as a guarantee that if something goes wrong while executing any of those operations, none of the operations will be affected in the database. The increase in the unit price of Mangoes will not happen if the unit price of Juicy Apples fails to update. Since we want to update each fruit quantity by 50, Juicy Apples will not be updated if Mangoes fail to update. PQSL transactions perform these checks for us.
COMMIT
command with ROLLBACK
.SAVEPOINT
command. The ROLLBACK TO
command can be used to roll back to the savepoint you defined and release the savepoints that were defined in it.