Isolation Levels

The isolation level decides how strongly your program is separated from changes made by other programs. Stronger isolation means more locking and slower concurrency. Weaker isolation means less locking but you may see other people's uncommitted data.

The four isolation levels

• RR - Repeatable Read: strongest. No lost updates, no dirty reads, no nonrepeatable reads, no phantom reads. Locks are held till commit.
• RS - Read Stability: like RR, but phantom reads are possible. New rows added by others can appear in a second read.
• CS - Cursor Stability: the DB2 default. Only the current cursor row is locked. Nonrepeatable and phantom reads are possible.
• UR - Uncommitted Read: weakest and fastest. The program can read uncommitted changes (dirty reads). Use only for read-only reports where approximate data is fine.
• The phenomena table:-
  Isolation Level Lost Dirty Nonrepeatable Phantom Updates Reads Reads Reads Repeatable Read No No No No Read Stability No No No Yes Cursor Stability No No Yes Yes Uncommitted Read No Yes Yes Yes

How to set the isolation level

• Set it at bind time with the ISOLATION parameter, for example ISOLATION(CS). This applies to the whole package.
• Override it for one query with the WITH UR clause at the end of a SELECT. This is handy for reports.
• ACQUIRE and RELEASE on the BIND work together with isolation to control lock duration.
• Example:-
  -- read-only report, no locks taken SELECT EMPNO, LASTNAME, SALARY FROM EMP WITH UR; -- bind time setting (JCL BIND step) -- BIND PACKAGE(MYCOLL.MYPKG) ISOLATION(CS) ...

Which level to choose

• Use CS for normal online programs. It is the default and the best balance for most work.
• Use RR only when the program must see exactly the same data on repeated reads, like a totals program that reads a table twice.
• Use RS when you need repeatable reads of existing rows but do not care about newly inserted rows.
• Use UR for read-only reporting and queries against tables that change rarely. Never use UR for money or inventory balances.