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:

• The insert into the AUDIT_LOG table on line 46 will, indeed, be rolled back when the department cannot be inserted. However, the overhead of inserting and then rolling back that insert might not be trivial in a high-throughput environment.
• The execution flow of the transaction is unclear. The rollback is defined in the handler on line 43, but actually will be triggered only when the insert fails on line 49. It is hard to tell just by looking at the INSERT statement what will happen, making it difficult to understand the overall logic of the transaction. It is, quite simply, more complicated than necessary.

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.