MySQL in a Nutshell

Name

ROLLBACK

Synopsis

ROLLBACK

Use this statement with an InnoDB or BDB table to reverse transactions that have not yet been committed. If AUTOCOMMIT is enabled, it must be disabled for this statement to be meaningful. To do this, set the value of AUTOCOMMIT to 0 with the SET statement. You can also disable AUTOCOMMIT with the START TRANSACTION statement and reinstate it with the COMMIT statement.

Here is an example of this statement’s use:

START TRANSACTION;
LOCK TABLES orders WRITE;
INSERT DATA INFILE '/tmp/customer_orders.sql'
  INTO TABLE orders;
SELECT ...;
ROLLBACK;
UNLOCK TABLES;

In this example, after the batch of orders was inserted into the orders table, the administrator manually enters a series of SELECT statements (not shown) to check the integrity of the data. If everything seems alright, the COMMIT statement would be issued instead of the ROLLBACK statement shown here, to commit the transactions. If there is a problem, though, the ROLLBACK statement could be issued as shown here. It would remove the data imported by the INSERT DATA INFILE statement.

The ROLLBACK statement works only with InnoDB and BDB tables. A rollback will not undo the creation or deletion of databases. It also cannot be performed on changes to table schema (i.e., ALTER TABLE, CREATE TABLE, or DROP TABLE statements).

Transactions cannot be reversed with the ROLLBACK statement if they have been committed. Commits are caused by the COMMIT statement, as well as with the following statements: ALTER TABLE, ...