Cursor Behavior with COMMIT/ROLLBACK
One of the most important concepts in database programming is the transaction or the unit of work (UOW), a set of one or more SQL statements that execute as a single operation. In order for the transaction to complete properly, all SQL statements in it must execute without errors. If even one part of an atomic transaction fails, then the entire transaction must fail. Proper transaction control using COMMIT and/or ROLLBACK is critical for guaranteeing data integrity.
In DB2, the COMMIT and ROLLBACK statements are supported inside SQL procedures. When you use COMMIT or ROLLBACK statements with cursors, the behavior of the cursor depends on whether or not it is declared using the WITH HOLD clause. Figure 5.12 is an enhanced version of the syntax diagram presented in Figure 5.1 for DECLARE CURSOR that includes the WITH HOLD clause. The WITH RETURN clause is used to pass the result set defined by the cursor to an application or another procedure. The TO CALLER and TO CLIENT clauses are meaningful only in the context of nested SQL procedures that will be discussed in Chapter 8, "Nested SQL Procedures."
Figure 5.12. A syntax diagram for DECLARE CURSOR.
>>-DECLARE--cursor-name--CURSOR----+------------+---------------> '-WITH HOLD--' >-----+------------------------------+--------------------------> | .-TO CALLER--. | '-WITH RETURN--+------------+--' '-TO CLIENT--' >----FOR--+-select-statement-+--------------------------------->< '-statement-name---'
Differences in the DECLARE CURSOR statement for iSeries are discussed at the end of the chapter.
The simplified syntax diagram for DECLARE CURSOR is shown in Figure 5.12. If the cursor is not declared using the WITH HOLD clause, all of its resources (cursor, locks, and large-object datatype, or LOB, locators) are released upon either COMMIT or ROLLBACK. Therefore, if you need to use the cursor after completing a transaction, you will have to re-open the cursor and traverse it again from the first row. Defining a cursor using WITH HOLD will cause the cursor to maintain its position and some locks across transactions. You should understand that only locks which are used to protect the current cursor position are held.
DB2 for iSeries has additional cursor functionality that is described in more detail at the end of the chapter.
A lock is a database object that is used to control how multiple applications can access the same resource. A LOB locator is a 4-byte value stored in a host variable that a program can use to refer to a LOB value held in the database system.
For cursors defined WITH HOLD after COMMIT:
For cursors defined WITH HOLD after ROLLBACK:
To better explain behavior of a WITH HOLD cursor, let's look at the example in Figure 5.13.
Figure 5.13. An example of the cursor behavior on COMMIT/ROLLBACK.
CREATE PROCEDURE update_department ( ) LANGUAGE SQL SPECIFIC upd_dept -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries ud: BEGIN -- Declare variable DECLARE v_deptno CHAR(3); -- Declare returncode DECLARE SQLSTATE CHAR(5); DECLARE c_dept CURSOR WITH HOLD FOR SELECT deptno FROM department FOR UPDATE OF location; -- Declare condition handler DECLARE CONTINUE HANDLER FOR SQLSTATE '24504', SQLSTATE '24501' L1: LOOP -- (1) LEAVE L1; END LOOP; -- Procedure logic OPEN c_dept; FETCH FROM c_dept INTO v_deptno; -- (2) UPDATE department SET location='FLOOR1' WHERE CURRENT OF c_dept; -- (3) COMMIT; -- (4) FETCH FROM c_dept INTO v_deptno; -- (5) COMMIT; -- (6) UPDATE department SET location='FLOOR2' WHERE CURRENT OF c_dept; -- (7) FETCH FROM c_dept INTO v_deptno; -- (8) UPDATE department SET location='FLOOR3' WHERE CURRENT OF c_dept; -- (9) COMMIT; -- (10) FETCH FROM c_dept INTO v_deptno; -- (11) UPDATE department SET location='FLOOR4' WHERE CURRENT OF c_dept; -- (12) ROLLBACK; -- (13) FETCH FROM c_dept INTO v_deptno; -- (14) UPDATE department SET location='FLOOR5' WHERE CURRENT OF c_dept; -- (15) CLOSE c_dept; RETURN 0; END ud
For platform compatibility, the LEAVE control statement has been placed in the condition handler on Line (1). DB2 for iSeries and DB2 for zSeries cannot have empty condition handlers. The handler is not doing anything but allowing the procedure to continue.
The update_department procedure mixes five FETCH/UPDATE blocks with COMMIT and ROLLBACK statements to show their effect on transactions. It queries and updates the department table. Figure 5.14 shows the first five rows of the two relevant columns in the department table, before the execution of the procedure.
Figure 5.14. A partial table DEPARTMENT before the execution of update_department.
DEPTNO LOCATION ------ -------- A00 - B01 - C01 - D01 - D11 -
Figure 5.15 shows the same five rows after the update_department procedure is invoked. Only two rows are updated.
Figure 5.15. A partial table DEPARTMENT after the execution of update_department.
DEPTNO LOCATION ------ -------- A00 FLOOR1 B01 - C01 FLOOR3 D01 - D11 -
Here is an analysis of the sequence of execution. After the cursor was declared with the FOR UPDATE clause, it was opened so that the cursor was positioned before the first row. The first block of FETCH and UPDATE on Lines (2) and (3) ran without a problem. Line (4) includes a COMMIT statement. Because the cursor was declared using the WITH HOLD clause, it remained open and was positioned before the second row for the next UOW, Lines (5) and line (6). The lock on the first row has been released, and the location field in the first row has been updated to "FLOOR1".
The FETCH statement on Line (5) was successful. However, because of the COMMIT statement on Line (6), the cursor was no longer positioned at the second row, it was positioned before the third row, which has not been fetched at this point. The UPDATE statement on Line (7) failed because the cursor was not positioned on any rows. The cursor was between the second and third rows. It would have generated the following SQL error if the error handling is not in place:
SQL0508N The cursor specified in the UPDATE or DELETE statement is not positioned on a row. SQLSTATE=24504
Even though error handling has not yet been covered, (see Chapter 6, "Condition Handling"), the example uses it to allow all the procedure to run through to the end. If the handler did not exist, the procedure would terminate at the first error and roll back changes up to the last COMMIT point.
The next two sets of FETCH and UPDATE statements on Lines (8), (9), (11), and (12) ran successfully, and the third and fourth rows were updated.
Line (13) is a ROLLBACK statement. It can only roll back changes made since the last COMMIT statement. So the change made to the location field on the fourth row was set back to the original null value. But the change to the third row was not affected because of the COMMIT statement on Line (10).
After the ROLLBACK statement, the cursor was closed and all locks were released. The FETCH statement on Line (14) would have caused the following error, which was also caught by the error-handling block. The UPDATE statement on Line (15) failed as well.
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open. SQLSTATE=24501
If the cursor had not been declared using the WITH HOLD clause, all the FETCH and UPDATE statements from Line (5) through (15) would have failed with the previous SQL error because the cursor would be closed implicitly by either a COMMIT or ROLLBACK statement.
Save Points Within Procedures
As the SQL PL has become more robust, developers have increased the amount of program logic they execute at the database level. This has led to very large stored procedures and transactions. With a very large transaction, it is likely that if one part of the stored procedure fails, you don't want to have the entire unit of work rolled back. Save points can be used to save interval points where the transaction can be rolled back to.
A save point is an indicator in a stored procedure that marks a point within a transaction. You can then use the save point to control how locking and transactions are handled if a rollback occurs. The syntax of a save point is shown in Figure 5.16.
Figure 5.16. A SAVEPOINT syntax diagram.
>>- SAVEPOINT--savepoint-name----+--------+---------------------> '-UNIQUE-' >--ON ROLLBACK RETAIN CURSORS--+--------------------------+------>< '-ON ROLLBACK RETAIN LOCKS-'
The savepoint-name field cannot begin with 'SYS' or you will receive a SQLSTATE 42939 error. The UNIQUE option indicates that the save point name will not be reused while the save point is active. The ON ROLLBACK RETAIN CURSORS clause in the statement causes your cursors to be retained after a rollback occurs. An additional option, ON ROLLBACK RETAIN LOCKS, is needed to keep from losing your locks on ROLLBACK.
When you are using save points, they will be implicitly released when a SQL statement, utility, or DB2 command issues a COMMIT statement. Save points will also affect the use of buffered inserts. The insert buffer will be implicitly flushed when a SAVEPOINT, ROLLBACK, or RELEASE TO SAVEPOINT command is issued.
You can use more than one save point within your transaction. Each save point can be placed after another save point by using a different name. This allows you to nest save points within each other and have more flexibility regarding from where you would like the rollback to continue. A pseudo-code example of using save points is shown in Figure 5.17.
Figure 5.17. A SAVEPOINT example.
savepoint A; Do program logic; savepoint B; Do more program logic; savepoint C; Do even more program logic;
Once you have set one or more save points, you can now have the option of rolling back to a specific save point. You can think of the nested save points as different levels. If you roll back to a save point at the bottom level, which is the one that was issued last, then all of the save points which are before it are still active. You can still roll back to the earlier save points even after issuing the ROLLBACK statement. The syntax is given in Figure 5.18.
Figure 5.18. The ROLLBACK TO SAVEPOINT syntax.
ROLLBACK TO SAVEPOINT savepoint-name
The example in Figure 5.19 illustrates how multiple save points can be rolled back. The first ROLLBACK command on Line (1) will return the transaction data to the point at save point c. The second ROLLBACK command on Line (2) will then return the data to how it was in the transaction at save point a. The third ROLLBACK on Line (3) will fail since save point b will no longer exist. This is because it was created after the transaction point marked by save point a. Because it was at a later nested level, the save point has been lost.
Figure 5.19. A ROLLBACK TO SAVEPOINT example.
savepoint a; Do program logic; savepoint b; Do more program logic; savepoint c; Do even more program logic; ROLLBACK TO SAVEPOINT c; (1) Do some new logic; ROLLBACK TO SAVEPOINT a; (2) Do some more logic; ROLLBACK TO SAVEPOINT b; (3)
With large transactions, nested save points can give you more flexibility in how you handle program or data errors. It is not recommended to use very complex nested save point logic because it makes the flow of your procedures difficult to understand. It can also lead to some unexpected behavior if someone else modifies your code and does not fully understand how you are using the nested save points. A detailed discussion and example of save points can be found in Chapter 10, "Leveraging DB2 Application Development Features."