DB2锁监控的几种方式

最新推荐文章于 2025-06-17 16:01:58 发布

Easylove-xjl

最新推荐文章于 2025-06-17 16:01:58 发布

阅读量1.6k

点赞数

CC 4.0 BY-SA版权

分类专栏： DB2

本文链接：https://blog.youkuaiyun.com/easylovexjl/article/details/87191913

本文介绍了DB2数据库锁监控的三种方式：通过性能视图sysibmadm.snapappl、sysibmadm.snapappl_info、sysibmadm.lockwaits等获取锁信息；使用DB2TOP工具观察LockUsed、LockEscals和LockWait，深入分析锁等待和锁链；并提供了相关参考材料链接以供进一步学习。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 使用性能视图
相关视图如下：
sysibmadm.snapappl
sysibmadm.snapappl_info
sysibmadm.lockwaits
获取锁升级、死锁和锁超时

select substr(ai.appl_name,1,10) as application ,
substr(ai.primary_auth_id,1,10) as authid,
int(ap.locks_held) as locks_held,
int(ap.lock_escals) as lock_escals,
int(ap.lock_timeouts) as lock_timeouts, 
int(ap.deadlocks) as deadlocks,
int(ap.int_deadlock_rollbacks) as int_deadlock_rollbacks,
substr(inbound_comm_address,1,15) as address 
from sysibmadm.snapappl ap,sysibmadm.snapappl_info ai 
where ap.agent_id = ai.agent_id;

监控锁等待时间（定位是否存在严重锁等待）

select substr(ai.appl_name,1,20) as appl_name ,
substr(ai.primary_auth_id,1,10) as auth_id,
ap.lock_waits,ap.lock_wait_time/1000 as total_waits,
(ap.lock_wait_time/ap.lock_waits) as avg_waits 
from sysibmadm.snapappl_info ai,sysibmadm.snapappl ap 
where ai.agent_id = ap.agent_id and ap.lock_waits > 0;

监控LOCK CHAIN（定位谁持有锁，谁在锁等待）