SAVEPOINT

Use this MySQL statement to identify a point in a transaction to which SQL statements may potentially be undone later.

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).

SAVEPOINT identifier

Explanation

Use this MySQL statement to identify a point in a transaction to which MySQL statements may potentially be undone later. It's used in conjunction with the MySQL statement, ROLLBACK TO SAVEPOINT. It may be released with the MySQL statement, RELEASE SAVEPOINT. You can use any unreserved word to identify a savepoint and can create several savepoints 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.

Examples

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 (the /tmp/customer_orders.sql file), but not the client information that was first imported (the /tmp/customer_info.sql file), the following statement could be entered:

ROLLBACK TO SAVEPOINT orders_import;

If the MySQL administrator decides that the customer information that was imported also has problems, the MySQL statement, ROLLBACK can be issued to undo the entire transaction.

As of version 5.0.17 of MySQL, if a stored function or trigger is used, a new savepoint level is set up and the previous savepoints are suspended. When the stored function or trigger is finished, any savepoints it created are released and the original savepoint level resumes.