对客户locklist的调整记录

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的系统

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13165828/viewspace-615294/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13165828/viewspace-615294/