What are transactions in PostgreSQL?

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.

Stock table

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.

Things to note about PSQL transactions

• Transactions succeed and fail as a unit.
• If all operations succeed, the transaction is successful.
• If all operations fail, the transaction is unsuccessful.
• If one of the transactions succeeds out of many, the transaction as a unit is unsuccessful and will not update in the database.
• If one of the transactions fails out of many, the transaction as a unit is unsuccessful and will not update in the database.
• Transactions are atomic. This implies that a transaction is an all-or-nothing operation, meaning that all operations in the transaction block either succeed or fail.
• Updates in a transaction block simultaneously reflect in the database. One operation in the transaction block cannot be visible in the database while waiting for others to complete.
• Updates to a database by a transaction can be canceled by replacing the COMMIT command with ROLLBACK.
• You can discard some parts of the transaction code block while you commit the rest. This can be achieved through the 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.