DB2 isolation levels: CS, UR, RS, and RR

Why isolation exists

Without isolation rules, one program could read a salary another program has updated but not committed — and that update might roll back. Isolation also decides whether you can re-read a row and see the same value, and whether a new row can appear in a result you thought was finished (a phantom).

You choose isolation on BIND (ISOLATION option on the package or plan) and you can override it on a statement with WITH UR, WITH CS, WITH RS, or WITH RR. Statement isolation is the usual way to run one dirty-read COUNT while the rest of the package stays CS.

Cursor stability (CS)

CS is the default and IBM’s first recommendation. Like RR, you do not read a row another process has changed until that process releases it. Unlike RR, CS does not keep every row you ever looked at locked until commit. A lock is usually taken to prove the row is committed; when you FETCH the next row, the previous row or page can be unlocked. Stability lasts only while the cursor is on that row (unless you changed the row — then the X lock lasts until commit).

CS allows maximum concurrency with data integrity for typical OLTP: you see committed data, you do not hold the whole table, and other users can update rows you have already left. You can still see non-repeatable reads and phantoms if you scan again before commit.

1
2
3
4
SELECT EMPNO, LASTNAME, SALARY
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
  WITH CS;

Uncommitted read (UR)

UR does not promise much. Except for LOB data, it avoids acquiring locks on data and can see uncommitted inserts and updates. It is fast and causes little contention. Logical inconsistencies are possible: you might report a row that never commits.

UR is legal only for read-only operations: SELECT, SELECT INTO, or FETCH on a read-only cursor. If you specify UR on an updating operation, Db2 uses CS for that operation. Do not bind a money-moving package ISOLATION(UR).

1
SELECT COUNT(*) FROM HR.EMPLOYEE WITH UR;

Read stability (RS)

RS keeps locks on every row that qualified until your application commits or rolls back. Other processes cannot update or delete those rows. They can insert rows that would have qualified, or update a non-qualifying row so it now qualifies — a phantom on a second read. If a remote server does not support RS, it uses RR.

Use RS when the set of rows you already accepted must not change, but you do not need a frozen table.

Repeatable read (RR)

RR locks every row or page you accessed, including those that failed the predicate, and holds those locks until commit. Nobody else can update, insert, or delete in a way that would change the answer set. Re-running the same query in the same UR returns the same membership. That stability costs concurrency: RR is last on IBM’s preference list.

WITH RR USE AND KEEP UPDATE LOCKS (or EXCLUSIVE, or SHARE) tells Db2 which lock type to take and keep until commit, even on fetches. That is a deliberate contention choice for “I will update these next.”

CURRENTDATA

CURRENTDATA applies with ISOLATION(CS) to read-only and ambiguous cursors. It trades concurrency against “is this fetch still the live row?”

• CURRENTDATA(NO) — typically the fewest locks; enables lock avoidance. IBM recommends this with CS for most applications.
• CURRENTDATA(YES) — data returned to the application must not be changed before the next FETCH. Use when the program looks at a row and immediately decides based on that image still being current.

Lock avoidance is Db2 proving a page is committed using log-range information instead of always getting an S lock. CURRENTDATA(NO) plus CS is the usual way to get that benefit. It is not UR: you still should not see uncommitted changes.

Currently committed

Even with CS, a reader can wait on a row another transaction has inserted or deleted but not committed. Currently committed access lets the reader continue:

• Uncommitted INSERT — skip the row (it is not committed yet)
• Uncommitted DELETE — return the last committed version of the row
• Uncommitted UPDATE — readers may still wait; currently committed on z/OS is aimed at insert/delete, not a free pass around every updater

Bind CONCURRENTACCESSRESOLUTION(USECURRENTLYCOMMITTED) or WAITFOROUTCOME. The SQL clause USE CURRENTLY COMMITTED / WAIT FOR OUTCOME does the same for a statement. It applies when isolation is CS or RS. SKIPUNCI (SKIP UNCOMM INSERTS) at the subsystem can skip uncommitted inserts even when the bind option is not specified. USECURRENTLYCOMMITTED needs a universal table space in supported cases; otherwise Db2 may wait anyway.

Lock conversion and lock structure

Isolation chooses how long and which rows you lock. The lockmode (S, U, X, IS, IX, SIX) is a separate axis. Conversion is changing mode while you still hold the object: an S or U lock on a row you then UPDATE becomes X. Intent locks on the table space (IS/IX) convert when your activity changes from read to write.

Those modes live in the lock structure managed by IRLM (and, in data sharing, the coupling-facility lock structure). Isolation does not replace IRLM; it tells IRLM when it may release a share lock after FETCH versus when it must keep it until commit. Details of S/U/X and durations belong on the lock-types page; here the takeaway is: RR and RS convert a short CS-style hold into a commit-duration hold on many more objects.

Explain It Like I'm Five

Isolation is the library rule for a book you are reading. UR means you may read a page someone is still erasing. CS means you only read ink that has dried, but when you turn the page someone else may write on the page you left. RS means every page you decided to keep stays untouched until you leave the library, but new books can still be wheeled in. RR means the whole shelf you walked past is frozen, even the books you did not pick, so nobody can sneak a new book into your pile. CURRENTDATA(NO) is letting the librarian use a stamp instead of chaining every book to your wrist. Currently committed means if someone is halfway through putting a book on the shelf, you skip it and keep walking.

Exercises

1. Name the four z/OS isolation levels from most concurrent to most stable.
2. Write a SELECT that counts rows WITH UR and explain a dirty-read risk.
3. When would you bind CURRENTDATA(YES) instead of NO?
4. How can a phantom appear under RS but not under RR?
5. Explain USE CURRENTLY COMMITTED for an uncommitted INSERT versus WAIT FOR OUTCOME.