Transactions in MySQL & ACID Properties
When working with databases, sometimes a single task requires multiple SQL statements.
For example:
- Deduct money from one bank account
- Add money to another account
If one step succeeds and the other fails, the data becomes inconsistent.
To solve this, MySQL uses transactions.
In MySQL, transactions ensure that a group of operations either fully succeed or fully fail.
1. What Is a Transaction?
A transaction is a sequence of one or more SQL statements treated as a single logical unit of work.
Either:
- All statements execute successfully
OR - None of them are applied
This protects data integrity.
2. Why Transactions Are Needed
Consider a banking example:
Transfer ₹1000 from Account A to Account B.
Steps:
- Deduct ₹1000 from Account A
- Add ₹1000 to Account B
If the system crashes after step 1 but before step 2:
- Money is deducted
- But not credited
This causes data inconsistency.
Transactions prevent this problem.
3. ACID Properties
Transactions follow four important properties called ACID.
A – Atomicity
Atomicity means:
Either all operations happen, or none happen.
If one statement fails:
- The entire transaction is rolled back.
Example:
START TRANSACTION;
UPDATE Accounts
SET balance = balance - 1000
WHERE account_id = 1;
UPDATE Accounts
SET balance = balance + 1000
WHERE account_id = 2;
COMMIT;
If any statement fails:
Use ROLLBACK instead of COMMIT.
C – Consistency
Consistency means:
The database remains in a valid state before and after the transaction.
Constraints like:
- Primary key
- Foreign key
- NOT NULL
Are enforced during transactions.
If rules are violated, the transaction fails.
I – Isolation
Isolation means:
Transactions do not interfere with each other.
If two users access the same data simultaneously:
- Each transaction behaves as if it were running alone.
This prevents:
- Dirty reads
- Inconsistent data
(MySQL provides different isolation levels, but that is advanced.)
D – Durability
Durability means:
Once a transaction is committed, the changes are permanent.
Even if:
- System crashes
- Power failure occurs
Committed data remains saved.
4. Controlling Transactions
START TRANSACTION
Begins a transaction.
START TRANSACTION;
COMMIT
Saves all changes permanently.
COMMIT;
ROLLBACK
Undoes all changes since the transaction started.
ROLLBACK;
5. SAVEPOINT
SAVEPOINT allows partial rollback.
Example:
START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE account_id = 1;
SAVEPOINT after_deduction;
UPDATE Accounts SET balance = balance + 1000 WHERE account_id = 2;
ROLLBACK TO after_deduction;
COMMIT;
This rolls back only to the savepoint instead of the entire transaction.
Useful in complex operations.
6. Auto-Commit Mode
By default, MySQL runs in auto-commit mode.
This means:
- Every statement is automatically committed.
To disable auto-commit:
SET autocommit = 0;
Or use START TRANSACTION manually.
7. Real-World Example
Online shopping:
When placing an order:
- Insert order record
- Reduce product stock
- Record payment
If payment fails:
- The order should not be stored
- Stock should not be reduced
All these actions must be inside a transaction.
8. Common Beginner Mistakes
- Forgetting to use COMMIT
- Not handling errors properly
- Leaving transactions open too long
- Confusing auto-commit behavior
- Not using transactions for multi-step operations
Summary
Transactions ensure safe and reliable database operations.
Key commands:
- START TRANSACTION
- COMMIT
- ROLLBACK
- SAVEPOINT
ACID properties guarantee:
- Atomicity
- Consistency
- Isolation
- Durability
Transactions are essential in real-world applications like banking, e-commerce, and financial systems.