Savepoints allow you to perform a partial rollback of
the changes in your transaction. If you issue an unqualified
ROLLBACK, any and all changes in your
current session are erased. If, however, you place a
SAVEPOINT statement in your program, then
you can roll back to that point in your program (and your
transaction). In other words, any changes made before that statement
can still be saved to the database with a
Generally, savepoints are intended to allow you to recover from a statement-level error without having to abort and restart your transaction. In these circumstances, the transaction includes one or more statements that might fail, yet should not force the invalidation of the entire transaction. Usually you will want to roll back to a savepoint, as part of handling the error, and then take the appropriate action, as indicated by the particular error that was raised.
demonstrates the use of a savepoint with a transaction that creates or
location record, and then
creates or updates a
record that resides at that location:
Example 8-3. Example of a transaction that uses a savepoint
1 CREATE PROCEDURE savepoint_example(in_department_name VARCHAR(30), 2 in_location VARCHAR(30), 3 in_address1 VARCHAR(30), 4 in_address2 VARCHAR(30), 5 in_zipcode VARCHAR(10), 6 in_manager_id INT) 7 BEGIN 8 DECLARE location_exists INT DEFAULT 0; 9 DECLARE duplicate_dept INT DEFAULT 0; 10 11 12 START TRANSACTION; 13 14 -- Does the ...