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:
It may be that you are working in a federated database system that supports applications and users submitting SQL statements referencing two or more database management systems (DBMSs) or databases in a single statement. A DB2 federated system provides location transparency for database objects. For example, if information about tables and views is moved, references to that information (called nicknames) can be updated without changes to applications that request the information. When an application accesses nicknames, DB2 relies on the concurrency control protocols of data source database managers to ensure isolation levels. (A data source consists of a DBMS and data.) DB2 will attempt to match the requested level of isolation at the data source with a logical equivalent; however, results may vary based on data source capabilities. Refer to the Application Development Guide manual for information on writing applications accessing nicknames.
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. (See Maximum Percent of Lock List Before Escalation (maxlocks) and Maximum Storage for Lock List (locklist).) 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 38 summarizes the different isolation levels in terms of the
undesirable effects described in Application Development
Guide manual.
Table 38. 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 39 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 39. 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). Other command line processor packages are also bound to the database when a database is created. Refer to the Application Development Guide for more information about 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. (Refer to 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.
The DB2_RR_TO_RS profile registry variable can be used to prevent Repeatable Read (RR) isolation level access to user tables. This registry value can be set to "YES" using db2set in environments where RR isolation semantics are not required. Before taking effect, you must stop and start the database. Following the db2start, this change affects the entire instance. Once set, if a request to access a user table using RR is received, the request is modified internally to use the Read Stability (RS) isolation level instead. No warning is given when this occurs.
If you are using the command line processor you may change the isolation level using the CHANGE ISOLATION LEVEL command. Refer to the Command Reference manual for more information.
For DB2 Call Level Interface (DB2 CLI), you may change the isolation level as part of the DB2 CLI configuration. In addition, many commercially-written applications also provide a method to allow you to choose the isolation level. Refer to the CLI Guide and Reference manual for more information.