The ISOLATION (UR) option

The ISOLATION (UR) or uncommitted read option allows an application to read while acquiring few locks, at the risk of reading uncommitted data. UR isolation applies only to the following read-only operations: SELECT, SELECT INTO, or FETCH from a read-only result table.

Reading uncommitted data introduces an element of uncertainty.

For example, an application tracks the movement of work from station to station along an assembly line. As items move from one station to another, the application subtracts from the count of items at the first station and adds to the count of items at the second station. Assume that you want to query the count of items at all the stations, while the application is running concurrently.

If your query reads data that the application has changed but has not committed:
  • If the application subtracts an amount from one record before adding it to another, the query could miss the amount entirely.
  • If the application adds first and then subtracts, the query could add the amount twice.

If those situations can occur and are unacceptable, do not use UR isolation.

Restrictions for using ISOLATION (UR)

You cannot use the ISOLATION (UR) option for the following types of statements:

  • INSERT, UPDATE, DELETE, and MERGE
  • SELECT FROM INSERT, UPDATE, DELETE, or MERGE.
  • Any cursor defined with a FOR UPDATE clause

If you bind with ISOLATION(UR) and the statement does not specify WITH RR or WITH RS, Db2 uses CS isolation for these types of statements.

When to use ISOLATION (UR)

You can probably use UR isolation in cases such as the following examples:
When errors cannot occur
The follow examples describe situations in which errors can be avoided while using the ISOLATION(UR) option.
Reference tables
Like a table of descriptions of parts by part number. Such tables are rarely updated, and reading an uncommitted update is probably no more damaging than reading the table 5 seconds earlier.
Tables with limited access
The employee table of Spiffy Computer, our hypothetical user. For security reasons, updates can be made to the table only by members of a single department. And that department is also the only one that can query the entire table. It is easy to restrict queries to times when no updates are being made and then run with UR isolation.
When an error is acceptable
Spiffy Computer wants to do some statistical analysis on employee data. A typical question is, What is the average salary by sex within education level? Because reading an occasional uncommitted record cannot affect the averages much, UR isolation can be used.
When the data already contains inconsistent information
Spiffy computer gets sales leads from various sources. The data is often inconsistent or wrong, and users of the data are accustomed to dealing with that problem. Inconsistent access to a table of data on sales leads does not add to the problem.

When not to use ISOLATION (UR)

Do not use uncommitted read, ISOLATION (UR), in the following cases:

  • When computations must balance
  • When the answer must be accurate
  • When you are unsure whether using the ISOLATION (UR) might cause damage