What is a Transaction?
Transactions in SQL
A transaction treats multiple SQL statements as a single, atomic unit of work.
Transaction Commands
| Command | Purpose |
|---|---|
BEGIN / START TRANSACTION |
Initiates a transaction boundary |
COMMIT |
Permanently saves all changes to disk |
ROLLBACK |
Reverts all changes made since BEGIN |
SAVEPOINT name |
Creates a checkpoint within the transaction |
ROLLBACK TO SAVEPOINT name |
Undoes changes only back to the checkpoint |
Transaction Control Syntax
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT debit_complete;
UPDATE accounts SET balance = balance + 100 WHERE id = 999; -- Erroneous account
-- Revert back to the savepoint if error occurs:
ROLLBACK TO SAVEPOINT debit_complete;
COMMIT;