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 sp_location_exists INT DEFAULT 0; 9 DECLARE duplicate_dept INT DEFAULT 0; 10 11 12 START TRANSACTION; 13 14 -- Does the location exist? 15 SELECT COUNT(*) 16 INTO location_exists 17 FROM locations 18 WHERE location=in_location; 19 20 IF location_exists=0 THEN 21 22 INSERT INTO AUDIT_LOG (audit_message) 23 VALUES (CONCAT('Creating new location',in_location)); 24 25 INSERT INTO locations (location,address1,address2,zipcode) 26 VALUES (in_location,in_address1,in_address2,in_zipcode); 27 ELSE 28 29 UPDATE locations set address1=in_address1, 30 address2=in_address2, 31 zipcode=in_zipcode 32 WHERE location=in_location; 33 34 END IF; 35 36 SAVEPOINT savepoint_location_exists; 37 38 BEGIN 39 DECLARE DUPLICATE_KEY CONDITION FOR 1062; 40 DECLARE CONTINUE HANDLER FOR DUPLICATE_KEY /*Duplicate key value*/ 41 BEGIN 42 SET duplicate_dept=1; 43 ROLLBACK TO SAVEPOINT savepoint_location_exists; 44 END; 45 46 INSERT INTO AUDIT_LOG (audit_message) 47 VALUES (CONCAT('Creating new department',in_department_name)); 48 49 INSERT INTO DEPARTMENTS (department_name,location,manager_id) 50 VALUES (in_department_name,in_location, in_manager_id); 51 52 IF duplicate_dept=1 THEN 53 54 UPDATE departments 55 SET location=in_location, 56 manager_id=in_manager_id 57 WHERE department_name=in_department_name; 58 END IF; 59 60 END; 61 62 COMMIT; 63 64 END;

Here is an explanation of this complex transaction logic:

Line(s)

Explanation

12

The START TRANSACTION statement denotes the start of the transaction. We can place this statement after our declarations, since they do not participate in any way in the transaction.

15

In this SQL statement we check to see if a matching location exists.

20-26

If the location does not exist (line 20), we insert an audit log record (lines 22-23) and then create the location (lines 25-26).

29-32

If the location already exists, we update it with new detail.

36

Whether or not the location existed in line 20, it definitely exists now, so we establish a savepoint indicating that we have gotten this much work done.

39-44

Define an error handler that will fire in the event of a duplicate key error. If the handler is invoked, it will issue a rollback to our savepoint and then set the duplicate_dept variable so that we can detect that the rollback has occurred. You will find more information about handler logic in Chapter 6.

46-50

Insert an audit record and then insert a new department. If a department already exists with this name, the handler will fire, setting the duplicate_dept variable and rolling back to the savepoint. This partial rollback will undo the audit log entry for the new department, but will preserve the inserts or update executed to ensure that the location existed.

52-58

Check the duplicate_dept variable to see if there was a problem inserting the department. If so, then update the existing DEPARTMENTS record with the new information.

Now that you have seen how to use the SAVEPOINT and ROLLBACK TO statements, we need to point out two undesirable side effects of this approach and then offer a restructuring of the program that renders savepoints unnecessary. These are the side effects:

We can rewrite this program to avoid the use of savepoints altogether (see Example 8-4). A hint of this approach was offered earlier in the procedure (lines 20-34): check to see if the record exists, then issue the INSERT or UPDATE as appropriate. The resulting logic is more straightforward, and actually reduces the number of SQL statements we need to code.

Example 8-4. Alternative to the SAVEPOINT implementation

CREATE PROCEDURE nosavepoint_example(in_department_name VARCHAR(30), in_location VARCHAR(30), in_address1 VARCHAR(30), in_address2 VARCHAR(30), in_zipcode VARCHAR(10), in_manager_id INT) BEGIN DECLARE location_exists INT DEFAULT 0; DECLARE department_exists INT DEFAULT 0; START TRANSACTION; -- Does the location exist? SELECT COUNT(*) INTO location_exists FROM locations WHERE location=in_location; IF location_exists=0 THEN INSERT INTO AUDIT_LOG (audit_message) VALUES (CONCAT('Creating new location',in_location)); INSERT INTO locations (location,address1,address2,zipcode) VALUES (in_location,in_address1,in_address2,in_zipcode); ELSE UPDATE locations set address1=in_address1, address2=in_address2, zipcode=in_zipcode WHERE location=in_location; END IF; -- Does the department exists? SELECT COUNT(*) INTO department_exists FROM departments WHERE department_name=in_department_name; IF department_exists=1 THEN UPDATE departments SET location=in_location, manager_id=in_manager_id WHERE department_name=in_department_name; ELSE INSERT INTO AUDIT_LOG (audit_message) VALUES (CONCAT('Creating new department',in_department_name)); INSERT INTO DEPARTMENTS (department_name,location,manager_id) VALUES (in_department_name,in_location, in_manager_id); END IF; COMMIT; END;

Savepoints can be used to partially roll back transactions in the event of an error. If you cannot achieve the same effect through the use of exception handlers and conditional logic, then savepoints may be required. Watch out for SAVEPOINT-based implementations, however, that result in unnecessary and unnecessarily complicated code.

One good use of savepoints is to implement "nested" transactions inside of discrete stored programs. You may with to implement a stored program that performs a small transaction, but you don't want a rollback in that program to abort any larger transaction that may be in progress. A savepoint is a good way to do this, since you can easily roll back only the statements that you have issued within the procedure. Example 8-5 shows a stored program that implements this approach.

Example 8-5. Example of a "nested" transaction using a savepoint

CREATE PROCEDURE nested_tfer_funds( in_from_acct INTEGER, in_to_acct INTEGER, in_tfer_amount DECIMAL(8,2)) BEGIN DECLARE txn_error INTEGER DEFAULT 0 ; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET txn_error=1; END; SAVEPOINT savepoint_tfer; UPDATE account_balance SET balance=balance-in_tfer_amount WHERE account_id=in_from_acct; IF txn_error THEN ROLLBACK TO savepoint_tfer; SELECT 'Transfer aborted '; ELSE UPDATE account_balance SET balance=balance+in_tfer_amount WHERE account_id=in_to_acct; IF txn_error THEN ROLLBACK TO savepoint_tfer; SELECT 'Transfer aborted '; END IF; END IF; END;

The program in Example 8-5 creates a savepoint before issuing any DML statements. Should any errors occur, the program issues a rollback to that savepoint to ensure that the DML statements issued by the programbut only those statementsare reversed.

Related

Категории