MySQL DML Commands: Rollback Concepts Explained

What is Rollback in MySQL?

Rollback is a command used to revert all changes made during the current transaction. It cancels the transaction and restores the database to its previous state before the transaction began.

This is especially useful when an error occurs or when you decide not to save the changes made during a transaction.

• Undo all uncommitted changes.
• Restore data to the last committed state.
• Used within transactional storage engines like InnoDB.

How Rollback Works with Transactions

Transactions group multiple SQL statements into a single logical unit. You can either commit all changes or rollback all changes if something goes wrong.

The typical transaction flow involves starting a transaction, executing DML commands, and then either committing or rolling back.

• START TRANSACTION or BEGIN to start a transaction.
• Execute INSERT, UPDATE, DELETE commands.
• Use COMMIT to save changes permanently.
• Use ROLLBACK to undo changes if needed.

Example of Using Rollback in MySQL

Here is a simple example demonstrating rollback in a MySQL transaction.