ROLLBACK

Use this MySQL statement with transactional tables to reverse transactions that have not yet been committed.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

Explanation

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 it with the execution of the COMMIT statement, the ending of current session, and several other statements that imply that a commit is desired. See the explanation of COMMIT 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 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.

Examples

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 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 MySQL statement, COMMIT would be issued to commit the transactions, instead of the MySQL statement, ROLLBACK shown here. In this case, a problem led 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 MySQL statement, COMMIT, as well as with several other implicit commit statements. See the explanation of COMMIT in our MySQL documentation for a list of statements that imply a commit.