ROLLBACK

Definition

ROLLBACK is the statement that ends a transaction UNSUCCESSFULLY, undoing every change it made so far and returning the database to the state before the transaction began.

How It Works

sql
START TRANSACTION;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
ROLLBACK;

After ROLLBACK, both balances are exactly as they were before the transaction started — as if none of the statements inside it ever ran. This is Atomicity (22.5) in action: an incomplete or unwanted transaction leaves absolutely no trace.

Edge Cases and Pitfalls

• ROLLBACK is also what the database does AUTOMATICALLY on certain failures (a constraint violation, a deadlock the database chooses to abort, a crash) — you don't always have to write ROLLBACK explicitly for it to happen; application code should still be prepared to catch such errors and treat the transaction as rolled back.
• On the SQL judge platform used in this course, ROLLBACK only actually undoes anything on a transactional engine (InnoDB); the same script against a default MyISAM table would silently leave the changes applied, verified empirically while preparing this chapter's material.
• A common real-world mistake: forgetting to ROLLBACK (or COMMIT) an interactive session's open transaction, leaving it "hanging" and potentially holding locks — most tools/drivers auto-rollback on disconnect, but relying on that rather than closing transactions explicitly is fragile practice.

Key Takeaways

• ROLLBACK undoes every statement in the current transaction, returning the database to its pre-transaction state.
• ROLLBACK can happen automatically (constraint violations, deadlocks, crashes), not only via an explicit statement.
• ROLLBACK requires a transactional storage engine to have any real effect — a non-transactional engine like MyISAM ignores it.