How to Use ROLLBACK in MySQL: Syntax and Examples

In MySQL, use the ROLLBACK command to undo all changes made in the current transaction since the last COMMIT. This helps revert unwanted changes and maintain data consistency within transactions.

Syntax

The ROLLBACK command is used to undo all changes made in the current transaction. It must be used after starting a transaction with START TRANSACTION or BEGIN. If you do not commit the transaction, you can rollback to cancel all changes.

• START TRANSACTION; - begins a new transaction.
• ROLLBACK; - cancels all changes made since the transaction started.
• COMMIT; - saves all changes permanently.

sql

START TRANSACTION;
-- SQL statements here
ROLLBACK;

Example

This example shows how to start a transaction, make changes, and then undo them using ROLLBACK. The changes will not be saved to the database.

sql

START TRANSACTION;
INSERT INTO employees (name, position) VALUES ('Alice', 'Developer');
SELECT * FROM employees WHERE name = 'Alice';
ROLLBACK;
SELECT * FROM employees WHERE name = 'Alice';

Output

Query OK, 1 row affected
+----+-------+-----------+
| id | name  | position  |
+----+-------+-----------+
| 10 | Alice | Developer |
+----+-------+-----------+
Query OK, 0 rows affected
+----+------+----------+
| id | name | position |
+----+------+----------+
(empty set)

Common Pitfalls

Common mistakes when using ROLLBACK include:

• Not starting a transaction before using ROLLBACK. Without a transaction, rollback has no effect.
• Forgetting to commit changes when you want to save them.
• Using ROLLBACK after an implicit commit statement like ALTER TABLE, which ends the transaction automatically.

sql

/* Wrong: rollback without transaction */
ROLLBACK;

/* Right: start transaction before rollback */
START TRANSACTION;
UPDATE employees SET position = 'Manager' WHERE id = 1;
ROLLBACK;

Quick Reference

Command	Description
START TRANSACTION;	Begin a new transaction.
COMMIT;	Save all changes made in the transaction.
ROLLBACK;	Undo all changes made in the current transaction.
SAVEPOINT name;	Set a savepoint to rollback partially.
ROLLBACK TO SAVEPOINT name;	Rollback to a specific savepoint.

Key Takeaways

Always start a transaction with START TRANSACTION before using ROLLBACK.

ROLLBACK undoes all changes made since the transaction began.

Use COMMIT to save changes permanently; otherwise, changes can be rolled back.

Certain commands cause implicit commits, ending the transaction automatically.

Savepoints allow partial rollbacks within a transaction.