getting too much locks and lock escalation??????????
Up to DB2getting too much locks and lock escalation??????????
Posted by Pawan at April 25. 2008Below are the parameters related to locks...
Max storage for lock list (4KB) (LOCKLIST) = 100
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1
I am getting locks snapshot below....
Locks held currently = 7
Lock waits = 1940
Time database waited on locks (ms) = Not Collected
Lock list memory in use (Bytes) = 24960
Deadlocks detected = 2
Lock escalations = 174
Exclusive lock escalations = 174
Agents currently waiting on locks = 0
Lock Timeouts = 0
Number of indoubt transactions = 0
below warning are getting in notification log file...
ADM5500W DB2 is performing lock escalation. The total number of locks
currently held is "1034", and the target number of locks to hold is "517".
2008-04-24-22.01.03.801820 Instance:idsldap Node:000
PID:12926(db2agent (IDSLDAP) 0) TID:3086919360 Appid:*LOCAL.idsldap.080424163103
data management sqldEscalateLocks Probe:3 Database:IDSLDAP
ADM5502W The escalation of "987" locks on table "SYSIBM .SYSCOLDIST" to lock
intent "X" was successful.
2008-04-24-22.01.09.922015 Instance:idsldap Node:000
PID:12926(db2agent (IDSLDAP) 0) TID:3086919360 Appid:*LOCAL.idsldap.080424163103
data management sqldEscalateLocks Probe:2 Database:IDSLDAP
ADM5500W DB2 is performing lock escalation. The total number of locks
currently held is "1031", and the target number of locks to hold is "515".
2008-04-24-22.01.09.922235 Instance:idsldap Node:000
PID:12926(db2agent (IDSLDAP) 0) TID:3086919360 Appid:*LOCAL.idsldap.080424163103
data management sqldEscalateLocks Probe:3 Database:IDSLDAP
ADM5502W The escalation of "509" locks on table "SYSIBM .SYSCOLUMNS" to lock
intent "X" was successful.
2008-04-24-22.01.09.922671 Instance:idsldap Node:000
PID:12926(db2agent (IDSLDAP) 0) TID:3086919360 Appid:*LOCAL.idsldap.08042416310
I am getting daily these warning....
How we can remove these warnings???what action I should take????
Is reorg related to locks?????
If we reorg those tables then chances to remove those locks????
Thanks
Pawan
Re: getting too much locks and lock escalation??????????
Posted by perallis at April 25. 2008In the first moment, check the type of index you have:
http://www.dbatodba.com/db2/how-to-do/how-to-identify-the-type-of-index-on-db2
If you have some Type-1 indexes, please transform to type-2, monitor your database and if the error occur again, paste your comment here.
Thanks.
Re: getting too much locks and lock escalation??????????
Posted by Pawan at April 26. 2008I checked ..all are type-2.....getting below warning......is reorg related to locks????
if i do reorg on these tables...can i remove locks ahead??????
ADM10501W Health indicator "Lock Escalation Rate" ("db.lock_escal_rate")
breached the "upper" warning threshold of "5 Lock escalations per hour" with
value "7 Lock escalations per hour" on "database" "carsdb.EVENTXML".
Calculation: "delta(db.lock_escals,12);" = "(179 - 172)" = "7 Lock escalations
per hour". History (Timestamp, Value, Formula): " (04/26/2008 09:04:11.944255,
4, (176 - 172)), (04/26/2008 08:59:11.700676, 0, (172 - 172)), (04/26/2008
08:54:11.564008, 0, (172 - 172)), (04/26/2008 08:49:11.456531, 0, (172 - 172)),
(04/26/2008 08:44:11.230953, 0, (172 - 172)), (04/26/2008 08:39:11.081149, 0,
(172 - 172)), (04/26/2008 08:34:11.853391, 0, (172 - 172)), (04/26/2008
08:29:11.710363, 0, (172 - 172)), (04/26/2008 08:24:11.536771, 0, (172 - 172))"
Re: getting too much locks and lock escalation??????????
Posted by perallis at April 28. 2008Pawan,
In this case, your LOCKLIST VALUE is very low.
Try to increase the LOCKLIST parameter to 1000 and monitor your database again.
See the recommendation to the LOCKLIST VALUE in the link below:
Info Center IBM DB2
If you increase the LOCKLIST many times and the problem not fix, follow the steps below:
Find which are the applications that are causing locks.
Bad applications can cause several locks.
The link below shows how you can identify the applications which are holding locks.
http://www.dbatodba.com/db2/problem-resolution/locks/lock-wait-and-lock-timeout
If a applications has few Commits, please increase the number of commits.
Warning: If the application are executing a huge number of updates and inserts, you will need a lot of commits.