All SQL programs execute as part of an application process. In OS/400, an application process is called a job. An application process is made up of one or more activation groups. Each activation group involves the execution of one or more programs. Programs run under a non-default activation group or the default activation group. All programs except those created by ILE compilers run under the default activation group.
For more information on activation groups, see the book ILE Concepts, SC41-5606-04.
An application process that uses commitment control can run with one or more commitment definitions. A commitment definition provides a means to scope commitment control at an activation group level or at a job level. At any given time, an activation group that uses commitment control is associated with only one of the commitment definitions.
A commitment definition can be explicitly started through the Start Commitment Control (STRCMTCTL) command. If not already started, a commitment definition is implicitly started when the first SQL statement is executed under an isolation level different than COMMIT(*NONE). More than one activation group can share a job commitment definition.
Figure 3 shows the relationship of an application process, the activation groups in that application process, and the commitment definitions. Activation groups A and B run with commitment control scoped to the activation group. These activation groups have their own commitment definitions. Activation group C does not run with any commitment control and does not have a commitment definition.
Figure 3. Activation Groups without Job Commitment Definition
|
Figure 4 shows an application process, the activation groups in that application process, and the commitment definitions. Some of the activation groups are running with the job commitment definition. Activation groups A and B are running under the job commitment definition. Any commit or rollback operation in activation group A or B affects both because the commitment control is scoped to the same commitment definition. Activation group C in this example has a separate commitment definition. Commit and rollback operations performed in this activation group only affect operations within C.
Figure 4. Activation Groups with Job Commitment Definition
 |
For more information on commitment definitions, see the book Backup and Recovery, SC41-5304-04.
Each commitment definition involves the execution of one of more units of work. A unit of work (also known as a logical unit of work and unit of recovery) is a recoverable sequence of operations. At any given time, a commitment definition has a single unit of work. A unit of work is started either when the commitment definition is started, or when the previous unit of work is ended by a commit or rollback operation.
A unit of work is ended by a commit operation, a rollback operation, or the ending of the activation group. A commit or rollback operation affects only the database changes made within the unit of work that the commit or rollback ends. While changes remain uncommitted, other activation groups using different commitment definitions running under isolation levels COMMIT(*CS), COMMIT(*RS), and COMMIT(*RR) cannot perceive the changes. The changes can be backed out until they are committed. Once changes are committed, other activation groups running in different commitment definitions can access them, and the changes can no longer be backed out.
Application processes and activation groups that use different commitment definitions can request access to the same data at the same time. Locking is used to maintain data integrity under such conditions. Locking prevents such things as two application processes updating the same row of data simultaneously.
The database manager acquires locks to keep the uncommitted changes of one activation group undetected by activation groups that use a different commitment definition. Object locks and other resources are allocated to an activation group. Row locks are allocated to a commitment definition.
When an activation group other than the default activation group ends normally, the database manager releases all locks obtained by the activation group. A user can also explicitly request that most locks be released sooner. This operation is called commit. Object locks associated with cursors that remain open after commit are not released.
The recovery functions of the database manager provide a means of backing out of uncommitted changes made in a commitment definition. The database manager may implicitly back out uncommitted changes under the following situations:
A user can also explicitly request that their database changes be backed out. This operation is called rollback.
Locks acquired by the database manager on behalf of an activation group are held until the unit of work is ended. A lock explicitly acquired by a LOCK TABLE statement can be held past the end of a unit of work if COMMIT HOLD or ROLLBACK HOLD is used to end the unit of work.
A cursor can implicitly lock the row at which the cursor is positioned. This lock prevents:
The start and end of a unit of work defines points of consistency within an activation group. 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 are added to the second account is consistency established again. When both steps are complete, the commit operation can be used to end the unit of work. After the commit operation, the changes are available to activation groups that use different commitment definitions.
Figure 5. Unit of Work with a Commit Statement
 |
If a failure occurs before the unit of work ends, the database manager backs out uncommitted changes to restore the data consistency that it assumes existed when the unit of work started.
Figure 6. Unit of Work with a Rollback Statement
 |