All SQL programs execute as part of an application process or agent. An application process involves the execution of one or more programs, and is the unit to which the database manager allocates resources and locks. Different application processes may involve the execution of different programs, or different executions of the same program.
More than one application process may request access to the same data at the same time. Locking is the mechanism used to maintain data integrity under such conditions, preventing, for example, two application processes from updating the same row of data simultaneously.
The database manager acquires locks in order to prevent uncommitted changes made by one application process from being accidentally perceived by any other. The database manager releases all locks it has acquired and retained on behalf of an application process when that process ends, but an application process itself should explicitly request that locks be released sooner. This operation is called commit and it writes the changes to the database.
The database manager provides a means of backing 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 or lock timeout situation. An application process itself, however, can explicitly request that its database changes be backed out. This operation is called rollback.
A unit of work is a recoverable sequence of operations within an application process. At any time, an application process has a single unit of work (3), but the life of an application process may involve many units of work as a result of commit or rollback operations.
A unit of work is initiated when an application process is initiated. A unit of work is also initiated when the previous unit of work is ended by something other than the termination of the application process. A unit of work is ended by a commit operation, a rollback operation, or the end of an application process. A commit or rollback operation affects only the database changes made within the unit of work it ends. While these changes remain uncommitted, other application processes are unable to perceive them and they can be backed out. (4) Once committed, these database changes are accessible by other application processes and can no longer be backed out by a rollback.
Locks acquired by the database manager on behalf of an application process are held until the end of a unit of work. The exception to this rule is with a read stability or cursor stability isolation level, or an uncommitted read level, in which case the lock is released as the cursor moves from row to row (see Isolation Level).
The initiation and termination of a unit of work define points of consistency within an application process. 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 unit of work, thereby making the changes available to other application processes.
Figure 1. Unit of Work with a Commit Statement
|
If a failure occurs before the unit of work ends, the database manager will roll back uncommitted changes to restore the data consistency that it assumes existed when the unit of work was initiated.
Figure 2. Unit of Work with a Rollback Statement
 |
| Note: | An application process is never prevented from performing operations because of its own locks. |
(3) DB2 CLI supports a connection mode called concurrent transactions which supports multiple connections, each of which is an independent transaction. Furthermore, an application can have multiple concurrent connections to the same database, which is not possible at all with DB2 embedded SQL.
(4) Except for isolation level uncommitted read, described in Uncommitted Read (UR).