Commit, Rollback, and Savepoint

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Chapter 7.  Application Program Features

Commit, Rollback, and Savepoint

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, "Locking and Concurrency."

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

A commit operation releases locks acquired by the application process and commits database changes made by the same process. There are two good reasons to commit:

  • Improve concurrency.

  • Prevent massive, long-running rollbacks when abnormal terminations occur.

Even programs that have no concurrency problems should at least do a commit 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 such that it is restartable.

When concurrency is an issue, you probably want a commit frequency somewhere between 2 seconds and 20 seconds. A very good practice is to make sure the commit frequency can be influenced from the outside. Identify a logical UOW in your program and execute this 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 could also have separate settings depending on the time of day. It is a good idea to have the same design in all of your programs. There are software solutions available from the independent software vendors (ISVs) that 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 (background) transaction (printing, for example), you 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 in the event of a failure on the part of an application process, or in a deadlock situation. An application process, however, can explicitly request that its database changes be backed out. This operation is called rollbac k. 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 used by a SQL program 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 information management system (IMS), CICS (Customer Information Control System), or WebSphere environments.

Unit of Work

A 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 operation (either explicitly initiated by the application process or implicitly initiated by DB2), or the end of an application process. A commit or rollback operation affects only the changes made to recoverable resources within the UOW.

While changes (via insert, update, or delete) to data remain 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. Only after the funds have been added to the second account is consistency reestablished. 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 Recovery

A 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 (e.g., VSAM or MQSeries), the UOW will be supported by those URs. If relational databases are the only recoverable resources used by the application process, then the scope of the UOW and the UR are the same, and either term can be used.

Rolling Back Work

DB2 can back out all changes or only selected changes made in a UR. Only backing out all changes results in a point of consistency.

The SQL ROLLBACK statement without the TO SAVEPOINT clause specified 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.

Savepoints

A savepoint enables milestones within a transaction or UR. An external savepoint represents the state of the data and schema at a particular point in 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 TEST516 (  other options  ) END-EXEC. EXEC SQL ROLLBACK TO SAVEPOINT name END-EXEC. 

ROLLBACK can be used to restore to a savepoint. This would be 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 system (via DRDA or private protocol using aliases or three-part names) is 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 the number of savepoints that can be set.

Establishing a Savepoint

In order to set a savepoint, you use the SAVEPOINT statement. You can choose the name for the savepoint, so the name can be meaningful if you wish. Application logic will determine if the savepoint name needs to be reused as the application progresses or that the savepoint name 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 savepoint is destroyed and a new savepoint is 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-EXCE. 

The SAVEPOINT statement sets a savepoint within a UR. This statement as well as the ROLLBACK and RELEASE statements can be imbedded in application programs, external user -defined functions, stored procedures (that is, defined as MODIFIES SQL DATA), or 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 7-1 provides a brief description of each part of the SAVEPOINT syntax.

Table 7-1. Savepoint Syntax

Syntax

Description

svptname

Savepoint identifier that names the savepoint.

UNIQUE

Specifies that the application program cannot reuse this savepoint name within the UR. An error occurs if a savepoint with the same name as svptname already exists within the UR. If you do not use UNIQUE, then the application can reuse this savepoint name within the UR.

ON ROLLBACK RETAIN CURSORS

This option specifies that any cursors opened after the savepoint is set are not tracked, and thus are not closed upon rollback to the savepoint. Even though these cursors do remain open after rollback to the savepoint, they may not be useable.

ON ROLLBACK RETAIN LOCKS

This option specifies that any locks that are acquired after the savepoint is set are not tracked and therefore are not released upon rollback to the savepoint.

Restoring to a Savepoint

In order to restore to a savepoint, the ROLLBACK statement is used with the TO SAVEPOINT clause. The example below shows pseudocode for an application that sets and restores to a savepoint. The example (an IBM-supplied example) application makes airline reservations on a preferred date, and then makes hotel reservations. If the hotel is unavailable, the application then 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 imbedded. The skeleton syntax is as follows:

 EXEC SQL  ROLLBACK WORK TO SAVEPOINT s  vptname  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 there is no savepoint name 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:

  • opening or closing of cursors

  • changes in cursor positioning

  • acquisition and release of locks

  • caching of the rolled back statements

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  a  pplication code set ..  EXEC SQL SAVEPOINT TWO ON ROLLBACK RETAIN CURSORS; END-EXEC.  Second  a  pplication 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 Savepoint

Releasing a savepoint involves the use the RELEASE SAVEPOINT statement. You cannot rollback to a savepoint after it is released. There is just a small amount of overhead to maintain a savepoint, but it is more important to release them because any outstanding savepoints will block any system-directed connections to remote locations. After it is no longer required for an application 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 Environment

In 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 a SQL statement with a three-part name, a 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 it executes 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, "Accessing Distributed Data."


Team-Fly    
Top


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net