Choosing an ISOLATION option

Various isolation levels offer less or more concurrency at the cost of more or less protection from other application processes.

About this task

Begin program-specific programming interface information.The ISOLATION option of an application specifies the degree to which operations are isolated from the possible effects of other operations that act concurrently. The ISOLATION options specified how soon Db2 can release S and U locks on rows or pages. Regardless of the isolation level that you specify, outstanding claims on Db2 objects can inhibit the execution of Db2 utilities or commands.

The default ISOLATION option differs for different types of bind operations, as shown in the following table.

Table 1. The default ISOLATION values for different types of bind operations
Operation Default value
BIND PLAN ISOLATION (CS) with CURRENTDATA (NO)
BIND PACKAGE The value used by the plan that includes the package in its package list
REBIND PLAN or PACKAGE The existing value for the plan or package being rebound
The recommended order of preference for isolation levels is:
  1. Cursor stability (CS)
  2. Uncommitted read (UR)
  3. Read stability (RS)
  4. Repeatable read (RR)

Although uncommitted read provides the lowest level of isolation, cursor stability isolation is recommended in most cases. ISOLATION(CS) provides a high level of concurrency, without sacrificing data integrity.

Procedure

To ensure that your applications can access your data concurrently, without sacrificing data integrity:

  1. Choose an isolation level according to the needs and characteristics of the particular application.
  2. Bind most applications with the ISOLATION(CS) and CURRENTDATA(NO) options.
    These options enable Db2 to release locks early and avoid taking locks in many cases.
    ISOLATION(CS) typically enables Db2 to release acquired locks as soon as possible. The CURRENTDATA(NO) typically enables Db2 to acquire the fewest number of locks, for better lock avoidance.
    When you use ISOLATION(CS) and CURRENTDATA(NO), consider using the SKIPUNCI subsystem parameter value to YES so that readers do not wait for the outcome of uncommitted inserts.
  3. If you do not use ISOLATION(CS) and CURRENTDATA(NO), use the following bind options, in order of decreasing preference:
    1. ISOLATION(CS) with CURRENTDATA(YES), when data returned to the application must not be changed before your next FETCH operation.
    2. ISOLATION(RS), when data returned to the application must not be changed before your application commits or rolls back. However, you do not care if other application processes insert additional rows.
    3. ISOLATION(RR), when data evaluated as the result of a query must not be changed before your application commits or rolls back. New rows cannot be inserted into the answer set.
  4. Use ISOLATION(UR) option cautiously.
    The Resource Recovery Services attachment facility UR isolation acquires almost no locks on rows or pages. It is fast and causes little contention, but it reads uncommitted data. Do not use it unless you are sure that your applications and end users can accept the logical inconsistencies that can occur.

    As an alternative, consider using a SKIP LOCKED DATA clause if omitting data is preferable to reading uncommitted data in your application.