SQL TRANSACTIONS – BEGIN / COMMIT / ROLLBACK / SAVEPOINT Explained
Introduction – Why SQL Transactions Matter
In real-world applications like banking, inventory management, and data migration, operations must be atomic—either all succeed or none at all. This is where SQL Transactions come into play.
A transaction is a sequence of SQL statements that are executed as a single unit of work. Transactions ensure data integrity by following the ACID properties: Atomicity, Consistency, Isolation, and Durability.
In this guide, you’ll learn:
- The core commands:
BEGIN,COMMIT,ROLLBACK, andSAVEPOINT - Real-world use cases and syntax
- Best practices and common pitfalls
1. What Is a SQL Transaction?
A transaction is a logical unit of work that must be either fully completed or fully undone. It begins with BEGIN (or START TRANSACTION) and ends with either COMMIT or ROLLBACK.
2. ACID Properties Overview
| Property | Description |
|---|---|
| Atomicity | All operations succeed or none at all |
| Consistency | Database remains in a valid state before and after |
| Isolation | Intermediate states are invisible to other transactions |
| Durability | Once committed, changes are permanent |
3. Syntax: BEGIN, COMMIT, ROLLBACK
Start a Transaction
BEGIN; -- Or START TRANSACTION;
Commit the Transaction
COMMIT;
Rollback the Transaction
ROLLBACK;
4. Real-World Example – Bank Transfer
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
COMMIT;
Explanation:
- Deducts money from account 1
- Adds money to account 2
COMMITensures both updates are permanent
If there’s a failure between updates, use ROLLBACK to avoid inconsistency.
5. Using SAVEPOINT and ROLLBACK TO
SAVEPOINT allows partial rollbacks within a transaction. Useful for large transactions with multiple steps.
Example:
BEGIN;
INSERT INTO orders VALUES (101, 'Laptop');
SAVEPOINT s1;
INSERT INTO payments VALUES (501, 101, 50000);
-- Oops, payment fails validation
ROLLBACK TO s1;
-- Continue with the transaction
COMMIT;
Explanation:
- Creates a restore point after the first insert
- Rolls back only the payment insert
- Final
COMMITsaves the validordersentry
6. Practical Use Cases for Transactions
| Scenario | Why Transactions Help |
|---|---|
| Banking systems | Ensure money isn’t lost or duplicated |
| Inventory systems | Prevent double-selling of items |
| Data migrations | Rollback if partial migration fails |
| Batch updates | Ensure consistency across multiple row operations |
| Order + Payment processing | Guarantee full order or cancel entirely |
7. Platform-Specific Notes
| DBMS | Start Keyword | Savepoint Support | Notes |
|---|---|---|---|
| MySQL | START TRANSACTION | Yes | Autocommit must be disabled manually |
| PostgreSQL | BEGIN | Yes | Full support for nested transactions |
| SQL Server | BEGIN TRANSACTION | Yes | SAVE TRANSACTION for savepoints |
| Oracle | Implicit with DML | Yes | SAVEPOINT and ROLLBACK TO supported |
Tips & Gotchas
| Best Practices | Avoid This |
|---|---|
| Always commit or rollback explicitly | Forgetting to close a transaction |
Use SAVEPOINT in long, multi-step transactions | Relying on implicit commits (can be dangerous) |
| Disable autocommit for manual transaction control | Making partial updates without rollback plan |
Summary – Recap & Next Steps
SQL transactions provide robust control over critical operations, ensuring your data is safe, consistent, and reliable.
Key Takeaways:
- Use
BEGIN,COMMIT, andROLLBACKto manage transactions SAVEPOINTallows granular control with partial rollbacks- Transactions are essential in multi-step, mission-critical logic
Real-World Relevance:
Transactions form the backbone of banking, retail, logistics, and any enterprise system where data accuracy is non-negotiable.
FAQ – SQL Transactions
What happens if I forget to COMMIT a transaction?
The transaction stays open, locking resources. If the session ends, some DBMSs automatically roll it back.
What’s the difference between ROLLBACK and ROLLBACK TO?
ROLLBACK cancels the entire transaction, while ROLLBACK TO reverts to a specific SAVEPOINT.
Is COMMIT required after every transaction?
Yes, unless you’re in autocommit mode (which is default in many databases). Explicit COMMIT is always safer.
Can I nest transactions?
Not directly, but you can simulate nesting using SAVEPOINT and ROLLBACK TO.
Is a SELECT statement transactional?
No. Only data-changing commands (INSERT, UPDATE, DELETE, MERGE) are transactional.
Share Now :
