Learn BEGIN, COMMIT, ROLLBACK, and ACID Properties with Examples
Master SQL transactions in this beginner-friendly guide. Learn how to use BEGIN, COMMIT, ROLLBACK, and savepoints to ensure data integrity. Practice implementing money transfers between accounts and rolling back changes in case of errors.
1. Introduction
A transaction is a sequence of one or more SQL operations executed as a single unit.
- Transactions ensure data consistency and reliability.
- Either all operations succeed (commit) or none (rollback).
Key Points:
- Transactions are critical for banking, e-commerce, and financial systems.
- They guarantee ACID properties for data integrity.
2. ACID Properties
| PropertyDescription | |
| Atomicity | All operations succeed or none do. |
| Consistency | Database remains in a consistent state before and after the transaction. |
| Isolation | Transactions do not interfere with each other. |
| Durability | Once committed, changes are permanent. |
3. Transaction Commands
3.1 BEGIN / START TRANSACTION
- Marks the start of a transaction.
Syntax:
BEGIN TRANSACTION;
-- or in MySQL: START TRANSACTION;
3.2 COMMIT
- Saves all changes made during the transaction.
Syntax:
COMMIT;
3.3 ROLLBACK
- Undoes all changes made during the transaction.
Syntax:
ROLLBACK;
3.4 Savepoints
- Allow partial rollbacks within a transaction.
Syntax:
SAVE TRANSACTION SavePointName; -- SQL Server
ROLLBACK TRANSACTION SavePointName;
4. Practical Example: Money Transfer
BEGIN TRANSACTION;
-- Deduct from sender
UPDATE Accounts
SET Balance = Balance - 500
WHERE AccountID = 101;
-- Add to receiver
UPDATE Accounts
SET Balance = Balance + 500
WHERE AccountID = 102;
-- Check for negative balance
IF (SELECT Balance FROM Accounts WHERE AccountID = 101) < 0
BEGIN
ROLLBACK; -- Cancel transaction
PRINT 'Transaction failed: Insufficient funds';
END
ELSE
BEGIN
COMMIT; -- Complete transaction
PRINT 'Transaction successful';
END;
5. Practical Exercises
- Implement a transaction to transfer money between accounts with error handling.
- Create a transaction that inserts data into multiple tables and rollbacks on failure.
- Use savepoints to rollback a portion of a transaction without affecting other operations.
- Test isolation by running two concurrent transactions and observe locking behavior.
- Ensure ACID compliance in all your transactional operations.
6. Tips for Beginners
- Always use transactions for critical operations like banking or inventory management.
- Use ROLLBACK to prevent data corruption in case of errors.
- Test transactions thoroughly to avoid deadlocks.
- Combine transactions with triggers and stored procedures for robust database logic.
Next Step: After mastering transactions, the next module is Advanced SQL Queries, where you’ll learn window functions, CTEs, recursive queries, and pivot/unpivot operations.
