问题描述
我有db2 9安装，我认为我们的应用程序没有处理
最佳锁定。
我有Maxlocks（96）和locklist（ 104832）设置为自动调谐。括号中这些参数的
值是运行我的应用程序一段时间后自动调整的值
。
我注意到db2diag中的值.log这些值正在增加
以防止锁升级。
如何确定导致这些高升级的sql？
从性能的角度来看，更好地锁定
相关的表格吗？
在具有4G内存的机器上 - 是否有规则大概如何分配
应该为锁定分配多少内存？ - locklist和maxlocks？
如果我将locklist和maxlocks设置为较低的值并且监视器
db2diag.log我将得到导致锁定的有问题的sql
升级？
谢谢。
I have db2 9 installation and I think our application isn''t handling
locking optimally.
I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
value of these parameters in the parenthesis are the auto tuned values
after running my application for a while.
I notice values in db2diag.log that these values are being increased
to prevent lock escalation.
How do I determine the sql that is causing these high escalations?
Would it be better from a performance standpoint to specifically lock
the table in question instead?
On a machine with 4G of memory - is there a rule of thumb of about how
much memory should be allocated for locking? - locklist and maxlocks?
If I set locklist and maxlocks to a lower value and monitor
db2diag.log will I get the offending sql that is causing the lock
escalation?
Thanks.
推荐答案
这是一个庞大的锁定列表。您是否有数据仓库应用程序，
或OLTP应用程序（包含大量更新，插入和删除）？如果你有几乎所有的选择，你可能不想避免锁升级，因为
表上的多个共享锁共存很好，或者甚至可能使用UR隔离
等级。
That is a huge amount of locklist. Do you have a data warehouse application,
or an OLTP application (with lots of updates, inserts, and deletes)? If you
have almost all selects, you may NOT want to avoid lock escalation since
multiple share locks on table coexist fine, or maybe even use UR isolation
level.
您的应用程序使用什么隔离级别？
What isolation level is your application using?
获取< db>上的应用程序的快照
和
获取锁定的快照< db>
将给你一个开始
/ Lennart
get snapshot for applications on <db>
and
get snapshot for locks on <db>
will give you a start
/Lennart
这是一个庞大的锁定列表。您是否有数据仓库应用程序，
或OLTP应用程序（包含大量更新，插入和删除）？如果你有几乎所有的选择，你可能不想避免锁升级，因为
表上的多个共享锁共存很好，或者甚至可能使用UR隔离
等级。
That is a huge amount of locklist. Do you have a data warehouse application,
or an OLTP application (with lots of updates, inserts, and deletes)? If you
have almost all selects, you may NOT want to avoid lock escalation since
multiple share locks on table coexist fine, or maybe even use UR isolation
level.
这两者都是。这个应用程序是一个oltp应用程序，但它将数据
移动到报告模式中。
基本上，我将事务移动到基于星型模式的报告中
架构。
隔离级别是DB2在使用DB2
jdbc驱动程序时选择的内容。
It''s both. This application is a oltp application but it moves data
into a reporting schema.
Basically, I move transactions into a star schema based reporting
schema.
The isolation level would be whatever DB2 chooses when using the DB2
jdbc driver.
这篇关于锁定升级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！