Isolation Level

The isolation level used during the execution of SQL statements determines the degree to which the activation group is isolated from concurrently executing activation groups. Thus, when activation group P executes an SQL statement, the isolation level determines:

• The degree to which rows retrieved by P and database changes made by P are available to other concurrently executing activation groups.
• The degree to which database changes made by concurrently executing activation groups can affect P.

Isolation level is specified as an attribute of an SQL program or SQL package and applies to the activation groups that use the SQL package or SQL program. DB2 UDB for AS/400 provides several ways to specify the isolation level:

• Use the COMMIT parameter on the CRTSQLxxx, STRSQL, and RUNSQLSTM commands to specify the default isolation level.
• Use the SET OPTION statement to specify the default isolation level within the source of a module or program that contains embedded SQL.
• Use the SET TRANSACTION statement to override the default isolation level within a unit of work. When the unit of work ends, the isolation level returns to the value it had at the beginning of the unit of work.
• Use the isolation-clause on the SELECT, SELECT INTO, INSERT, UPDATE, DELETE, and DECLARE CURSOR statements to override the default isolation level for a specific statement or cursor. The isolation level is in effect only for the execution of the statement containing the isolation-clause and has no effect on any pending changes in the current unit of work.

These isolation levels are supported by automatically locking the appropriate data. Depending on the type of lock, this limits or prevents access to the data by concurrent activation groups that use different commitment definitions. Each database manager supports at least two types of locks:

Share

Limits concurrent activation groups that use different commitment definitions to read-only operations on the data.

Exclusive

Prevents concurrent activation groups using different commitment definitions from updating or deleting the data. Prevents concurrent activation groups using different commitment definitions that are running COMMIT(*RS), COMMIT(*CS), or COMMIT(*RR) from reading the data. Concurrent activation groups using different commitment definitions that are running COMMIT(*UR) or COMMIT(*NC) are allowed to read the data.

The following descriptions of isolation levels refer to locking data in row units. Individual implementations can lock data in larger physical units than base table rows. However, logically, locking occurs at the base-table row level across all products. Similarly, a database manager can escalate a lock to a higher level. An activation group is guaranteed at least the minimum requested lock level.

DB2 UDB for AS/400 supports five isolation levels. For all isolation levels except No Commit, the database manager places exclusive locks on every row that is inserted, updated, or deleted. This ensures that any row changed during a unit of work is not changed by any other activation group that uses a different commitment definition until the unit of work is complete. The isolation levels are:

• Repeatable Read (RR)

  Level RR ensures:

  • Any row read during a unit of work is not changed by other activation groups that use different commitment definitions until the unit of work is complete. ²
  • Any row changed (or a row that is currently locked with an UPDATE row lock) by another activation group using a different commitment definition cannot be read until it is committed.

  In addition to any exclusive locks, an activation group running at level RR acquires at least share locks on all the rows it reads. Furthermore, the locking is performed so that the activation group is completely isolated from the effects of concurrent activation groups that use different commitment definitions.

  DB2 UDB for AS/400 supports repeatable-read through COMMIT(*RR). Repeatable-read isolation level is supported by exclusively locking the tables containing any rows that are read or updated. In the ANS and ISO standards, Repeatable Read is called Serializable.

• Read Stability (RS)

  Like level RR, level RS ensures that:

  • Any row read during a unit of work is not changed by other activation groups that use different commitment definitions until the unit of work is complete. ²
  • Any row changed (or a row that is currently locked with an UPDATE row lock) by another activation group using a different commitment definition cannot be read until it is committed.

  Unlike RR, RS does not completely isolate the activation group from the effects of concurrent activation groups that use a different commitment definition. At level RS, activation groups that issue the same query more than once might see additional rows. These additional rows are called phantom rows.

  For example, a phantom row can occur in the following situation:

  1. Activation group P1 reads the set of rows n that satisfy some search condition.
  2. Activation group P2 then INSERTs one or more rows that satisfy the search condition and COMMITs those INSERTs.
  3. P1 reads the set of rows again with the same search condition and obtains both the original rows and the rows inserted by P2.

  In addition to any exclusive locks, an activation group running at level RS acquires at least share locks on all the rows it reads.

  DB2 UDB for AS/400 supports read stability through COMMIT(*ALL) or COMMIT(*RS). In the ANS and ISO standards, Read Stability is called Repeatable Read.

• Cursor Stability (CS)

  Like levels RR and RS, level CS ensures that any row that was changed (or a row that is currently locked with an UPDATE row lock) by another activation group using a different commitment definition cannot be read until it is committed. Unlike RR and RS, level CS only ensures that the current row of every updateable cursor is not changed by other activation groups using different commitment definitions. Thus, the rows that were read during a unit of work can be changed by other activation groups that use a different commitment definition. In addition to any exclusive locks, an activation group running at level CS has at least a share lock for the current row of every cursor.

  DB2 UDB for AS/400 supports cursor stability through COMMIT(*CS). In the ANS and ISO standards, Cursor Stability is called Read Committed.

• Uncommitted Read (UR)

  For a SELECT INTO, a FETCH with a read-only cursor, subquery, or subselect used in an INSERT statement, level UR allows:

  • Any row read during the unit of work to be changed by other activation groups that run under a different commitment definition.
  • Any row changed (or a row that is currently locked with an UPDATE row lock) by another activation group running under a different commitment definition to be read even if the change has not been committed.

  For other operations, the rules of level CS apply.

  DB2 UDB for AS/400 supports uncommitted read through COMMIT(*CHG) or COMMIT(*UR). In the ANS and ISO standards, Uncommitted Read is called Read Uncommitted.

• No Commit (NC)

  For all operations, the rules of level UR apply except:

  • Commit and rollback operations have no effect on SQL statements. Cursors are not closed, and LOCK TABLE locks are not released. However, connections in the release-pending state are ended.
  • Any changes are effectively committed at the end of each successful change operation and can be immediately accessed or changed by other application groups using different commitment definitions.

  DB2 UDB for AS/400 supports No Commit through COMMIT(*NONE) or COMMIT(*NC).

  For a detailed description of record lock durations, see the discussion and table in the commitment control topic of the SQL Programming Concepts book.

²

For WITH HOLD cursors, these rules apply to when the rows were actually read. For read-only WITH HOLD cursors, the rows may have actually been read in a prior unit of work.