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. However, an application process can explicitly request that locks be released sooner. This is done using a commit operation which releases locks acquired during the unit of work and also commits database changes made during the unit of work.
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 time-out 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. A unit of work is initiated when an application process is started 3 . 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 exceptions to this rule are cursor stability isolation level, where the lock is released as the cursor moves from row to row, and uncommitted read level, where locks are not obtained (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.
Figure 1. Unit of Work with a Commit Statement
|
Figure 2. Unit of Work with a Rollback Statement
 |
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.
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.
| Note: | An application process is never prevented from performing operations because of its own locks. 5 |