What is a Transaction in a Database?
Learn what a database transaction is, how COMMIT and ROLLBACK work, and how transactions guarantee all-or-nothing data consistency.
Expected Interview Answer
A transaction is a sequence of one or more database operations that executes as a single, indivisible unit of work — either every operation succeeds and commits, or none of them take effect at all.
Transactions bundle related reads and writes (like debiting one account and crediting another) so the database never leaves data in a half-updated state. If any step fails, the transaction rolls back and undoes everything already applied. The database engine coordinates this using logs, locks, and the ACID guarantees, so concurrent transactions do not corrupt each other even under crashes or simultaneous access.
- All-or-nothing execution
- Protects against partial updates
- Safe under crashes and failures
- Enables concurrent access without corruption
AI Mentor Explanation
Think of a run-out review sent to the third umpire: the batter’s dismissal, the scoreboard update, and the striker/non-striker swap must all happen together based on one final decision. If the review overturns the on-field call, none of those changes are applied — the scoreboard stays exactly as before. A database transaction works the same way: every step is provisional until a final commit, and if anything goes wrong, the whole set of changes is discarded as one.
Step-by-Step Explanation
Step 1
Begin the transaction
Mark the start of a unit of work with BEGIN or START TRANSACTION.
Step 2
Execute operations
Run the related INSERT, UPDATE, or DELETE statements that must succeed together.
Step 3
Check for errors
If any statement fails or a constraint is violated, trigger a ROLLBACK to undo all changes.
Step 4
Commit or rollback
If every step succeeded, issue COMMIT to make the changes permanent; otherwise ROLLBACK discards them.
What Interviewer Expects
- Clear definition of atomic, all-or-nothing execution
- A real-world example such as a bank transfer
- Mention of COMMIT and ROLLBACK
- Awareness of how transactions relate to ACID properties
Common Mistakes
- Confusing a transaction with a single SQL statement only
- Forgetting to mention rollback on failure
- Not connecting transactions to concurrency and crash safety
- Unable to give a concrete example
Best Answer (HR Friendly)
“A transaction is a group of database operations that must all succeed or all fail together, like transferring money between two accounts. If anything goes wrong partway through, the database rolls back every change so the data never ends up in an inconsistent, half-updated state.”
Code Example
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;
-- If either UPDATE fails, run ROLLBACK instead of COMMIT
-- to undo both changes and restore the original balances.Follow-up Questions
- What are the ACID properties and how do they relate to transactions?
- What is the difference between COMMIT and ROLLBACK?
- What are savepoints and when would you use one?
- How do transaction isolation levels affect concurrent transactions?
MCQ Practice
1. Which statement makes the changes in a transaction permanent?
COMMIT finalizes all changes made during the transaction, making them durable and visible to others.
2. What happens if a statement inside a transaction fails and ROLLBACK is issued?
ROLLBACK undoes every operation performed since the transaction began, restoring the prior consistent state.
3. A classic real-world example of a database transaction is:
A bank transfer requires a debit and a credit to happen together as one atomic unit, the textbook transaction example.
Flash Cards
What is a transaction? — A group of database operations executed as a single all-or-nothing unit of work.
What does COMMIT do? — Makes all changes in the current transaction permanent and visible to other users.
What does ROLLBACK do? — Undoes all changes made during the current transaction, restoring the previous state.
Why use transactions? — To keep related changes consistent and safe under failures or concurrent access.