What Is a Transaction in SQL?
Learn what a SQL transaction is, the four ACID properties it guarantees, and how BEGIN, COMMIT, and ROLLBACK work, with a practical example.
Expected Interview Answer
A transaction is a sequence of one or more SQL statements that execute as a single all-or-nothing unit of work, guaranteed by the database to satisfy the ACID properties: atomicity, consistency, isolation, and durability.
Atomicity means every statement in the transaction succeeds together, or the whole batch is rolled back as if none of it happened. Consistency ensures the database moves from one valid state to another, respecting constraints and rules. Isolation controls how concurrent transactions see each other's uncommitted changes, governed by isolation levels like READ COMMITTED or SERIALIZABLE. Durability guarantees that once a transaction commits, its changes survive even a crash immediately afterward, typically via a write-ahead log. A typical pattern wraps multiple related writes — like debiting one account and crediting another — inside BEGIN TRANSACTION and COMMIT, with ROLLBACK available if any step fails.
- Atomicity prevents partial updates from corrupting related data
- Consistency keeps the database honoring its constraints at every commit
- Isolation prevents concurrent transactions from seeing half-finished work
- Durability ensures committed data survives crashes
- Rollback gives a safe way to undo a failed multi-step operation
AI Mentor Explanation
A transaction is like a run being credited only after the batter physically crosses the crease and the bails stay intact — the umpire won't award the run for a partial dash, it's all-or-nothing. If a run-out is called mid-attempt, the scoreboard rolls back to before the attempt as if it never happened, rather than showing half a run.
Step-by-Step Explanation
Step 1
Begin the transaction
Mark the start of a unit of work with BEGIN TRANSACTION (or an equivalent statement).
Step 2
Execute the statements
Run all the related INSERT/UPDATE/DELETE statements that must succeed or fail together.
Step 3
Commit on success
COMMIT makes all changes permanent and visible to other sessions.
Step 4
Rollback on failure
If any step errors or a business rule fails, ROLLBACK undoes every change made since BEGIN.
Step 5
Respect isolation level
The configured isolation level (e.g., READ COMMITTED, SERIALIZABLE) determines what concurrent transactions can see mid-flight.
What Interviewer Expects
- Defines a transaction as an all-or-nothing unit of work
- Can explain all four ACID properties in their own words
- Knows BEGIN/COMMIT/ROLLBACK syntax and purpose
- Mentions isolation levels and why they matter for concurrency
- Gives a real example (like a fund transfer) needing a transaction
Common Mistakes
- Forgetting to explain isolation or durability, only covering atomicity
- Thinking a transaction always spans multiple tables (a single statement can be one too)
- Not knowing default isolation levels differ by database engine
- Leaving long-running transactions open, causing lock contention
Best Answer (HR Friendly)
“A transaction bundles multiple database operations into one all-or-nothing unit — like transferring money between two accounts, either both the debit and credit happen, or neither does, so the data never ends up in a broken, half-finished state.”
Code Example
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If both succeed:
COMMIT;
-- If anything fails:
-- ROLLBACK;Follow-up Questions
- What are the differences between READ COMMITTED and SERIALIZABLE isolation levels?
- How does a write-ahead log support durability?
- What is a deadlock and how do databases typically resolve it?
- What is a savepoint and when would you use one?
- How do distributed transactions differ from single-database transactions?
MCQ Practice
1. Which ACID property guarantees a transaction is all-or-nothing?
Atomicity ensures every statement in a transaction succeeds together or none do.
2. What does the Durability property guarantee?
Durability ensures that once a transaction commits, its effects persist even after a system crash.
3. What command undoes all changes made since a transaction began?
ROLLBACK reverts every statement executed since the transaction's BEGIN.
Flash Cards
What is a transaction? — A group of SQL statements executed as one all-or-nothing unit.
ACID stands for — Atomicity, Consistency, Isolation, Durability.
COMMIT — Makes all changes in the transaction permanent.
ROLLBACK — Undoes all changes made since the transaction began.