We know about the 4 isolation levels in DB2, namely RR,RS,CS,UR. One thing which all these isolation level have in common is Locking. Locks in Databases serves the same purpose as they do in our house or cars.They Decide who can or can not access a resource like Table, Table Space,Rows etc. The DB2 Database manager imposes locks to prohibit "owning" transaction from accessing uncommitted data written by other application, and also to prevent other transactions to make any change to the currently owned rows.
Data Base manager locks have these 4 basic attributes:
1. SIZE - It indicates the physical size of the portion of data resource that is being locked.
Possible sizes are : Table space, Table, Partition, pages.
2. MODE - Type of access allowed for lock owner as well as the type of access permitted for concurrent users of the locked object
3. OBJECT - Resources being locked. The only type of explicitly lockable objects are tables. The database manager also imposes locks on other type of resources such as Rows,tables, and Table spaces.
4. DURATION - The length of time a lock is held. LOCK DURATIONS are affected by Isolation level.
ex: LOCK acquired by for RR(repeatable Read) transaction is likely to be more than lock acquired by CS transaction.
1.SIZE:The DB2 resources on which lock can be places are as below in ascending order of their sizes.
From Highest (Most data locked) to lowest( least data locked),the most common lock sizes are
A. TS
B. TABLE.
C.PAGE
D.ROW
Point to Note: Locks can be taken at any level in the locking hierarchy without taking a lock at the lower level. However, locks cannot be taken at the lower levels without a compatible higher-level lock also being taken. For example, DB2 can take a table space lock without taking any other lock, but DB2 cannot take a page lock without securing TS lock.
One common batch abend involving DB2 locking is like below:
DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = Tables-space-name
DSNUGASU - IRLM LOCK REQUEST FAILED,
IRLM RETURN CODE = X'00000008'
IRLM REASON CODE = X'2000'
This happens because one job was trying to acquire a DB2 resource, but that is being locked by other job. IRLM sets a timeout period, after that it terminates the execution and job fails with above mentioned error-code. DBA can provide information about the resource being used and the offending job what is causing the problem.Once that job is complete, we can restart our job from abend step.
2.Lock Modes:
Lock Modes at Table level are as follows:
IS : INTENT SHARE: The Lock owner can read any data in the table if an S lock can be obtained on the target rows or page.
IX: INTENT EXCLUSIVE: The lock owner can read or change any data in the table provided an X lock can be obtained on rows or pages to be changed and a U or S lock can be obtained on rows or pages to be read.
S : SHARE : The lock owner can read any data in the table and will not obtain row or page locks
U: UPDATE: The lock owner can read any data in the table and may UPDATE OR CHANGE data if an X lock on the table is obtained. No page or row locks are obtained.
X:EXCLUSIVE: The lock owner can read or update data in the table. Row or Page locks are not obtained.
ROW or PAGE lock modes:
S : SHARE : The Row or Page is being READ by one application and is available for READ-ONLY by other applications.
U:UPDATE : Row or page is currently READ by one application and it might get changed by that application itself. U lock will support cursors that are opened with FOR UPDATE OF clause.
X: EXCLUSIVE: The Row or PAGE is being changed by application and IS NOT available for other application, except those that permit UNCOMMITTED READ.
Indexes are not locked, because their serialization is controlled by latches and concurrency is controlled by data locking.
Skip Locked Data
The SKIPPED LOCKED DATA option lets a transaction skip rows that are incompatibly locked by other transactions. This option can help improve the performance of some applications by eliminating lock wait time. You should use this technique only for applications that don't require unavailable or uncommitted data. Transactions using this option will not read or modify data that is unavailable, uncommitted, or held by locks.
We can specify the SKIP LOCKED DATA option in a SELECT, SELECT INTO, PREPARE, searched UPDATE, or searched
DELETE statement. You can also use it with the UNLOAD utility. The option works with isolation level CS or RS. It is ignored for isolation levels UR and RR.
The next example returns a count of only the rows that are not uncommitted (if no index exists on CUST_NO).
SELECT COUNT(*)
FROM CUSTOMER
WHERE CUST_NO >= 500
SKIP LOCKED DATA
