ROLLBACK - MySQL

Overview

ROLLBACK reverses all database changes made within the current transaction and returns the database to the state it was in before the transaction began. Transactions in MySQL begin with a START TRANSACTION or BEGIN statement and end with either a COMMIT or ROLLBACK.

Syntax

ROLLBACK

Examples

• Roll back all changes within the current transaction:

START TRANSACTION;
-- Perform database modifications
ROLLBACK;

• Roll back specific changes within a transaction using SAVEPOINT:

START TRANSACTION;
-- Perform database modifications
SAVEPOINT my_savepoint;
-- More database modifications
ROLLBACK TO SAVEPOINT my_savepoint;
-- Further database modifications
COMMIT;

Common Issues

• Transaction not started: Trying to execute ROLLBACK without starting a transaction will result in an error. Ensure you initiate a transaction with START TRANSACTION before using ROLLBACK.
• Statements after ROLLBACK: Any SQL statements executed after ROLLBACK will not be rolled back. Be cautious of this when using ROLLBACK within a script or stored procedure.

Integration

ROLLBACK is typically used alongside COMMIT to manage database transactions effectively. Transactions ensure data integrity by ensuring all changes within a transaction are applied or none at all.

Related Commands

• START TRANSACTION: Initiates a new transaction.
• COMMIT: Finalizes and applies all changes within a transaction.
• SAVEPOINT: Creates a named point within a transaction, allowing for partial rollbacks.