Introduction
Suppose a student’s status and city are updated incorrectly and the changes should not be saved. If both updates are still part of an open transaction, ROLLBACK can cancel them together.
This is useful when a change is incorrect or a related operation cannot be completed successfully.
How ROLLBACK Works
ROLLBACK ends the current transaction and undoes its uncommitted data changes. A transaction groups related SQL statements into one unit. The changes can be finalized with COMMIT or canceled with ROLLBACK.
Syntax
ROLLBACK;ROLLBACK affects the work in the current transaction. It does not reverse changes already committed by other transactions. The affected tables must support transactions.
Example: Undoing Two Student Updates
Assume the students table contains these already committed rows. Each example below starts from this same data.
student_id | full_name | status | city |
|---|---|---|---|
1 | Ayaan | ACTIVE | Pune |
2 | Meera | ACTIVE | Delhi |
The following operation changes Ayaan’s status to BANNED and his city to Bengaluru, inspects the pending changes, and then cancels both updates. Run the entire example on the same database connection.
START TRANSACTION;UPDATE studentsSET status = 'BANNED'WHERE student_id = 1;UPDATE studentsSET city = 'Bengaluru'WHERE student_id = 1;-- View the pending changes in this transaction.SELECT student_id, full_name, status, cityFROM studentsORDER BY student_id;ROLLBACK;-- View the values after both updates are undone.SELECT student_id, full_name, status, cityFROM studentsORDER BY student_id;Before ROLLBACK
The first SELECT runs inside the transaction and returns:
student_id | full_name | status | city |
|---|---|---|---|
1 | Ayaan | BANNED | Bengaluru |
2 | Meera | ACTIVE | Delhi |
The current transaction can see its own updates. Ayaan’s new status and city are still uncommitted.
After ROLLBACK
The second SELECT returns:
student_id | full_name | status | city |
|---|---|---|---|
1 | Ayaan | ACTIVE | Pune |
2 | Meera | ACTIVE | Delhi |
Both updates have been undone: Ayaan’s status is ACTIVE again, and his city is Pune. Meera’s row remains unchanged because neither UPDATE selected it.
ROLLBACK cancels all the uncommitted changes in this transaction, including both updates.
Rules to Remember
Start the transaction before making changes: MySQL enables autocommit by default. Outside an explicit transaction, a successful UPDATE is committed automatically. Running ROLLBACK afterward cannot undo it.
Committed changes cannot be rolled back: Once COMMIT completes, a later ROLLBACK cannot reverse that transaction. Correcting those values requires a new operation.
Keep the same connection: A transaction belongs to one database connection. With a connection pool, retain one acquired connection until COMMIT or ROLLBACK finishes.
MySQL table-definition commands can end the transaction: Commands such as ALTER TABLE and TRUNCATE TABLE cause an implicit commit, which commits the active transaction automatically. Do not expect a later ROLLBACK to undo those commands or the earlier changes they committed.
A MySQL error may undo only one statement: Some errors leave earlier changes in the transaction pending. If the whole operation must be abandoned, explicitly issue ROLLBACK instead of assuming every error cancels the entire transaction.
Summary
ROLLBACK ends the current transaction and undoes its uncommitted changes. In the student example, it reverses both updates and restores Ayaan’s original status and city. Start the transaction before making changes, keep its statements on the same connection, and use transactional tables in MySQL. Changes that have already been committed cannot be undone by a later ROLLBACK.
Be the first to add a comment.