isolation-clause
The isolation-clause specifies the isolation level at which the statement is executed. (Isolation level does not apply to declared temporary tables because no locks are acquired.)
Syntax for isolation-clause
Description for isolation-clause
- RR
- Repeatable read
- RR lock-clause
- Repeatable read, using and keeping the type of lock that is specified in lock-clause on all accessed pages and rows
- RS
- Read stability
- RS lock-clause
- Read stability, using and keeping the type of lock that is specified in lock-clause on all accessed pages and rows
- CS
- Cursor stability
- UR
- Uncommitted read
- lock-clause
- Specifies the type of lock.
- USE AND KEEP EXCLUSIVE LOCKS
- USE AND KEEP UPDATE LOCKS
- USE AND KEEP SHARE LOCKS
- Specifies that Db2 is to acquire and hold X, U, or S locks, respectively.
WITH UR can be specified only if the result table of the fullselect or the SELECT INTO statement is read-only.
In an ODBC application, the SQLSetStmtAttr function can be used to set statement attributes that interact with the lock-clause. If SQLSetStmtAttr is invoked with a cursor's statement handle and specifying that its SQL_ATTR_CLOSE_BEHAVIOR is SQL_CC_RELEASE (locks are to be released when the cursor is closed), then irrespective of any lock-clause, lock used by the cursor that are not needed to protect the integrity of changed data are released..
Although requesting an UPDATE or EXCLUSIVE LOCK can reduce concurrency, it can prevent some types of deadlocks.
The default isolation level of the statement depends on:
- The isolation of the package or plan that the statement is bound in
- Whether the result table is read-only
Table 1 shows the default isolation level of the statement.
If package isolation is: | And plan isolation is: | And the result table is: | Then the default isolation is: |
---|---|---|---|
RR | Any | Any | RR |
RS | Any | Any | RS |
CS | Any | Any | CS |
UR | Any | Read-only | UR |
Not read-only | CS | ||
Not specified | Not specified | Any | RR |
RR | Any | RR | |
RS | Any | RS | |
CS | Any | CS | |
UR | Read-only | UR | |
Not read-only | CS |
A simple way to ensure that a result table is read-only is to specify FOR READ ONLY in the SQL statement.
Alternative syntax and synonyms: KEEP UPDATE LOCKS can be specified as a synonym for USE AND KEEP EXCLUSIVE LOCKS. However, KEEP UPDATE LOCKS can be specified only if FOR UPDATE OF is specified, and it is not supported in the SELECT INTO statement.