Various isolation levels offer less or more
concurrency at the cost of more or less protection from other application
processes.
About this task
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:
- Cursor stability (CS)
- Uncommitted read (UR)
- Read stability (RS)
- 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:
- Choose an isolation level according to the needs and characteristics
of the particular application.
- 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.
- If you do not use ISOLATION(CS) and
CURRENTDATA(NO), use the following bind options, in order of decreasing
preference:
- ISOLATION(CS) with CURRENTDATA(YES), when data returned to the
application must not be changed before your next FETCH operation.
- 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.
- 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.
- 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.