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 untilthe 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 locklistmemory 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) db2pdcfg with -catch option

7) 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