SQL Reference

Isolation Level

The isolation level associated with an application process defines the degree of isolation of that application process from other concurrently executing application processes. The isolation level of an application process, P, therefore specifies:

• The degree to which rows read and updated by P are available to other concurrently executing application processes
• The degree to which update activity of other concurrently executing application processes can affect P.

The isolation level is specified as an attribute of a package and applies to the application processes that use the package. The isolation level is specified in the program preparation process. Depending on the type of lock, this limits or prevents access to the data by concurrent application processes. For details on different types and attributes of specific locks refer to the Administration Guide. Declared temporary tables and the rows of declared temporary tables are not locked at all because they are only accessible by the application that declared the temporary tables. Thus, the following discussion on locking and isolation levels does not apply to declared temporary tables.

The database manager supports three general categories of locks:

Share

Limits concurrent application processes to read-only operations on the data.

Update

Limits concurrent application processes to read-only operations on the data providing these processes have not declared they might update the row. The database manager assumes the process looking at the row presently may update the row.

Exclusive

Prevents concurrent application processes from

accessing the data in any way except for application processes with an isolation level of uncommitted read, which can read but not modify the data. (See Uncommitted Read (UR).)

Locking occurs at the base table row. The database manager, however, can replace multiple row locks with a single table lock. This is called lock escalation. An application process is guaranteed at least the minimum requested lock level.

The DB2 Universal Database database manager supports four isolation levels. Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. Thus, all isolation levels ensure that any row that is changed by this application process during a unit of work is not changed by any other application processes until the unit of work is complete. The isolation levels are:

Repeatable Read (RR)

Level RR ensures that:

• Any row read during a unit of work⁶ is not changed by other application processes until the unit of work is complete.⁷
• Any row changed by another application process cannot be read until it is committed by that application process.

RR does not allow phantom rows (see Read Stability) to be seen.

In addition to any exclusive locks, an application process running at level RR acquires at least share locks on all the rows it references. Furthermore, the locking is performed so that the application process is completely isolated from the effects of concurrent application processes.

Read Stability (RS)

Like level RR, level RS ensures that:

• Any row read during a unit of work⁶ is not changed by other application processes until the unit of work is complete ⁸
• Any row changed by another application process cannot be read until it is committed by that application process.

Unlike RR, RS does not completely isolate the application process from the effects of concurrent application processes. At level RS, application processes 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. Application process P1 reads the set of rows n that satisfy some search condition.
2. Application process 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 application process running at level RS acquires at least share locks on all the qualifying rows.

Cursor Stability (CS)

Like the RR level:

• CS ensures that any row that was changed by another application process cannot be read until it is committed by that application process.

Unlike the RR level:

• CS only ensures that the current row of every updatable cursor is not changed by other application processes. Thus, the rows that were read during a unit of work can be changed by other application processes.

In addition to any exclusive locks, an application process running at level CS has at least a share lock for the current row of every cursor.

Uncommitted Read (UR)

For a SELECT INTO, FETCH with a read-only cursor, fullselect used in an INSERT, row fullselect in an UPDATE, or scalar fullselect (wherever used), level UR allows:

• Any row that is read during the unit of work to be changed by other application processes.
• Any row that was changed by another application process to be read even if the change has not been committed by that application process.

For other operations, the rules of level CS apply.

Comparison of Isolation Levels

A comparison of the four isolation levels can be found on Appendix I, Comparison of Isolation Levels.

⁶

The rows must be read in the same unit of work as the corresponding OPEN statement. See WITH HOLD in DECLARE CURSOR.

⁷

Use of the optional WITH RELEASE clause on the CLOSE statement means that any guarantees against non-repeatable read and phantoms no longer apply to any previously accessed rows if the cursor is reopened.

⁸

Use of the optional WITH RELEASE clause on the CLOSE statement means that any guarantees against non-repeatable read no longer apply to any previously accessed rows if the cursor is reopened.