Specifying isolation levels in SQL statements

You can override the isolation level that an application plan or package is bound with for specific SQL statements.

To override the isolation level for a specific SQL statement:

• Issue the SQL statements, and include a WITH isolation level clause.
  The WITH isolation level clause:

  • Can be used on these statements:
    • SELECT
    • SELECT INTO
    • Searched DELETE
    • INSERT from fullselect
    • Searched UPDATE
  • Cannot be used on subqueries.
  • Can specify the isolation levels that specifically apply to its statement. (For example, because WITH UR applies only to read-only operations, you cannot use it on an INSERT statement.)
  • Overrides the isolation level for the plan or package only for the statement in which it appears.
  The following statement finds the maximum, minimum, and average bonus in the sample employee table.

  SELECT MAX(BONUS), MIN(BONUS), AVG(BONUS)
    INTO :MAX, :MIN, :AVG
    FROM DSN8C10.EMP
       WITH UR;

  The statement is executed with uncommitted read isolation, regardless of the value of ISOLATION with which the plan or package containing the statement is bound.
• If you use the WITH RR or WITH RS clause, you can issue SELECT and SELECT INTO statements, and specify the following options:
  • USE AND KEEP EXCLUSIVE LOCKS
  • USE AND KEEP UPDATE LOCKS
  • USE AND KEEP SHARE LOCKS
  To use these options, specify them as shown in the following example:

  SELECT ...
   WITH RS USE AND KEEP UPDATE LOCKS;