Name
SAVEPOINT
Synopsis
SAVEPOINT identifier
Use this
statement in conjunction with the ROLLBACK TO
SAVEPOINT
statement to identify a point in a transaction to
which SQL statements may potentially be undone later. You can use any
unreserved word to identify a save point and create several save
points during a transaction. If an additional
SAVEPOINT
statement is issued with the same name,
the previous point will be replaced with the new point for the name
given.
START TRANSACTION; LOCK TABLES orders WRITE; INSERT DATA INFILE '/tmp/customer_info.sql' INTO TABLE orders; SAVEPOINT orders_import; INSERT DATA INFILE '/tmp/customer_orders.sql' INTO TABLE orders;
At this point in this example, the administrator can check the results of the orders imported before committing the transactions. If the administrator decides that the orders imported have problems, but not the client information that was first imported, the following statement could be entered:
ROLLBACK TO SAVEPOINT orders_import;
If the
administrator also decides that the customer information that was
imported also has problems, the ROLLBACK
statement
can be issued to undo the entire transaction.
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.