Neoview Control Query Default (CQD) Reference Guide (R2.5)

11 Managing Isolation Level
This section describes these CQDs that are used for managing the isolation level:
• “ISOLATION_LEVEL” (page 45)
• “ISOLATION_LEVEL_FOR_UPDATES” (page 46)
ISOLATION_LEVEL
Transaction control and lockingCategory
Specifies the default transaction isolation level that queries use.Description
‘READ
UNCOMMITTED’
‘READ COMMITTED’
‘REPEATABLE READ’
‘SERIALIZABLE’
The default value is ‘READ COMMITTED’ (ANSI).
Values
If you use uncommitted access (reading “dirty” data when queries are accessing data that
is being simultaneously updated), you can set the default isolation level as READ
UNCOMMITTED. The default isolation level of READ COMMITTED can cause concurrency
issues because reads would wait on locked rows. If rows are locked by long-running
transactions with infrequent commits, this can cause severe concurrency issues for SELECT
queries. See Conflicts/Synergies.
Usage
Not applicableProduction usage
Using this CQD has implications on locking and concurrency.
If set to READ UNCOMMITTED then select queries read through locks and don't have to
wait on locks. But they won't see committed consistent data.
If set to READ COMMITTED (the default setting) then the reads wait on locked rows before
they proceed with the scan. The read can proceed only when the rows locked by another
transaction are released after that transaction commits. The reader does not lock rows.
If set to REPEATABLE READ or SERIALIZABLE it has severe implications on concurrency
because every row read is also locked.
Impact
While you can use this at a query or a service level, the most common use is a system-wide
setting. If query tools are being used, then the query level setting cannot be used.
A service level setting may provide uncommitted access to certain users while providing
the default committed access to the other users, depending which users need to see
consistent data.
If however, access to tables during updates is well controlled and read uncommitted is
acceptable, this can be set at the system level.
Level
The problem with using READ UNCOMMITTED as the isolation level default value is that
in a SET TRANSACTION statement, the only possible access mode is READ ONLY. Any
query that attempts to update the database would fail.
To facilitate updates and DDL statements while the isolation level is set to READ
UNCOMMITTED, a new default attribute ISOLATION_LEVEL_FOR_UPDATES is provided.
This default attribute specifies the isolation level for update and DDL statements. If not
specified, or if not present in the SYSTEM_DEFAULTS table, the default value is the same
as the ISOLATION_LEVEL default attribute. However, if specified or present in the
SYSTEM_DEFAULTS table, its value is used as the isolation level for updates and DDL
statements. UPDATE in ISOLATION_LEVEL_FOR_UPDATES refers to INSERT, UPDATE,
and DELETE statements.
Conflicts/Synergies
Not applicableAddressing the real
problem
ISOLATION_LEVEL 45