Transaction Control in SQL
Learn how to control, save, or undo database changes using SQL transaction commands.
We'll cover the following...
Imagine we’re processing a customer’s large order in our OnlineStore database.
The system updates stock quantities, loyalty points, and the order records. But what if, midway through this process, a failure occurs—perhaps a system crash or a data error? Without proper safeguards, we could end up with inconsistent data: reduced stock but no recorded order.
That’s where transaction control in SQL becomes essential.
By the end of this lesson, we will be able to:
Understand the purpose of transaction control commands in SQL.
Use
START TRANSACTIONto start a new transaction.Use
COMMITto permanently save the changes of a transaction.Use
ROLLBACKto undo the changes of a transaction if an error occurs.Apply these commands to ensure data integrity in multi-step operations.
Transaction control
We have already learned about the ACID properties, which guarantee that transactions are reliable.
Most simple INSERT, UPDATE, or DELETE statements run as implicit, single-statement transactions. The database automatically starts a transaction before the statement runs and commits it if the statement succeeds, or rolls ...