DB2 query modifiers: FOR UPDATE, SKIP LOCKED, and isolation

Advanced SELECT and query modifiers

A select-statement wraps a fullselect. After the fullselect you may add clauses that do not change the mathematical result of grouping, but change locks, cursor type, and cut-off:

1
2
3
4
5
6
7
8
9
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00'
 ORDER BY SALARY DESC
 FETCH FIRST 10 ROWS ONLY
 FOR READ ONLY
 OPTIMIZE FOR 10 ROWS
 WITH CS
 QUERYNO 1201;

FOR READ ONLY

FOR READ ONLY (synonym FOR FETCH ONLY) states that the result table is read-only. Positioned UPDATE and DELETE cannot name this cursor. Some results are already read-only (UNION, GROUP BY, read-only views); the clause is then optional.

For tables that could be updated, specifying FOR READ ONLY lets Db2 use block fetching and avoid exclusive locks. In dynamic SQL without FOR READ ONLY or ORDER BY, Db2 may open the cursor as if FOR UPDATE were specified, which is slower. Production read queries should say FOR READ ONLY (or ORDER BY, which also makes many cursors read-only).

FOR UPDATE

FOR UPDATE OF col1, col2, … lists columns that a later UPDATE … WHERE CURRENT OF cursor may assign. Names are unqualified and must be columns of the table or view in the first FROM clause. Do not specify FOR UPDATE if the result is read-only.

1
2
3
4
5
6
7
8
9
10
DECLARE C1 CURSOR FOR
SELECT EMPNO, SALARY, COMM
  FROM DSN8C10.EMP
 WHERE WORKDEPT = :DEPT
 FOR UPDATE OF SALARY, COMM;
 
-- later
UPDATE DSN8C10.EMP
   SET SALARY = SALARY * 1.05
 WHERE CURRENT OF C1;

If you FETCH columns you will update, FOR UPDATE OF those columns tells Db2 not to use an index on the updating columns in a way that could reread the same row. FETCH with FOR UPDATE typically takes U (update) locks under CS, not S locks. With RR/RS, USE AND KEEP UPDATE LOCKS or EXCLUSIVE LOCKS (or RRULOCK=YES) can take U or X at fetch.

Dynamic SELECT without FOR UPDATE cannot be used in positioned UPDATE. Precompiler options STDSQL(YES) or NOFOR can omit FOR UPDATE on static cursors in some shops; still write it for clarity and locking.

SKIP LOCKED DATA

SKIP LOCKED DATA skips rows (or pages) held with incompatible locks that would block this statement. It applies to row and page locks only—not table, partition, LOB, XML, or table space locks. Isolation must be CS or RS; with UR or RR the clause is ignored.

1
2
3
4
5
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'D11'
 SKIP LOCKED DATA
 WITH CS;

Important IBM warnings:

• Use it only if incomplete results are acceptable
• Db2 does not issue a warning when it skips data
• Lock avoidance may still return committed locked data without waiting—not every lock is skipped
• Page locking skips all rows on the page
• Also valid on searched UPDATE/DELETE: those rows are neither read nor changed

CURRENTDATA

CURRENTDATA is specified on BIND/REBIND (CURRENTDATA(YES) or NO), not as a SELECT keyword. With isolation CS:

• CURRENTDATA(NO) — default for many modern binds; lock avoidance; fetched committed data might be updated by others before your next FETCH
• CURRENTDATA(YES) — the current row remains stable until you FETCH again (stronger “cursor on this row” guarantee)

Ambiguous cursors (dynamic, no FOR READ ONLY) interact with CURRENTDATA. Prefer an explicit FOR READ ONLY or FOR UPDATE so the cursor is not ambiguous.

Isolation clauses

WITH UR | CS | RS | RR on SELECT, SELECT INTO, searched UPDATE, searched DELETE, and INSERT from fullselect overrides the plan/package ISOLATION for that statement.

1
2
3
4
5
6
7
8
9
SELECT MAX(BONUS), MIN(BONUS), AVG(BONUS)
  INTO :MAX, :MIN, :AVG
  FROM DSN8C10.EMP
 WITH UR;
 
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00'
 WITH RS USE AND KEEP UPDATE LOCKS;

With RR or RS on SELECT / SELECT INTO you may add:

• USE AND KEEP SHARE LOCKS — hold S
• USE AND KEEP UPDATE LOCKS — hold U
• USE AND KEEP EXCLUSIVE LOCKS — hold X

Locks are held until commit. That reduces deadlock risk for a follow-on UPDATE of the same rows, at the cost of concurrency. Stage-2 rejected rows under RS might still be locked. IBM’s usual preference order is CS, then UR if dirty reads are OK, then RS, then RR.

QUERYNO

QUERYNO integer tags the statement. EXPLAIN and some monitoring use that number so you can find the same SQL after it is prepared. Pick a stable integer per statement in the program; do not reuse conflicting numbers in one package if you rely on them.

1
2
3
4
5
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE WORKDEPT = :DEPT
 FOR READ ONLY
 QUERYNO 44012;

ORDERING

ORDER BY expressions

A sort-key can be a column of the result, a sort-key-expression, or a column of a FROM table when the subselect has no GROUP BY, DISTINCT, or select-list aggregates.

1
2
3
4
SELECT LASTNAME, SALARY, BONUS, COMM,
       SALARY + VALUE(BONUS, 0) + VALUE(COMM, 0) AS TOTAL_PAY
  FROM DSN8C10.EMP
 ORDER BY SALARY + VALUE(BONUS, 0) + VALUE(COMM, 0) DESC;

You may ORDER BY an expression that is not in the select list. If the query is grouped, a sort-key-expression not in the select list must still be a grouping-expression or live inside an aggregate.

ORDER BY column positions

An unsigned integer n (1 through the number of result columns) sorts by that column. Useful after UNION when names are messy. Do not confuse this with GROUP BY—grouping does not take ordinals.

1
2
3
4
SELECT WORKDEPT, COUNT(*) 
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT
 ORDER BY 2 DESC, 1;

ASC is the default. DESC reverses. Null ordering follows your Db2 null sort rules (nulls typically sort high).

ROWS ONLY and FETCH

FETCH FIRST n ROWS ONLY (and FETCH NEXT / OFFSET in later versions) limits the result. ROWS ONLY means exactly those n rows, not WITH TIES. OPTIMIZE FOR n ROWS is a hint for access-path costing; FETCH FIRST is a hard cap. Use both when the application will show a page of n rows.

1
2
3
4
5
6
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 ORDER BY SALARY DESC
 FETCH FIRST 5 ROWS ONLY
 OPTIMIZE FOR 5 ROWS
 FOR READ ONLY;

Explain It Like I'm Five

The SELECT is asking for toys. FOR READ ONLY means “I am only looking.” FOR UPDATE means “I might rewrite the price tag, so keep your hand on this toy.” SKIP LOCKED DATA means “if another kid is hugging a toy, skip it and do not wait.” WITH UR is peeking at toys someone might put back. ORDER BY 2 is “sort by the second thing I asked for.” QUERYNO is writing a number on your homework so the teacher can find it later.

Exercises

1. Rewrite a dynamic SELECT used only for display so it cannot be treated as FOR UPDATE.
2. Declare a cursor FOR UPDATE OF SALARY and write the matching positioned UPDATE.
3. State what happens if you add SKIP LOCKED DATA WITH UR.
4. Contrast WITH CS and WITH RR for a report that must not see new rows inserted during the transaction.
5. ORDER BY a salary-plus-bonus expression that is not in the SELECT list.