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
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 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)
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 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) 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
Lock monitoring - to get more details click here
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
Lock monitoring - to get more details click here
No comments:
Post a Comment