The isolation level that is associated with an application process determines the degree to which the data that is being accessed by that process is locked or isolated from other concurrently executing processes. The isolation level is in effect for the duration of a unit of work.
- The degree to which rows that are read or updated by the application are available to other concurrently executing application processes
- The degree to which the update activity of other concurrently executing 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 preparing the statement.
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,
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 have not declared 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 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 accessing or updating data.
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, non-repeatable 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 referenced by a query if that query were to be executed again. This prevents phantom reads.
Because RR can acquire a considerable number of locks, this number might exceed limits 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 it appears that lock escalation is likely. If you do not want table-level locking, use the read stability isolation level.
While evaluating referential constraints, the Db2 server might occasionally upgrade the isolation level used on scans of the foreign table to RR, regardless of the isolation level that was previously set by the user. This results in additional locks being 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 read during a UOW cannot be changed by other application processes until the UOW completes, and that any change to a row made by another application process cannot be read until the change is committed by that process. Under RS, access to uncommitted data and non-repeatable 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 inserted by P2.
In a Db2 pureScale® environment, an application 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 for the duration of a unit of work
- Does not issue the same query more than once during a unit of work, or does not require the same result set when a query is issued more than once during a unit of work
Cursor stability (CS)
The cursor stability isolation level locks any row being 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 terminates. 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, non-repeatable 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 performed under this isolation level behaves according to the configuration parameter cur_commit (Currently Committed).
In a Db2 pureScale environment, an application running at this isolation level may 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.
Under UR, access to uncommitted data, non-repeatable reads, and phantom reads are possible. This isolation level is suitable if you run queries against read-only tables, or if you issue SELECT statements only, and seeing data that has not been 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 operating 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.
- Let the cursors in the application program remain ambiguous, but 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 could behave differently on Column-organized tables and Row-oranized tables.
Comparison of isolation levels
|Can an application see uncommitted changes made by other application processes?||Yes||No||No||No|
|Can an application update uncommitted changes made by other application processes?||No||No||No||No|
|Can the re-execution of 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||Yes/No 6||Yes/No 6||No||No|
Summary of isolation levels
|Isolation level||Access to uncommitted data||Non-repeatable 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-only transactions||RR or RS||UR|