A rollback is the operation that cancels an uncommitted database transaction and undoes every change it made, so that the database returns to the state it had before the transaction started. In database transactions, rollback is the mechanism behind atomicity, the A in ACID: a transaction either takes effect completely through a commit or leaves no trace at all through a rollback.
A rollback can be requested explicitly with the SQL statement ROLLBACK, or it can be triggered automatically by the database when an error, a deadlock, a constraint violation, a lost connection or a server crash prevents the transaction from completing. In both cases other sessions never see the discarded changes.
How a rollback undoes changes
A database can roll back a transaction only because it records enough information to reverse each change before or while making it. Engines use two main designs, and the design determines how expensive a rollback is.
| Engine design | What is stored | Cost of rollback |
|---|---|---|
| Undo log (MySQL InnoDB, Oracle) | Before-images of changed rows in undo segments | Proportional to the work done; large transactions roll back slowly |
| MVCC with versioned tuples (PostgreSQL) | New row versions tagged with the transaction ID | Nearly instant; the transaction is marked aborted |
| Shadow paging or journal (SQLite rollback journal) | Original copies of modified pages | Original pages are restored from the journal |
In an undo-log engine, the database walks the undo records backward and restores each original value, so rolling back a transaction that updated ten million rows can take as long as the update itself or longer. In PostgreSQL, the new row versions simply become invisible once the transaction is marked aborted, and VACUUM later removes them as dead tuples.
The write-ahead log keeps rollback safe across crashes. During recovery the engine first redoes logged work and then rolls back every transaction that had not committed at the moment of the crash, which is why a power failure never leaves half a transaction in the data.
Kinds of rollback
The term rollback covers several related operations that differ in scope and trigger. Distinguishing them avoids confusion when reading logs and error messages.
| Type | Trigger | Scope |
|---|---|---|
| Explicit rollback | Application issues ROLLBACK | Entire current transaction |
| Rollback to savepoint | ROLLBACK TO SAVEPOINT name | Changes after the savepoint only |
| Automatic statement rollback | A single statement fails | That statement only in most engines; whole transaction in PostgreSQL |
| Deadlock victim rollback | Deadlock detector chooses a victim | Entire transaction of the victim |
| Crash recovery rollback | Server restarts after failure | All transactions uncommitted at the crash |
Savepoints create nested points of return inside a transaction. They let an application retry one step, for example inserting a row that may violate a unique constraint, without abandoning earlier work. In PostgreSQL, any error puts the transaction into an aborted state in which further statements fail until a rollback or a rollback to savepoint is issued.
A rollback also releases every lock the transaction held, but only after the undo work is finished. Under any isolation level, other sessions that wait on those rows stay blocked until the rollback completes, which is why a long rollback after a failed batch job can stall unrelated traffic for minutes. Monitoring views such as pg_stat_activity in PostgreSQL or the InnoDB transaction list in MySQL show transactions that are still rolling back.
Rollback compared with commit and other recovery tools
Rollback and commit are the two possible endings of every transaction. Once a commit succeeds, a rollback can no longer undo it; reversing committed data requires new compensating changes or a restore from backup.
| Mechanism | Applies to | Typical use |
|---|---|---|
| Rollback | Uncommitted transaction | Error handling and aborted business operations |
| Commit | Uncommitted transaction | Make changes durable and visible |
| Compensating transaction | Committed changes | Business reversal such as a refund |
| Point-in-time recovery | Whole database or cluster | Recover from accidental deletes or corruption |
| Schema migration rollback | Deployed schema version | Revert a release; separate from SQL ROLLBACK |
Some engines commit DDL implicitly. In MySQL, statements such as CREATE TABLE or ALTER TABLE cause an implicit commit, so they cannot be rolled back with the surrounding transaction, while PostgreSQL supports transactional DDL for most schema changes.
Pitfalls and good practice
Rollback guarantees correctness only for work inside the database transaction. Many production incidents come from assuming it covers more than it does.
- External side effects are not undone. An email sent or an API called before the rollback stays sent; perform such actions after commit or through an outbox table.
- Sequences are not rolled back. Values taken from a sequence or auto-increment counter are consumed, which leaves gaps in identifiers.
- Large transactions roll back slowly in undo-log engines; split bulk updates into batches of a few thousand rows.
- Long open transactions hold locks and old row versions, so a forgotten transaction harms other sessions long before it is rolled back.
- Non-transactional tables such as MySQL MyISAM ignore rollback entirely.
Application code should treat rollback as the default outcome of any failure. A common pattern wraps the transaction in a try block, commits only at the end, and issues ROLLBACK in every error path, including timeouts and cancellations, before the connection returns to a pool.
In everyday language
A rollback is the database’s undo button for unfinished work. A transaction is a group of changes that belong together. If something goes wrong halfway, the database throws away all of those changes. Nothing is left half done.
Think of a bank transfer. Money leaves one account and arrives in another. If the second step fails, the rollback puts the money back in the first account. Nobody ever sees money disappear in between.
Seen in the real world
An online ticket shop processes a purchase in one transaction: it reserves two seats, inserts an order row, records the payment reference and reduces the remaining capacity of the event. The payment provider is called before the transaction begins, and the database work then runs as a single unit.
During a popular sale, another buyer takes one of the two seats a fraction of a second earlier, and the seat reservation fails on a unique constraint. The application catches the error and issues ROLLBACK. The order row and the capacity change disappear together, the customer sees a message that the seats are no longer available, and the payment authorization is released. No orphaned order or wrong seat count remains, and the shop’s capacity figures stay exact throughout the sale.