Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement with transactional tables to reverse transactions that have not yet been committed. Transaction statements are currently supported by the InnoDB, NDB Cluster, and BDB storage engines and are ignored if used with MyISAM tables.

If AUTOCOMMIT is enabled, it must be disabled for this statement to be meaningful, which can be done as follows:

SET AUTOCOMMIT = 0;

AUTOCOMMIT is also disabled when a transaction is started with the START TRANSACTION statement. It is reinstated with the execution of the COMMIT statement, the ending of the current session, and several other statements that imply that a commit is desired. See the explanation of COMMIT earlier in this chapter for a list of statements that imply a commit.

The WORK keyword is optional and has no effect on the results. It’s available for compatibility with its counterparts, BEGIN WORK and COMMIT WORK. Use the AND CHAIN clause to indicate that the transaction is to be rolled back and another is starting, thus making it unnecessary to execute the START TRANSACTION statement again. Use the AND RELEASE clause to end the current client session after rolling back the transaction. Add the keyword NO to indicate explicitly that a new transaction is not to begin (when used with CHAIN) or the client session is not to end (when used with RELEASE)—these are the default settings, though. It’s necessary to specify NO only when the system variable completion_type is set to something other than the default setting.

Here is an example of this statement’s use in context:

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 is 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 all right, the COMMIT statement would be issued to commit the transactions, instead of the ROLLBACK statement shown here. In this case, a problem leads the administrator to issue ROLLBACK to remove the data imported by the INSERT DATA INFILE statement.

A rollback will not undo the creation or deletion of databases. It also cannot be performed on changes to table schema (e.g., 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 several other implicit commit statements. See the explanation of COMMIT for a list of statements that imply a commit.