DB2 offers four locking isolation levels: Repeatable Read (RR), Read Stability (RS), Cursor Stability (CS), and Uncommitted Read (UR). Read my previous post on basics of isolation levels.
DB2 Four Isolation Levels.
Each of these isolation levels allows the user and application to control the number and duration of read (Share) locks held within a unit of work.
When you set the appropriate isolation level, based on a particular application’s requirement, lock resources can be minimized, and the user/program concurrency can be increased. Take the following example:
SELECT LASTNAME, EMPNO
FROM EMP
WHERE LASTNAME LIKE 'S%'
WITH UR1. Repeatable Read.
With RR in DB2 means that the same query can be executed multiple times within the same unit of work, and the results of the query will be identical every time (repeatable).
A Share lock will be set and will stay on each row or page until the query or logical unit of work has completed.
All accessed rows or pages are locked, even if they do not satisfy the predicate.
“For table scans, this would encompass each row/page within the table. For other queries not processing table scans, this would encompass any rows or pages that meet the predicate criteria of the SQL statement. “
- In the example above, this would be all rows or pages containing last names that begin with S.
- All Share locks with RR are held until a commit takes place. These share locks would effectively prevent updates, inserts, or deletes (X locks) from occurring on any of the rows/pages from any other process until a commit is executed.
- This is a common reason for many -911 SQLCODE errors.
- Note Most query tools on the market have their default isolation level set to RR, which is not good. This causes many problems in environments where users, analysts, developers, and others query the data often during the day.
- Many times users leave their workstations while a query running in the background is applying and holding locks on the data being retrieved.
2. Read Stability.
With RS in DB2 is much like With RR in DB2, except that it will allow inserts from other users.
- It can at times lock more rows/pages because locks are taken and held on data, even when it goes to stage 2 processing to further check predicates.
- If there is a stage 2 predicate and the data does not fit the predicate criteria, the RS lock is still placed and held.
3. Cursor Stability.
With CS in DB2 sets a Share lock on each row or page processed, and the moment the cursor moves on to another row or page, it releases the lock.
So at any one time, there is only one lock being held either on a row or page of data. This obviously allows good concurrency and some data integrity.
- Almost all batch COBOL programs in IT shops today are bound with the locking parameter CS.
- This is because as these programs execute cursor processing, they have no need to reread any data processed. The Share locks get freed up as the query moves through the cursor, and the query has data integrity as it processes each current row or page.
- This bind parameter, along with another bind parameter, Currentdata(No), provides an opportunity for avoiding locks altogether.
- With these two bind parameters together, DB2 can test whether a row or page has committed data on it, and if it has, DB2 will not have to obtain any lock.
4. Uncommitted Read
With UR in DB2 means that no Share locks are placed on any rows or pages processed by this query, and it does not matter if other processes have any locks on any of the data being retrieved.
- This can improve efficiency because it reduces overall processing time. But the one issue in using UR is that if some other process has applied updates to data being retrieved, UR will return the updated data from the buffer before the other process has executed a commit.
- If for some reason the other process does a rollback of its updates, then this UR process has updated data that was never committed.
- Even with the issue of possibly picking up non-committed data, there are definitely times when UR can be used.
Keep Reading
Srinimf 