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:
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 ...
Get MySQL Stored Procedure Programming 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.