DB2 9.5 and DB2 9.7 Locking Tips

DB2 Locking basics

In any relational database, in order to maintain consistency among the transaction locks need to be acquired on the resource on which the transaction will be executed. Resource can be a Relational Table, or Rows. Locking provide isolation of resource between transaction thereby providing concurrency between independent transaction. DB2 provides four isolation levels.

• Uncommitted Read
• Cursor Stability
• Read Stability
• Repeatable Read
• Currently committed (New in DB 9.7)

In order to utilize the isolation level in the transaction, they can be specified at
1) Statement level (SELECT, DELETE, UPDATE)
eg:- SELECT FROM WITH {UR, CS, RR, RS}

2) At session level
eg:- SET CURRENT ISOLATION = {UR, CS, RR, RS}


Commonly referred LOCKING terms

1) Locks
In order to use any resource, DB2 lock the resource before the transaction can use it.


2) Lock-Wait
If any transaction is requesting lock on a resource, and that resource is currently being held by other transaction, then the application requesting the lock goes into LOCK-WAIT mode until the lock on the resource is released by the primary transaction.

3) Deadlock
 If two transaction are waiting on each others resources the the both the transaction goes into deadlock state.

 4) Lock Escalation
 For each lock acquired on a resource, DB2 maintain that information in DB2 locklist memory component. If the locklist memory started getting exhausted, DB2 escalate all row level lock to table level lock in order to release memory.

How DB2 locking can be monitored?

1) Monitor switches
2) GET SNAPSHOT command
3) db2pd tool
4) Snapshot monitor SQL administrative routines
5) DB2_CAPTURE_LOCKTIMEOUT ( New in DB2 9.5 locking) Practical Use of DB2_CAPTURE_LOCKTIMEOUT
6) db2cos scripts
7) db2pdcfg with -catch option
8) Locking event monitor as apposed to Deadlock event monitory (New in DB2 9.7 Locking)

Lock related DB CFG parameter

1) LOCKLIST
2) MAXLOCK
3) LOCKTIMEOUT
4) DLCHKTIME

Lock related registry variable

1) DB2LOCK_TO_RB
2) DB2_KEEPTABLELOCK
3) DB2_MAX_NON_TABLE_LOCKS
4) DB2_EVALUNCOMMITED
5) DB2_SKIPDELETED
6) DB2_SKIPINSERTED


Reference URLs:

1) Example to understand the DB2 Locking Mechanism

2) Example on DB2 Snapshot and Event monitors

3) Whitepaper describing technique to Diagnose and resolve lock problems

 4) DB2 9.5 lock monitoring - Part 1

 5) DB2 9.5 Lock monitoring - Part 2

 6) DB2 9.5 Optimistic Locking ( New in DB2 9.5)