Database transaction is a collection of multiple database operations (ex:
INSERT) as a single unit of work. It represent real-world events of any company. Error handling becomes much simple for an application when using transactions.
We can define where the transaction will begin and when the transaction will end. To start the transaction we use
If queries in a transaction ran successfully, then we save the changes using
COMMIT syntax. If queries in a transaction failed to run, then we revert the changes before the transaction ran using
Now, let's see the example of a transaction.
Alice wants to transfer $100 to John. In the database,
ACCOUNT table stored Alice and John account data.
In a transaction, the operations are:
// Select Alice account data // Check the balance SELECT BALANCE FROM ACCOUNT WHERE ID = 1 // Substract 100 from Alice balance UPDATE ACCOUNT SET BALANCE = BALANCE - 100 WHERE ID = 1 // Add 100 to Alice balance UPDATE ACCOUNT SET BALANCE = BALANCE + 100 WHERE ID = 2
If the operations was success, then we have to commit the transaction using
If one of the operations was failed, then we have to rollback the transaction using