isolation-clause

The isolation-clause specifies an isolation level at which the select statement is executed.

Read syntax diagramSkip visual syntax diagramWITHNCURCSKEEP LOCKSRSlock-clauseRRlock-clause
lock-clause
Read syntax diagramSkip visual syntax diagramUSE AND KEEP EXCLUSIVE LOCKS
RR
Repeatable Read
USE AND KEEP EXCLUSIVE LOCKS
Exclusive row locks are acquired and held until a COMMIT or ROLLBACK statement is executed.
RS
Read Stability
USE AND KEEP EXCLUSIVE LOCKS
Exclusive row locks are acquired and held until a COMMIT or ROLLBACK statement is executed. The USE AND KEEP EXCLUSIVE LOCKS clause is only allowed in the isolation-clause in the following SQL statements:
  • DECLARE CURSOR
  • FOR
  • select-statement
  • SELECT INTO
  • PREPARE in the ATTRIBUTES string
It is not allowed on updatable cursors.
CS
Cursor Stability
KEEP LOCKS
The KEEP LOCKS clause specifies that any read locks acquired will be held for a longer duration. Normally, read locks are released when the next row is read. If the isolation clause is associated with a cursor, the locks will be held until the cursor is closed or until a COMMIT or ROLLBACK statement is executed. Otherwise, the locks will be held until the completion of the SQL statement.
UR
Uncommitted Read
NC
No Commit

If isolation-clause is not specified, the default isolation is used with the exception of a default isolation level of uncommitted read. See Isolation level for a description of how the default is determined.

Exclusive locks: The USE AND KEEP EXCLUSIVE LOCKS clause should be used with caution. If it is specified, the exclusive row locks that are acquired on rows will prevent concurrent access to those rows by other users running COMMIT(*CS), COMMIT(*RS), and COMMIT(*RR) till the end of the unit of work. Concurrent access by users running COMMIT(*NC) or COMMIT(*UR) is not prevented.

Keyword Synonyms: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

  • The keyword NONE can be used as a synonym for NC.
  • The keyword CHG can be used as a synonym for UR.
  • The keyword ALL can be used as a synonym for RS.