The integrity of the data in a relational database must be maintained as multiple users access and change the data. Concurrency is the sharing of resources by multiple interactive users or application programs at the same time. The database manager controls this access to prevent undesirable effects, such as:
When the query is repeated (step 3), some additional ("phantom") rows are returned as part of the result set that were not returned when the query was initially executed (step 1).
An isolation level determines how data is locked or isolated from other processes while the data is being accessed. The isolation level will be in effect for the duration of the unit of work. Applications that use a cursor declared using the WITH HOLD clause will keep the chosen isolation level for the duration of the unit of work in which the OPEN CURSOR was performed. (For more information, refer to the SQL Reference manual.) See "Specifying the Isolation Level" for information on how the isolation level is specified.
DB2 supports the following isolation levels:
(Note that some DRDA database servers support the no commit isolation level. On other databases, it behaves like the uncommitted read isolation level. Refer to the SQL Reference for information on this isolation level.)
See also:
Repeatable read (RR) locks all the rows an application references within a unit of work. Using repeatable read, a SELECT statement issued by an application twice within the same unit of work, in which the cursor was opened, gives the same result each time. With repeatable read, lost updates, access to uncommitted data, and phantom rows are not possible.
The repeatable read application can retrieve and operate on the rows as many times as needed until the unit of work completes. However, no other applications can update, delete, or insert a row that would affect the result table, until the unit of work completes. Repeatable read applications cannot see uncommitted changes of other applications.
With repeatable read, every row that is referenced is locked, not just the rows that are retrieved. Appropriate locking is performed so that another application cannot insert or update a row that would be added to the list of rows referenced by your query, if the query was re-executed. This prevents phantom rows from occurring. This means that if you scan 10 000 rows and apply predicates to them, locks are held on all 10 000 rows, even though only 10 rows qualify.
| Note: | The repeatable read isolation level ensures that all returned data remains unchanged until the time the application sees the data, even when temporary tables or row blocking are used. |
Since repeatable read may acquire and hold a considerable number of locks, these locks may exceed the number of locks available as a result of the locklist and maxlocks configuration parameters. (Refer to the Administration Guide for recommendations and information on setting these parameters.) In order to avoid lock escalation, the optimizer may elect to immediately acquire a single table level lock for an index scan, if it believes that lock escalation is very likely to occur. (See "Lock Escalation" for a discussion of lock escalation.) This functions as though the database manager has issued a LOCK TABLE statement on your behalf. If you do not want a table level lock to be obtained ensure that enough locks are available to the transaction or use the "Read Stability" isolation level.
Read stability (RS) locks only those rows that an application retrieves within a unit of work. It ensures that any qualifying row read during a unit of work is not changed by other application processes until the unit of work completes, and that any row changed by another application process is not read until the change is committed by that process. That is, "nonrepeatable read" behavior is not possible.
Unlike repeatable read, with read stability, if your application issues the same query more than once, you may see additional phantom rows (the phantom read phenomenon). Recalling the example of scanning 10 000 rows, read stability only locks the rows that qualify. Thus, with read stability, only 10 rows are retrieved, and a lock is held only on those ten rows. Contrast this with repeatable read, where in this example, locks would be held on all 10 000 rows. The locks that are held can be share, next share, update, or exclusive locks. (For more information on lock attributes, see "Attributes of Locks".)
| Note: | The read stability isolation level ensures that all returned data remains unchanged until the time the application sees the data, even when temporary tables or row blocking are used. |
One of the objectives of the read stability isolation level is to provide both a high degree of concurrency as well as a stable view of the data. To assist in achieving this objective, the optimizer ensures that table level locks are not obtained until lock escalation occurs. (See "Lock Escalation" for more information about lock escalation).
The read stability isolation level is best for applications that include all of the following:
Cursor stability (CS) locks any row accessed by a transaction of an application while the cursor is positioned on the row. This lock remains in effect until the next row is fetched or the transaction is terminated. However, if any data on a row is changed, the lock must be held until the change is committed to the database.
No other applications can update or delete a row that a cursor stability application has retrieved while any updatable cursor is positioned on the row. Cursor stability applications cannot see uncommitted changes of other applications.
Recalling the example of scanning 10 000 rows, if you use cursor stability, you will only have a lock on the row under your current cursor position. The lock is removed when you move off that row (unless you update that row).
With cursor stability, both nonrepeatable read and the phantom read phenomenon are possible. Cursor stability is the default isolation level and should be used when you want the maximum concurrency while seeing only committed rows from other applications.
Uncommitted read (UR) allows an application to access uncommitted changes of other transactions. The application also does not lock other applications out of the row it is reading, unless the other application attempts to drop or alter the table. Uncommitted read works differently for read-only and updatable cursors.
Read-only cursors can access most uncommitted changes of other transactions. However, tables, views, and indexes that are being created or dropped by other transactions are not available while the transaction is processing. Any other changes by other transactions can be read before they are committed or rolled back.
Cursors that are updatable operating under the uncommitted read isolation level will behave as if the isolation level was cursor stability.
Recalling the example of scanning 10 000 rows, if you use uncommitted read, you do not acquire any row locks.
With uncommitted read, both nonrepeatable read behavior and the phantom read phenomenon are possible.
The uncommitted read isolation level is most commonly used for queries on read-only tables, or if you are only executing select-statements and you do not care whether you see uncommitted data from other applications.
Table 4 summarizes the different isolation levels in terms of the undesirable
effects described in "Concurrency" .
Table 4. Summary of isolation levels
| Isolation Level | Access to Uncommitted Data | Nonrepeatable Reads | Phantom Read Phenomenon |
|---|---|---|---|
| Repeatable Read (RR) | Not Possible | Not Possible | Not Possible |
| Read Stability (RS) | Not Possible | Not Possible | Possible |
| Cursor Stability (CS) | Not Possible | Possible | Possible |
| Uncommitted Read (UR) | Possible | Possible | Possible |
Table 5 provides a simple heuristic that may help you choose an initial isolation
level for your applications. Consider this table as a starting point,
and refer to the previous discussions of the various levels for factors that
might make another value more appropriate for your requirements.
Table 5. Guidelines for choosing an isolation level
| Application Type | High data stability required | High data stability not required |
|---|---|---|
| Read-write transactions | RS | CS |
| Read-only transactions | RR | UR |
Choosing the appropriate isolation level for an application is very important to avoid the phenomena that are intolerable for that application. The isolation level affects not only the degree of isolation among applications but also the performance characteristics of an individual application since the CPU and memory resources, required to obtain and free locks, vary with the isolation level. The potential for deadlock situations also varies with the isolation level.
The isolation level is specified at precompile time or when an application is bound to a database. For an application written in a supported compiled language, use the ISOLATION option of the command line processor PREP or BIND commands. The isolation level can also be specified by using the PREP or BIND APIs. If no isolation level is specified, the default of cursor stability is used.
If a bind file is created at precompile time, the isolation level is stored in the bind file. If no isolation level is specified at bind time, the default is the isolation level used during precompilation.
You can determine the isolation level of a package by executing the following query:
SELECT ISOLATION FROM SYSCAT.PACKAGES
WHERE PKGNAME = 'XXXXXXXX'
AND PKGSCHEMA = 'YYYYYYYY'
where XXXXXXXX is the name of the package and YYYYYYYY is the schema name of the package. Both of these names must be in all capital letters.
When a database is created, multiple bind files used to support the different isolation levels for SQL in REXX are bound to the database (on those servers that support REXX). See "Execution Requirements for REXX" for more information about these bind files.
REXX and the command line processor connect to a database using a default isolation level of cursor stability. Changing to a different isolation level does not change the connection state. It must be executed in the CONNECTABLE AND UNCONNECTED state or in the IMPLICITLY CONNECTABLE state. (See the CONNECT TO statement in the SQL Reference for details about connection states.) You cannot be connected to a database when issuing this command.
The isolation level being used can be checked by a REXX application by checking the value of the SQLISL REXX variable. The value is updated every time the CHANGE SQLISL command is executed.
A transaction is a sequence of SQL statements (possibly with intervening host language code) that the database manager treats as a whole.
A transaction (also known as unit of work), is the basic building block the database manager uses to ensure that a database is in a consistent state. Any reading or writing of the database is done within a unit of work. A point of consistency (or commit point) is a point in time when all recoverable data an application accesses is consistent. It occurs when updates, inserts, and deletions are either committed to the physical database or rolled back (not committed and therefore discarded).
For example, a banking application might transfer funds from account A to account B. After the application subtracts the amount from account A, the two accounts are inconsistent; not until the amount is added to account B are they consistent again. When both steps are complete, the application can announce a point of consistency, and then make the changes available to other applications. This entire process constitutes one transaction.
Any application that successfully connects to a database automatically starts a unit of work. The application must end the unit of work by issuing either a COMMIT or a ROLLBACK statement, or by disconnecting from the database. Disconnecting will automatically do a COMMIT and close all cursors. For more information on cursors, see "Selecting Multiple Rows Using a Cursor".
The COMMIT statement makes all changes made within the unit of work permanent, while the ROLLBACK statement removes all these changes from the database. Either of these statements usually frees locks held on data objects by the unit of work, and another unit of work can access that data. During the execution of an application, an explicit COMMIT statement can be issued to create a point of consistency, or an explicit ROLLBACK statement can be issued to restore data changed by SQL statements back to the state at its last commit point. Once a COMMIT or ROLLBACK has been issued, it cannot be stopped.
If the application ends abnormally while in the middle of a unit of work (this can occur while the application is not executing a database manager API or SQL statement), the unit of work is rolled back. Normally, an application is expected to end units of work on a timely basis so that other units of work can also access the same data. For information about coding transactions, see "Coding Transactions". For information regarding transactions in which multiple databases are accessed, refer to "Distributed Unit of Work".
The consistency of a database is also protected in the event of a system crash by the recovery process. The recovery process uses a log of transactions to ensure consistency.
Transaction logging is the process that records each change to a database to permit recovery. A transaction is a sequence of statements to the database manager that performs one complete change. One transaction constitutes a unit of work.
The database manager maintains a log of recent changes made to a database so that the recovery process can restore the database to a consistent state. The consistent state is defined as follows: at the time of the system error, all units of work that had successfully committed or rolled back are restored to that state, respectively; all units of work that were inflight (units of work that had made changes but not yet committed or rolled back) are rolled back.
The recovery log contains the entries that describe the changes to database objects. Each entry contains the information needed to roll back the transaction before the changed data is written to the database. Thus, although some work may be lost, the database is restored to a state where all changes to data have been made by a unit of work that ended with a COMMIT statement.
If you are logging large object (LOB) data, you have to consider the impact to performance. If you turn logging on for LOB data, your application's performance will deteriorate and you may encounter problems related to the increased size of the log file. If you turn the logging off, your application's performance improves; however, its recoverability is sacrificed. For more information on the merits of logging LOB data, refer to the Administration Guide.