2009-08-11-09.20.20.253256 Instance:db2inst1 Node:000
PID:327876(db2agent (DB_NAME) 0) TID:1 Appid:*LOCAL.db2inst1.0600D1010730
data management sqldEscalateLocks Probe:4 Database:DB_NAME
ADM5503E The escalation of "2066409" locks on table "ODS .FHDSCKFHZ" to
lock intent "X" has failed. The SQLCODE is "-911".
2009-08-11-09.23.51.119827 Instance:db2inst1 Node:000
PID:327876(db2agent (DB_NAME) 0) TID:1 Appid:*LOCAL.db2inst1.0600D1010730
database utilities sqluRegisterLoadStart Probe:30 Database:DB_NAME
Load Error: Unable to obtain table lock
2009-08-11-09.23.51.174296 Instance:db2inst1 Node:000
PID:327876(db2agent (DB_NAME) 0) TID:1 Appid:*LOCAL.db2inst1.0600D1010730
database utilities sqluLoadPartition Probe:50 Database:DB_NAME
Load Error: Error loading table.
2009-08-11-09.23.51.217721 Instance:db2inst1 Node:000
PID:327876(db2agent (DB_NAME) 0) TID:1 Appid:*LOCAL.db2inst1.0600D1010730
database utilities DIAG_ERROR Probe:0 Database:DB_NAME
LOADID: 327876.2009-08-11-09.22.51.087190.0 (5;17)
Error acquiring partition resources. -2146435004, fffffc71, Detected in file: sqluvtld.C,line 1092
2009-08-11-09.23.51.253751 Instance:db2inst1 Node:000
PID:327876(db2agent (DB_NAME) 0) TID:1 Appid:*LOCAL.db2inst1.0600D1010730
database utilities DIAG_ERROR Probe:0 Database:DB_NAME
LOADID: 327876.2009-08-11-09.22.51.087190.0 (5;17)
sqluv_rollback 0, 42, Detected in file: sqluvutl.C,line 1135
2009-08-11-09.23.51.289646 Instance:db2inst1 Node:000
PID:327876(db2agent (DB_NAME) 0) TID:1 Appid:*LOCAL.db2inst1.0600D1010730
database utilities sqlu_register_table_load Probe:50 Database:DB_NAME
Load Error: Error acquiring partition resources.
2009-08-11-09.23.51.625645 Instance:db2inst1 Node:000
PID:213216(db2lrid 0) TID:1 Appid:*LOCAL.db2inst1.0600D1010730
database utilities DIAG_NOTE Probe:0 Database:DB_NAME
红色部分,很清楚,锁升级!然后有失败了,失败的原因要看Reason Code, 这里的情况是rc68 即lock timeout, 简单的解决办法是增加LOCKTIMEOUT 但是这并不好,如果内存还有剩余的话还是增加LOCKLIST比较好一点,当然了,如果没办法增加LOCKLIST的话,那就得从程序处着手了:
Perform frequent COMMITs to release locks.
When performing many updates, lock the entire table before updating
(using the SQL LOCK TABLE statement). This will use only one lock, keeps
others from interfering with the updates, but does reduce concurrency of
the data.
You can also use the LOCKSIZE option of the ALTER TABLE statement to
control how locking is done for a specific table.
Use of the Repeatable Read isolation level may result in an automatic table
lock.
v Use the Cursor Stability isolation level when possible to decrease the
number of share locks held. If application integrity requirements are not
compromised use Uncommitted Read instead of Cursor Stability to further
decrease the amount of locking.
客户原来db参数设置为:
Max storage for lock list (4KB) (LOCKLIST) = 50000
Percent. of lock lists per application (MAXLOCKS) = 50
Lock timeout (sec) (LOCKTIMEOUT) = 60
对LOCKLIST进行了更改,直接double
现在没有了锁升级的报警了, 对于上面的配置可以算出这些内存空间最多可以锁住多少行的数据:
50000*4K*50%=102400000 Byte
再看文档上,On 32-bit platforms, each lock requires 36 or 72 bytes of the lock list,
depending on whether other locks are held on the object:
On 64-bit platforms, each lock requires 56 or 112 bytes of the lock list,
depending on whether other locks are held on the object:
对于我们客户的具体情况,最多可以锁住 102400000/36=2844444 两百多万行,这个结果和我在db2diag.log里面看到的也是吻合的,没有超过这个值的锁升级成功,而在我们改过参数后都有五百多万行锁升级成功的记录:
2009-08-12-08.08.26.475084 Instance:db2inst1 Node:000
PID:987140(db2agent (DB_NAME) 0) TID:1 Appid:*LOCAL.db2inst1.070D92000632
data management sqldEscalateLocks Probe:3 Database:DB_NAME
ADM5502W The escalation of "5349632" locks on table "ODS .DJKXHDJB" to
lock intent "X" was successful.
PS:以前公司的db2数据库设置的lock方面的值都很小:
Max storage for lock list (4KB) (LOCKLIST) = 20000
Percent. of lock lists per application (MAXLOCKS) = 15
不过这些都是OLTP的系统
阅读(1549) | 评论(0) | 转发(0) |