🔐 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, and SAVEPOINT
• 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
• COMMIT ensures 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 COMMIT saves the valid orders entry

---

💡 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, and ROLLBACK to manage transactions
• SAVEPOINT allows 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 :