Commit, Rollback, and Savepoint Operations
| More than one application process might request access to the same data at the same time. Locking is used to maintain data integrity under such conditions, preventing, for example, two application processes from updating the same row of data simultaneously. For more information, refer to Chapter 16. DB2 implicitly acquires locks to prevent uncommitted changes made by one application process from being perceived by any other. DB2 implicitly releases all locks it has acquired on behalf of an application process when that process ends, but an application process can also explicitly request that locks be released sooner. Commit and Rollback OperationsA commit operation releases locks acquired by the application process and commits database changes made by the same process. Two good reasons to commit are to improve concurrency and to prevent massive, long-running rollbacks when abnormal terminations occur. Even programs that have no concurrency problems should do a commit at least every 10 minutes, as a general rule. If you design a good batch program, commit processing is part of it. If you do commit processing, you have to identify the unit of work (UOW) in your program and also design the batch program to be restartable. When concurrency is an issue, you probably want a commit frequency between 2 seconds and 20 seconds. A good practice is to make sure that the commit frequency can be influenced from the outside. In your program, identify a logical UOW and execute it x number of times before doing a commit. The magic number x could come from a control card to your program or from a heuristic control table. This way, programs that cause concurrency problems can be tuned, and you can also have separate settings depending on the time of day. It is a good idea to have the same design in all your programs. Software solutions from independent software vendors (ISVs) take care of all commit and restart problems. It is not good practice to do intermittent committing in online transactions rather than commit at the end of the transaction. When you have a long-running, or background, transactionprinting, for exampleyou should design your transaction such that it will reschedule itself and then terminate, doing an implicit commit. This way, all resources are released, and the transaction server can shut down in an orderly way when needed. DB2 also provides a way to back out uncommitted changes made by an application process. This might be necessary if an application process fails or is in a deadlock situation. An application process, however, can explicitly request that its database changes be backed out. This operation is called rollback. DB2 creates undo log records for rollbacks and redo log records for recovery for every row that is changed. DB2 also logs these records for changes to all affected indexes. The interface an SQL program uses to explicitly specify these commit and rollback operations depends on the environment. If the environment can include recoverable resources other than DB2 databases, the SQL COMMIT and ROLLBACK statements cannot be used. Thus, these statements cannot be used in IMS, CICS, or WebSphere environments. Unit of WorkA UOW, sometimes called a logical UOW, is a recoverable sequence of operations within an application process. At any time, an application process has a single UOW, but the life of an application process can involve many UOWs as a result of commit or full-rollback operations. A UOW is initiated when an application process is initiated. A UOW is also initiated when the previous UOW is ended by something other than the end of the application process. A UOW is ended by a commit operation, a full-rollback operationeither explicitly initiated by the application process or implicitly initiated by DB2or the end of an application process. A commit or rollback operation affects only the changes made to recoverable resources within the UOW. While changes to datavia insert, update, or deleteremain uncommitted, other application processes are unable to perceive them unless they are running with an isolation level of uncommitted read. The changes can still be backed out by a rollback. Once committed, these database changes are accessible by other application processes and can no longer be backed out by a rollback. Locks acquired by DB2 on behalf of an application process to protect uncommitted data are held at least until the end of a UOW. The initiation and termination of a UOW define points of consistency within an application process. A point of consistency is a claim by the application that the data is consistent. For example, a banking transaction might involve the transfer of funds from one account to another. Such a transaction would require that these funds be subtracted from the first account and added to the second. Following the subtraction step, the data is inconsistent. Consistency is reestablished only after the funds have been added to the second account. When both steps are complete, the commit operation can be used to end the UOW, thereby making the changes available to other application processes. Unit of RecoveryA DB2 unit of recovery (UR) is a recoverable sequence of operations executed by DB2 for an application process. If a UOW involves changes to other recoverable resources, such as VSAM or MQSeries, the UOW will be supported by those URs. If relational databases are the only recoverable resources used by the application process, the scope of the UOW and the UR is the same, and either term can be used. Rolling back WorkDB2 can back out all changes or only selected changes made in a UR. Only backing out all changes results in a point of consistency. Without the TO SAVEPOINT clause, the SQL ROLLBACK statement causes a full-rollback operation. If such a rollback operation is successfully executed, DB2 backs out uncommitted changes to restore the data consistency that it assumes existed when the UOW was initiated. That is, DB2 undoes the work. SavepointsA savepoint enables milestones within a transaction or UR to be bookmarked. An external savepoint represents the state of the data and schema at a particular time. After the savepoint is set, changes made to data and schemas by the transaction can be rolled back to the savepoint, as application logic requires, without affecting the overall outcome of the transaction: EXEC SQL SAVEPOINT name (other options) END-EXEC. EXEC SQL ROLLBACK TO SAVEPOINT name END-EXEC. ROLLBACK can be used to restore to a savepoint. This is useful when a point has been reached during a unit of work and there is a need to back out without undoing the entire unit of work. Individual savepoints can be named, and then ROLLBACK can be used to roll back work to whichever point is required, based on the application-processing requirements, skipping over individual savepoints, if necessary. If there are outstanding savepoints, access to a remote database management systemvia DRDA or private protocol, using aliases or three-part namesis not permitted, because the scope of a savepoint is within the database management system on which it was set. DRDA access using a CONNECT statement is allowed; however, the savepoints are local to their site. DB2 does not restrict the use of aliases and three-part names to connect to a remote site when there are outstanding savepoints at the remote site. But this is not recommended. There is no limit to the number of savepoints that can be set. Establishing a SavepointIn order to set a savepoint, you use the SAVEPOINT statement. You must choose the name for the savepoint, so the name can be meaningful, if you wish. Application logic will determine whether the savepoint name needs to be reused as the application progresses or needs to denote a unique milestone. You can specify the UNIQUE option on the SAVEPOINT statement if you do not intend for the name to be reused. This will prevent an invoked procedure from unintentionally reusing the name. If a savepoint is coded in a loop, however, there is no choice; do not use UNIQUE. If the name of the savepoint identifies a savepoint that already exists within the UR and the savepoint was not created with the UNIQUE option, the existing savepoint is destroyed and a new one created. Destroying a savepoint by reusing its name for another savepoint is not the same as releasing the savepoint. Reusing a savepoint name destroys only the one savepoint. Releasing a savepoint releases the named savepoint and all savepoints that were subsequently set. (Releasing savepoints is described later.) The following statement shows an example of setting a unique savepoint named START_AGAIN. After executing this statement, the application program needs to check the SQL return code to verify that the savepoint was set: EXEC SQL SAVEPOINT START_AGAIN UNIQUE ON ROLLBACK RETAIN CURSORS END-EXEC. The SAVEPOINT statement sets a savepoint within a UR. This statement, as well as the ROLLBACK and RELEASE statements, can be embedded in application programs, external user-defined functions, stored proceduresthat are defined as MODIFIES SQL DATAor issued interactively. It cannot be issued from the body of a trigger. It is an executable statement that can be dynamically prepared only if DYNAMICRULES(RUN) behavior is implicitly or explicitly in effect. The syntax is as follows: EXEC SQL SAVEPOINT svptname UNIQUE ON ROLLBACK RETAIN CURSORS ON ROLLBACK RETAIN LOCKS END-EXEC. Table 12-1 provides a brief description of each part of the SAVEPOINT syntax.
Restoring to a SavepointIn order to restore to a savepoint, the ROLLBACK statement is used with the TO SAVEPOINT clause. The next example shows pseudocode for an application that sets and restores to a savepoint. This IBM-supplied example application makes airline reservations on a preferred date and then makes hotel reservations. If the hotel is unavailable, the application rolls back the airline reservations and repeats the process for a next-best date. Up to three dates are tried: EXEC SQL SAVEPOINT START_AGAIN UNIQUE ON ROLLBACK RETAIN CURSORS; Check SQL code; Do i = 1 to 3 UNTIL got_reservation; Book_Air (dates(i),ok); If ok then Book_Hotel(dates(i),ok); If ok then got_reservations Else EXEC SQL ROLLBACK TO START_AGAIN; End loop; EXEC SQL RELEASE SAVEPOINT START_AGAIN; The ROLLBACK statement with the new TO SAVEPOINT option backs out data and schema changes that were made after a savepoint. This can be embedded. The skeleton syntax is as follows: EXEC SQL ROLLBACK WORK TO SAVEPOINT svptname END-EXEC. In the skeleton syntax, ROLLBACK WORK rolls back the entire UR. All savepoints that were set within the UR are released. TO SAVEPOINT specifies that the rollback of the UR occurs only to the specified savepoint. If no savepoint name is specified, rollback is to the last active savepoint; svptname is the name of the savepoint to roll back to. In the following example, the ROLLBACK TO SAVEPOINT statement will cause the rollback to savepoint two, which will cause the second and third sets of application code to be rolled back: EXEC SQL SAVEPOINT ONE ON ROLLBACK RETAIN CURSORS; END-EXEC First application code set .. EXEC SQL SAVEPOINT TWO ON ROLLBACK RETAIN CURSORS; END-EXEC. Second application code set.. EXEC SQL SAVEPOINT THREE ON ROLLBACK RETAIN CURSORS; END-EXEC. Third application code set.. EXEC SQL RELEASE SAVEPOINT THREE; END-EXEC. EXEC SQL ROLLBACK TO SAVEPOINT; END-EXEC. If the named savepoint does not exist, an error will occur. Data and schema changes made after the savepoint was set are backed out. Because changes made to global temporary tables are not logged, they are not backed out, but a warning is issued. A warning is also issued if the global temporary table is changed and there is an active savepoint. None of the following items are backed out:
Savepoints that are set after the one to which rollback is performed are released. The savepoint to which rollback is performed is not released. For example, in the following scenario, the ROLLBACK TO SAVEPOINT TWO statement causes savepoint three to be released but not savepoint two: EXEC SQL SAVEPOINT ONE ON ROLLBACK RETAIN CURSORS; END-EXEC. First application code set .. EXEC SQL SAVEPOINT TWO ON ROLLBACK RETAIN CURSORS; END-EXEC. Second application code set.. EXEC SQL SAVEPOINT THREE ON ROLLBACK RETAIN CURSORS; END-EXEC. Third application code set.. EXEC SQL ROLLBACK TO SAVEPOINT TWO; END-EXEC. Releasing a SavepointReleasing a savepoint involves the use of the RELEASE SAVEPOINT statement. You cannot roll back to a savepoint after it is released. Only a small amount of overhead is needed to maintain savepoints, but it is more important to release them, because any outstanding savepoints will block any system-directed connections to remote locations. After an application no longer needs to roll back to that savepoint, you should release it. The following example releases a savepoint named START_AGAIN and all the savepoints that were subsequently set by the transaction: EXEC SQL RELEASE SAVEPOINT START_AGAIN; END-EXEC. The RELEASE SAVEPOINT statement releases the named savepoint and any subsequently established savepoints. Once a savepoint has been released, it is no longer maintained, and rollback to the savepoint is no longer possible. The syntax is as follows: EXEC SQL RELEASE TO SAVEPOINT svptname END-EXEC. The svptname is the savepoint identifier that identifies the savepoint to be released. If there is no named savepoint, an error will occur. The named savepoint and all the savepoints that were subsequently established by the transaction are released. Savepoints in a Distributed EnvironmentIn a distributed environment, you can set savepoints only if you use DRDA access with explicit CONNECT statements. If you set a savepoint and then execute an SQL statement with a three-part name, an SQL error occurs. The site at which a savepoint is recognized depends on whether the CONNECT statement is executed before or after the savepoint is set. For example, if an application executes the statement SET SAVEPOINT C1 at the local site before executing a CONNECT TO S1 statement, savepoint C1 is known only at the local site. If the application executes CONNECT TO S1 before SET SAVEPOINT C1, the savepoint is known only at site S1. For more information on the CONNECT statement, refer to Chapter 10. |
EAN: 2147483647
Pages: 175