Isolation levels
When an application process accesses data, the isolation level determines the degree to which that data is locked or isolated from other concurrent processes. The isolation level is in effect during a unit of work.
- The degree to which rows that are read or updated by the application are available to other concurrently running application processes.
- The degree to which the update activity of other concurrently running application processes can affect the application.
The isolation level for static SQL statements is specified as an attribute of a package and
applies to the application processes that use that package. The isolation level is specified during
the program preparation process by setting the ISOLATION bind or precompile option. For dynamic SQL
statements, the default isolation level is the isolation level that was specified for the package
while the statement was prepared. Use the SET CURRENT ISOLATION statement to specify a different
isolation level for dynamic SQL statements that are issued within a session. For more information,
see CURRENT ISOLATION special register
. For both static SQL statements and dynamic SQL
statements, the isolation-clause in a
select-statement overrides both the special register (if set) and the bind
option value. For more information, see Select-statement
.
Isolation levels are enforced by locks, and the type of lock that is used limits or prevents access to the data by concurrent application processes. Declared temporary tables and their rows cannot be locked because they are only accessible to the application that declared them.
- Share (S)
- Under an S lock, concurrent application processes are limited to read-only operations on the data.
- Update (U)
- Under a U lock, concurrent application processes are limited to read-only operations on the data, if these processes did not declare that they might update a row. The database manager assumes that the process currently looking at a row might update it.
- Exclusive (X)
- Under an X lock, concurrent application processes are prevented from accessing the data in any way. This lock type does not apply to application processes with an isolation level of uncommitted read (UR), which can read but not modify the data.
A detailed description of each isolation level follows, in decreasing order of performance impact, but in increasing order of the care that is required when data is accessed or updated.
Repeatable read (RR)
The repeatable read isolation level locks all the rows that an application references during a unit of work (UOW). If an application issues a SELECT statement twice within the same unit of work, the same result is returned each time. Under RR, lost updates, access to uncommitted data, nonrepeatable reads, and phantom reads are not possible.
Under RR, an application can retrieve and operate on the rows as many times as necessary until the UOW completes. However, no other application can update, delete, or insert a row that would affect the result set until the UOW completes. Applications running under the RR isolation level cannot see the uncommitted changes of other applications. This isolation level ensures that all returned data remains unchanged until the time the application sees the data, even when temporary tables or row-blocking is used.
Every referenced row is locked, not just the rows that are retrieved. For example, if you scan 10 000 rows and apply predicates to them, locks are held on all 10 000 rows, even if, say, only 10 rows qualify. Another application cannot insert or update a row that would be added to the list of rows that are referenced by a query if that query were to be run again. This aspect of RR prevents phantom reads.
Because RR can acquire a considerable number of locks, this number might exceed limits that are specified by the locklist and maxlocks database configuration parameters. To avoid lock escalation, the optimizer might elect to acquire a single table-level lock for an index scan, if lock escalation is likely. If you do not want table-level locking, use the read stability isolation level.
While the Db2 server evaluates referential constraints, it might occasionally upgrade the isolation level used on scans of the foreign table to RR. This upgrade might occur regardless of the isolation level that was previously set by the user. Due to this upgrade of isolation level, more locks might be held until commit time, which increases the likelihood of a deadlock or a lock timeout. To avoid these problems, create an index that contains only the foreign key columns, which the referential integrity scan can use instead.
Read stability (RS)
The read stability isolation level locks only those rows that an application retrieves during a unit of work. RS ensures that any qualifying row that is read during a UOW cannot be changed by other application processes until the UOW completes. RS also ensures that any change to a row that is made by another application process cannot be read until the change is committed by that process. Under RS, access to uncommitted data and nonrepeatable reads are not possible. However, phantom reads are possible. Phantom reads might also be introduced by concurrent updates to rows where the old value did not satisfy the search condition of the original application but the new updated value does.
- Application process P1 reads the set of rows n that satisfy some search condition.
- Application process P2 then inserts one or more rows that satisfy the search condition and commits those new inserts.
- P1 reads the set of rows again with the same search condition and obtains both the original rows and the rows that were inserted by P2.
In a Db2 pureScale® environment, an application that is running at this isolation level might reject a previously committed row value if the row is updated concurrently on a different member. To override this behavior, specify the WAIT_FOR_OUTCOME option.
This isolation level ensures that all returned data remains unchanged until the time the application sees the data, even when temporary tables or row-blocking is used.
The RS isolation level provides both a high degree of concurrency and a stable view of the data. To that end, the optimizer ensures that table-level locks are not obtained until lock escalation occurs.
- Operates in a concurrent environment.
- Requires qualifying rows to remain stable during a unit of work.
- Does not issue the same query more than one time during a unit of work.
- Does not require the same result set when a query is issued more than one time during a unit of work.
Cursor stability (CS)
The cursor stability isolation level locks any row that is accessed during a transaction while the cursor is positioned on that row. This lock remains in effect until the next row is fetched or the transaction ends. However, if any data in the row was changed, the lock is held until the change is committed.
Under this isolation level, no other application can update or delete a row while an updatable cursor is positioned on that row. Under CS, access to the uncommitted data of other applications is not possible. However, nonrepeatable reads and phantom reads are possible.
CS is the default isolation level. It is suitable when you want maximum concurrency and need to see only committed data. Scans that run under this isolation level behave according to the configuration parameter cur_commit (Currently Committed).
In a Db2 pureScale environment, an application that runs at this isolation level might return or reject a previously committed row value if the row is concurrently updated on a different member. The WAIT FOR OUTCOME option of the concurrent access resolution setting can be used to override this behavior.
Uncommitted read (UR)
The uncommitted read isolation level allows an application to access the uncommitted changes of other transactions. Moreover, UR does not prevent another application from accessing a row that is being read, unless that application is attempting to alter or drop the table.
- You run queries against read-only tables.
- You issue SELECT statements only, and seeing data that was committed by other applications is not a problem.
- Read-only cursors can access most of the uncommitted changes of other transactions.
- Tables, views, and indexes that are being created or dropped by other transactions are not available while the transaction is processing. Any other changes by other transactions can be read before they are committed or rolled back. Updatable cursors that operate under UR behave as though the isolation level were CS.
- Modify the cursors in the application program to be unambiguous. Change the SELECT statements to include the FOR READ ONLY clause.
- Leave the cursors in the application program as-is so they remain ambiguous. However, precompile the program or bind it with the BLOCKING ALL and STATICREADONLY YES options to enable the ambiguous cursors to be treated as read-only when the program runs.
ISOLATION LEVEL UR might behave differently on Column-organized tables and Row-oranized tables.
Comparison of isolation levels
UR | CS | RS | RR | |
---|---|---|---|---|
Can an application see uncommitted changes that are made by other application processes? | Yes | No | No | No |
Can an application update uncommitted changes that are made by other application processes? | No | No | No | No |
Can rerunning a statement be affected by other application processes? 1 | Yes | Yes | Yes | No 2 |
Can updated rows be updated by other application processes? 3 | No | No | No | No |
Can updated rows be read by other application processes that are running at an isolation level other than UR? | No | No | No | No |
Can updated rows be read by other application processes that are running at the UR isolation level? | Yes | Yes | Yes | Yes |
Can accessed rows be updated by other application processes? 4 | Yes | Yes | No | No |
Can accessed rows be read by other application processes? | Yes | Yes | Yes | Yes |
Can the current row be updated or deleted by other application processes? 5 | See note 6 | See note 6 | No | No |
Note:
|
Summary of isolation levels
Isolation level | Access to uncommitted data | Nonrepeatable reads | Phantom reads |
---|---|---|---|
Repeatable read (RR) | Not possible | Not possible | Not possible |
Read stability (RS) | Not possible | Not possible | Possible |
Cursor stability (CS) | Not possible | Possible | Possible |
Uncommitted read (UR) | Possible | Possible | Possible |
Application type | High data stability required | High data stability not required |
---|---|---|
Read/write transactions | RS | CS |
Read-only transactions | RR or RS | UR |