Skip to main content
Documents
Share
LinkedIn
Facebook
Twitter
Copy
ClausesStatementsKeywordsExpressionsFunctionsIndexesPerformance Optimization

MySQL ROLLBACK Statement

The `ROLLBACK` statement in MySQL is used to undo transactions that have not yet been committed to the database. It allows you to revert the database to the last committed state, ensuring data integrity in case of errors or unintended changes.

Usage

`ROLLBACK` is utilized within transaction control to cancel a transaction and restore the database to its previous state. This statement is particularly useful when an error occurs during a transaction, allowing you to reverse any changes made after the last `COMMIT`. Transactions are sequences of operations performed as a single logical unit of work, and `COMMIT` is used to make all changes permanent, whereas `ROLLBACK` undoes them.

ROLLBACK;

In this syntax, `ROLLBACK` simply reverts all operations performed in the current transaction. Note that `ROLLBACK` is only applicable in storage engines that support transactions, such as InnoDB, and is not applicable in non-transactional storage engines like MyISAM.

Examples

1. Basic Rollback

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
ROLLBACK;

In this example, the `UPDATE` operation is reverted, so the `balance` remains unchanged due to the `ROLLBACK`.

2. Rollback After Error

START TRANSACTION;
INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 2, 3);
-- Error: Duplicate entry for key 'PRIMARY'
ROLLBACK;

Here, a transaction is started, but an error occurs during the `INSERT` operation. The `ROLLBACK` statement ensures no partial data is saved.

3. Conditional Rollback

START TRANSACTION;
UPDATE inventory SET stock = stock - 10 WHERE product_id = 5;
IF (SELECT stock FROM inventory WHERE product_id = 5) < 0 THEN
  ROLLBACK;
END IF;

This example uses a conditional statement to `ROLLBACK` only if the resulting stock becomes negative, ensuring logical data consistency. Note that MySQL's procedural constructs like `IF... THEN... END IF` require handlers or stored procedures/functions, which might not be directly executable in a standard SQL script without additional context.

Tips and Best Practices

  • Use with Transactions. Always use `ROLLBACK` within a transaction block to ensure its effectiveness.
  • Check Conditions. Before rollback, verify conditions to avoid unnecessary operations, ensuring efficient resource usage.
  • Combine with Savepoints. Use `SAVEPOINT` for more granular control, allowing partial rollbacks within transactions with `ROLLBACK TO SAVEPOINT`.
  • Monitor Performance. Frequent rollbacks can impact performance; ensure transactions are well-planned to minimize rollbacks.
  • Data Consistency Impact. Improper use of rollbacks can affect data consistency. Carefully manage transactions to avoid unintended data loss.
  • Irreversibility Warning. Once `ROLLBACK` is executed, the changes are irreversibly undone. Make sure that this operation is intentionally performed.