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
, ...
Get MySQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.