MySQL Stored Procedure Programming

Working with Savepoints

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

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.

Example 8-3 demonstrates the use of a savepoint with a transaction that creates or updates a location record, and then creates or updates a departments 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 ...