DB2 Isolation level

The Isolation Level in DB2 determines the extent of the locks to be held. In sequence from fewest locks to most locks, the values of Isolation Level are UR (Uncommitted Read), CS (Cursor Stability), RS (Read Stability) and RR (Repeatable Read). The isolation level of a program is set when the bind is done as an option to the bind. The default value for a bind of a PLAN is RR, while the default for a package is that of that plan to which is bound. A package can have a different isolation level than a plan that uses it. The isolation can used on select clause (WITH UR, or WITH CS). Uncommitted read cannot be specified on a statement that changes data (Insert, update or delete). Other options on a declare cursor have implications with regard to the number of rows or pages locked. Java language program use a different mechanism to set the isolation, with conflicting terminology (more on this later). 

When you use SPUFI, the isolation level is set in the Options panel. The default is RR. The first time that you run SPUFI, you are likely to think that you “can’t run SPUFI” because no one is authorized to run SPUFI with RR: you will need to change the default value to CS. RR is the safest for you to use, but it has the most impact on everyone else (it basically locks all the rows or pages that you might be using). The only recommended option for SPUFI is CS. 

The Current Data can be set to Yes or No at bind time (for a plan or package). Current data means that the data that is fetched cannot be changed while it has been fetched. If the server is local, there is a difference only for read-only ambiguous cursors (those that specify neither “for read only” nor “for update of …”). For those cursors, the data cannot be changed while it is fetched. Of course, if the cursor is fetching from a work file, the data cannot be changed anyway. If the server is remote from the client, current data yes will prevent the data from being fetched a block at a time (ambiguous cursors only –  read only and UR cursors are always block fetched). For best performance, it is strongly recommended that you always use a bind option of Current Data NO, and specify “for read only”  in your cursor declaration whenever possible. 

Uncommitted Read holds the fewest possible locks. With UR, you can be retrieving rows that someone else has updated or inserted, but not yet committed. If someone else insert 1000 rows, and then later rolls back their changes, you might still see those rows (even though they were never really in the database). UR should only be used for applications that don’t care about the consistency of the data. UR should never be used for queries or selects for financial transactions. 

Repeatable Read is the default and is the safest from the point of view of “if you don’t know, make the safest choice,” yet it has the most impact on other users of the data. RR locks all rows that MIGHT be part of the result set, in other words, all rows that retrieved to examine them for qualification against the where clause. RR is intended to be used if you open a cursor a second time with the same where clause values, it should retrieve exactly the same rows that it did the first time. If you have a program design that opens a cursor, counts the rows, then reopens the cursor and processes that number of rows, you need to use RR. You avoid this problem during the design phase, by designing the program to process the cursor only once. It is safest because the database doesn’t know what your program design is: can you tolerate getting different result sets? It is a problem because all rows that might be part of the result set will remain locked until you commit. 

Read Stability is somewhat similar in that it locks all rows that ARE part of the result set. While RR locks all rows that are examined, RS, locks only those rows that qualify. With RS, if you reopen the cursor, you might see more rows the second time, but you will never see fewer rows. Once again, you can and should design to avoid the use of RS. No all servers support RS: those that don’t treat it as RR. 

Cursor Stability locks only the row or page that has been fetched. DB2 will lock the row and then fetch the row. When you issue the next fetch, DB2 will unlock the row, lock the next and then fetch the contents. If you are using Current Data NO and fetch data with CS concurrency, in most case DB2 can avoid taking locks entirely, so long as it is read only. For remote applications in these circumstances, DB2 will use block fetch and move many rows over the network simultaneously. Thus for best performance and maximum concurrency, you should always use CS in conjunction with current data NO. 

In order to achieve the maximum concurrency in the database, all applications should designed to use, and be using isolation level CS and Current Data NO. Cursors should be declared as FOR READ ONLY, or FOR UPDATE OF …. so that there are no ambiguous cursors. Commits should be frequently issued. Commits should not include less than one complete unit of work, nor should they be less frequent than 1000 changes to a single table. These specifications need to be considered in a project from early in the design phase and throughout a project. If they were not considered in the design, it is difficult to consider them later. But these principles are critical to getting good from a DB2 database.