SQL Transactions โ COMMIT, ROLLBACK & SAVEPOINT
SQL isn't only about retrieving data.
In real-world systems, SQL is also used to:
โข Insert records
โข Update records
โข Delete records
โข Process financial transactions
โข Move money between accounts
โข Update multiple related tables
โข Maintain data consistency
But what happens when a process involves multiple SQL statements and one of them fails?
That's where SQL transactions become important.
1๏ธโฃ What Is a Transaction?
A transaction is a group of one or more SQL operations that are treated as a logical unit of work.
For example, transferring money between two accounts may involve:
โข Account A โ Deduct โน1,000
โข Account B โ Add โน1,000
These two operations should normally be treated as one transaction.
You don't want this situation:
โข Account A โ โน1,000 deducted โ
โข Account B โ โน1,000 not credited โ
The transaction mechanism helps maintain consistency.
2๏ธโฃ Basic Transaction Flow
BEGIN TRANSACTION
โ
SQL Statement 1
โ
SQL Statement 2
โ
SQL Statement 3
โ
COMMIT
If something goes wrong:
BEGIN TRANSACTION
โ
SQL Statement 1
โ
SQL Statement 2 โ
โ
ROLLBACK
โ
Changes undone
3๏ธโฃ BEGIN TRANSACTION
Depending on the database, you may see:
BEGIN TRANSACTION;
-- or
BEGIN;
Some database systems handle transaction boundaries differently, so exact syntax varies.
For example:
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 101;
The transaction has started.
4๏ธโฃ COMMIT
"COMMIT" permanently saves the changes made during the transaction.
Example:
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 101;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 202;
COMMIT;
After the transaction is successfully committed, the changes become durable according to the database's transaction rules.
5๏ธโฃ ROLLBACK
"ROLLBACK" reverses uncommitted changes within the transaction.
Example:
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 101;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 202;
ROLLBACK;
The changes made in that transaction are undone.
6๏ธโฃ COMMIT vs ROLLBACK
This is a common interview question.
โข COMMIT โ Save the transaction
โข ROLLBACK โ Undo uncommitted changes
Example:
BEGIN;
UPDATE customers
SET status = 'Inactive'
WHERE customer_id = 101;
COMMIT;
The change is committed.
Whereas:
BEGIN;
UPDATE customers
SET status = 'Inactive'
WHERE customer_id = 101;
ROLLBACK;
The uncommitted update is rolled back.
7๏ธโฃ SAVEPOINT
Sometimes you don't want to roll back the entire transaction.
You can create a "SAVEPOINT".
Example:
BEGIN;
UPDATE customers
SET status = 'Active'
WHERE customer_id = 101;
SAVEPOINT customer_update;
UPDATE customers
SET status = 'Inactive'
WHERE customer_id = 102;
Now suppose you don't want the second update.
You can roll back to the savepoint:
ROLLBACK TO SAVEPOINT customer_update;