How Transactions Work in SQL (ACID)
💡 Concept Name
SQL Transactions are logical units of work made up of one or more SQL statements that must all succeed or fail as a group, governed by ACID properties.
📘 Quick Intro
Transactions ensure that database operations are performed reliably. SQL uses ACID properties—Atomicity, Consistency, Isolation, and Durability—to maintain integrity during operations like updates, inserts, or deletes.
🧠 Analogy / Short Story
Think of a bank transfer: you withdraw money from Account A and deposit it into Account B. If one step fails, the transaction is rolled back. SQL transactions ensure both steps happen together or not at all, just like undoing a failed money transfer.
🔧 Technical Explanation
- BEGIN TRANSACTION marks the start of a transaction block.
- COMMIT saves all changes permanently to the database.
- ROLLBACK undoes all changes since the last BEGIN TRANSACTION.
- Atomicity: All or nothing execution.
- Consistency: Maintains data integrity before and after.
- Isolation: Prevents interference from other concurrent transactions.
- Durability: Changes remain even after crashes once committed.
🎯 Purpose & Use Case
- ✅ Group multiple related changes into one atomic operation.
- ✅ Prevent partial updates that can corrupt data.
- ✅ Ensure consistent state in concurrent user environments.
- ✅ Handle errors gracefully with rollback support.
💻 Real Code Example
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
IF @ERROR <> 0
ROLLBACK;
ELSE
COMMIT;
❓ Interview Q&A
Q1: What is a transaction in SQL?
A: A group of SQL statements executed as a single unit of work that must either all succeed or all fail.
Q2: What are ACID properties?
A: Atomicity, Consistency, Isolation, and Durability — the key principles that ensure safe transaction processing.
Q3: What is the use of ROLLBACK?
A: It undoes all changes made during the transaction if any part fails.
Q4: What ensures data integrity across transactions?
A: The Consistency property of ACID ensures valid data before and after the transaction.
Q5: How does Isolation help in transactions?
A: It prevents one transaction’s intermediate results from being visible to others.
Q6: Can transactions be nested in SQL Server?
A: Yes, but only the outermost COMMIT completes the transaction.
Q7: What is the default isolation level in SQL Server?
A: READ COMMITTED.
Q8: What happens if a COMMIT is missed?
A: The transaction remains open, locking resources, or may be rolled back on disconnect.
Q9: What is the role of @ERROR?
A: It checks for errors after SQL statements to conditionally commit or rollback.
Q10: Is durability guaranteed during a crash?
A: Yes, once COMMIT is executed, changes persist even after a crash, thanks to logging.
📝 MCQs
Q1. What does BEGIN TRANSACTION do?
- Commits changes
- Starts a new transaction block
- Ends a session
- Creates a backup
Q2. Which ACID property ensures all steps succeed or none?
- Consistency
- Atomicity
- Durability
- Concurrency
Q3. What does ROLLBACK do?
- Saves changes
- Validates schema
- Undoes all changes in the current transaction
- Refreshes indexes
Q4. Which SQL statement makes a transaction permanent?
- ROLLBACK
- BEGIN
- SAVEPOINT
- COMMIT
Q5. Which is not part of ACID?
- Atomicity
- Durability
- Flexibility
- Isolation
Q6. Which property ensures data is valid before and after transaction?
- Isolation
- Atomicity
- Consistency
- Rollback
Q7. What prevents dirty reads in SQL Server?
- NOLOCK
- AUTO COMMIT
- READ COMMITTED isolation level
- UNCOMMITTED
Q8. What ensures committed changes survive a crash?
- Atomicity
- Durability
- Isolation
- Logging
Q9. Which T-SQL keyword ends a transaction successfully?
- END
- ROLLBACK
- COMMIT
- RELEASE
Q10. Which of the following supports concurrent access safely?
- Atomicity
- Commit
- Isolation
- DROP
💡 Bonus Insight
Using transactions strategically helps prevent data corruption, especially during batch operations or complex workflows. Always handle exceptions and log errors to support reliable rollbacks and audits in production systems.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!