ROLLBACK

Hook: ROLLBACK is the database's undo button: interviewers love it because it checks whether you understand how SQL keeps data safe when something goes wrong.

Question: What does ROLLBACK do in SQL?

Answer: ROLLBACK cancels the changes made in the current transaction and returns the database to the last committed state. It is used when something fails, when you decide not to keep the changes, or when you want to undo part of a transaction with a savepoint. A key idea is that it only affects uncommitted work; once data is committed, ROLLBACK cannot bring it back.

Interview-Ready Answer: I use ROLLBACK to undo all uncommitted changes in the current transaction. That means inserts, updates, and deletes done since BEGIN are discarded, and the database goes back to the last committed state. It is especially important in multi-step operations like checkout or money movement, because one failed step should not leave partial data behind. If I need only part of the work undone, I use a SAVEPOINT and roll back to that point instead of canceling the whole transaction.

What ROLLBACK really means

Detailed Explanation: Think of a transaction as a work session with a clear finish line. Until you COMMIT, the database treats the changes as temporary. ROLLBACK says, “discard this session’s work and restore the previous committed version.” That is the heart of atomicity (all-or-nothing behavior), one of the ACID properties of transactions.

How it works under the hood

1. You start a transaction with BEGIN (or your client starts one implicitly in autocommit-off mode).
2. The database records your writes as part of the open transaction, not as permanent truth yet.
3. If something fails or you change your mind, you issue ROLLBACK.
4. The engine discards the uncommitted changes, releases locks tied to that transaction, and marks the transaction as finished.
5. Other sessions then see only the last committed data, so the database stays consistent.

One important mental model: a rollback is not a time machine for the whole database. It only applies to the current transaction. If another session already committed its work, your rollback cannot touch it.

ROLLBACK vs COMMIT vs ROLLBACK TO SAVEPOINT

When and why to use it

• Multi-step writes: If one step fails, you want the whole unit of work to disappear.
• Manual safety: In admin sessions, you can test a write and back out if the result looks wrong.
• Partial recovery: With SAVEPOINT, you can undo only the risky part while keeping earlier valid work.

Performance and practical cost

From the application’s point of view, asking for a rollback is fast. But the database still has to clean up the transaction’s changes, so the real cost depends on how much work the transaction did. A small transaction with a few rows often rolls back in milliseconds; a huge batch that touched hundreds of thousands of rows can take noticeable time because the engine must discard that work and release resources. In interview terms, the logical complexity is roughly proportional to the amount of uncommitted change, not a fixed constant.

Important edge cases

• No open transaction: Some databases ignore ROLLBACK or raise an error if nothing is active.
• Committed work: Once COMMIT happens, rollback cannot undo it.
• After an error: Many databases mark the transaction as aborted; you must rollback before you can continue safely.
• DDL differences: Some databases treat certain schema changes as transactional, while others auto-commit them, so not every DDL statement can be rolled back everywhere.
• External side effects: A rollback only reverses database changes, not emails sent, files written, or payment gateways called.

Memory angle for the whiteboard: ROLLBACK is the “erase the draft, not the published book” command. Draft work disappears; published work stays.

Real-world story

Real-World Example: Imagine a checkout service for an online store. In one transaction, it inserts an order, reserves inventory, and marks the cart as paid. If the inventory update fails because stock is gone, the service should ROLLBACK so the order row, payment flag, and stock reservation do not end up half-finished.

What goes wrong when people misunderstand this? They might commit each step separately, thinking they can “fix it later.” Then a failure leaves the system in a messy state: the customer sees an order confirmation, inventory is reserved incorrectly, and support logs show constraint errors or orphan rows. The symptom is usually partial data, angry customers, and reconciliation jobs that have to clean up what should have been prevented by one transaction.

One subtle but important lesson: if the checkout service charged a credit card through an external payment API before the database rollback, the database cannot undo that charge. That is why teams often use a careful order of operations, idempotency keys, and compensating actions for external systems.

Follow-up & Tricky Questions:

• What is the difference between ROLLBACK and COMMIT? COMMIT makes the transaction permanent, while ROLLBACK throws away the uncommitted work. They are opposites: one publishes the draft, the other deletes it.
• What is SAVEPOINT? A savepoint is a marker inside a transaction that lets you undo only the later part of the work. It is useful when one sub-step is risky but you still want to keep the earlier successful changes.
• What happens if you ROLLBACK after an error? In many databases, the transaction becomes aborted after a statement error, and rollback is required before you can continue. That is why application code usually catches the failure and rolls back immediately.
• Does ROLLBACK undo locks? Yes, the transaction’s locks are released when the transaction ends. That is one reason long-running transactions are dangerous: they can block other users until rollback or commit happens.
• Can ROLLBACK undo schema changes? Sometimes, but not always. It depends on the database and the specific DDL statement, because some systems auto-commit certain schema operations.
• Tricky: If I rolled back, did the database never do the work? No. The database did the work, then discarded the uncommitted result. That distinction matters for performance, locks, and side effects outside the database.
• Tricky: Can rollback undo an email or payment sent by the app? No. It only reverses database state. Anything outside the database needs its own compensation or idempotency strategy.
• Tricky: Can I keep using a transaction after a failed statement? Often not until you roll back to a savepoint or roll back the whole transaction. Many candidates miss that the transaction may be poisoned after the first error.

Common Mistakes:

• Mistake: Thinking ROLLBACK can undo committed data. Correction: It only affects the current uncommitted transaction.
• Mistake: Forgetting that external actions are not rolled back. Correction: Use compensation logic for emails, APIs, and file writes.
• Mistake: Leaving transactions open for too long. Correction: Keep transactions short so locks and rollback cost stay small.
• Mistake: Assuming every database treats DDL the same way. Correction: Know your engine’s transaction rules before relying on rollback for schema changes.

Memory Hook: “Rollback is the undo button for the current draft. If it’s committed, the ink is dry.”

Cheat Sheet:

• ROLLBACK cancels uncommitted changes.
• COMMIT makes changes permanent.
• SAVEPOINT lets you undo part of a transaction.
• Rollback does not undo external side effects.
• After an error, the transaction may need a rollback before continuing.
• Short transactions are easier to roll back and safer under load.

Practice Tasks:

• Create a table, insert a few rows inside a transaction, and verify that ROLLBACK leaves the table unchanged.
• Add a SAVEPOINT, make a second insert, then roll back only to the savepoint and confirm the first insert remains.
• Try a transaction with a deliberate mistake in a test database, then observe how your DB behaves before and after rollback.