Database Rollback – What and Why


In database technologies, rollback is a command that returns the database to some previous point, and it is used to restart the current transaction and reestablish the last commitment boundary.

Rollbacks are essential for database integrity as they indicate that the database can be restored to a clean copy even after an incorrect operation. By rolling back to an active transaction, the database can be restored to a consistent state.

When the ROLLBACK command is executed, the changes made to database files, updates, additions, or deletions since the commitment boundary are rolled back and the original entries are put back in the files. Records added to the files remain as deleted records. The files are repositioned to the last commitment boundary. Changes made to other commitment resources are rolled back as well.

Automatic and Manual Rollback

Rollbacks can be performed manually by users or automatically by database systems.

If a user modifies a data field but does not keep the changes, the data is stored in a transaction log or a temporary state. Users who run a query against the database will see the unchanged values. 

However, if the user chooses not to save the data, a rollback command handles the data to discard any change made by the user. Therefore, a rollback happens when a user starts changing data, finds out that the wrong record is updated, and cancels the transaction to undo any modifications.

A rollback could also happen automatically after a database or server crash. When the database restarts, all unfinished transactions are rolled back, enabling users to enter again and save the necessary changes.

Difference Between COMMIT and ROLLBACK 

COMMIT and ROLLBACK are the two transactional statements that are used to do or undo actions. 

COMMIT is a statement that indicates the successful completion of a transaction. Every time a transaction completes its execution without any interruption, the changes made to the database become permanent. This means that the database cannot retrieve its previous states.

If the transaction enters into a committed state, it cannot be rolled back, and a new transaction starts.

Same as COMMIT, ROLLBACK is a statement, but it indicates that the transaction has not been performed successfully. 

ROLLBACK is vital if an error happens during the execution of a transaction. ROLLBACK can occur only if COMMIT is not yet executed.

The principal difference between the COMMIT and ROLLBACK commands is that the execution of the COMMIT statement makes all the modifications made by the current transaction permanent. On the other hand, ROLLBACK deletes all the modifications made by the current session, and the database reaches its previous state.

To make sure that the modifications done by the transaction are permanently kept in the database, use COMMIT after the transaction’s successful completion. If the transaction faces any error during the execution, ROLLBACK is used to undo the changes.

Contact Soaring Eagle Consulting for a Free Database Evaluation Today

Getting started is simple. Click the button below to request your free one-hour database assessment from the DBA experts at Soaring Eagle Consulting®.

Get Your Free Database Evaluation