DB2 v10.5在事务繁忙时段,有大量数据库锁?

背景与环境:

  1. DB2 v10.5 ,未安装DB2 PureScale特性;两台aix S814小机做了HA双机集群;带曙光存储;
  2. 每个整点或半点后十分钟,开始有集中量的数据要由应用程序写入数据库,应用程序处理很慢,平均每300毫秒处理一条记录,查看快照信息发现此时有几百个数据库锁;据目前观察,最高的时候能有700多个锁。
  3. 随后把上层的查询应用从本库中切走后,数据处理速度过了10多天后,数据写入记录比较快了。

现场信息:

        Database Lock Snapshot

Database name = ZBBB
Database path = /db2data/db2inst1/NODE0000/SQL00001/MEMBER0000/
Input database alias = ZBBB
Locks held = 295
Applications currently connected = 161
Agents currently waiting on locks = 0
Snapshot timestamp = 05/05/2018 11:06:08.916275

Application handle = 10641
Application ID = 172.20.2.2.65000.180824194601
Sequence number = 00001
Application name = db2jcc_application
CONNECT Authorization ID = USERDBS
Application status = UOW Executing
Status change time = Not Collected
Application code page = 1208
Locks held = 292
Total wait time (ms) = Not Collected

List Of Locks
Lock Name = 0x0000000300000001000130A0D6
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Variation Lock
Mode = S

Lock Name = 0x000500060000000072CB000652
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 1925906438
Object Type = Row
Tablespace Name = MHAPPLOB
Table Schema = MHAPP
Table Name = RPT01_CAC
Mode = X

Lock Name = 0x000500060000000072CB000552
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 1925906437
Object Type = Row
Tablespace Name = MHAPPLOB
Table Schema = MHAPP
Table Name = RPT01_CAC
Mode = X

Lock Name = 0x00040005000000004601000452
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 1174470660
Object Type = Row
Tablespace Name = MHAPPGEN
Table Schema = MHAPP
Table Name = ELE01_SASP
Mode = X

Lock Name = 0x000500060000000072CB000452
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 1925906436
Object Type = Row
Tablespace Name = MHAPPLOB
Table Schema = MHAPP
Table Name = RPT01_CAC
Mode = X

Lock Name = 0x00040005000000004601000352
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 1174470659
Object Type = Row
Tablespace Name = MHAPPGEN
Table Schema = MHAPP
Table Name = ELE01_SASP
Mode = X

Lock Name = 0x000500060000000072CB000352
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 1925906435
Object Type = Row
Tablespace Name = MHAPPLOB
Table Schema = MHAPP
Table Name = RPT01_CAC
Mode = X

Lock Name = 0x00040005000000004601000252
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 1174470658
Object Type = Row
Tablespace Name = MHAPPGEN
Table Schema = MHAPP
Table Name = ELE01_SASP
Mode = X

Lock Name = 0x000500060000000072CB000252
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 1925906434
Object Type = Row
Tablespace Name = MHAPPLOB
Table Schema = MHAPP
Table Name = RPT01_CAC
Mode = X
......后面还有很多

db2diag.log中无报错信息;插入事务的程序工程师反映其在插入数据库后,确实做过了主动commit。

问题:
现阶段,我不知道该继续收集那些信息,如何判断瓶颈在何处。
请老师多多指教

参与19

4同行回答

DB-TrendSetterDB-TrendSetter联盟成员数据库架构师公司
两种监控锁等待的方法,大量的锁,需要结合监控结果去分析,是哪些SQL hold了锁:1 db2 event monitor for locking2 db2pd -d dbname -wlocks显示全部

两种监控锁等待的方法,大量的锁,需要结合监控结果去分析,是哪些SQL hold了锁:
1 db2 event monitor for locking
2 db2pd -d dbname -wlocks

收起
电信运营商 · 2018-05-25
浏览2083

提问者

lightening
系统运维工程师AMC

问题来自

相关问题

相关资料

相关文章

问题状态

  • 发布时间:2018-05-25
  • 关注会员:4 人
  • 问题浏览:5823
  • 最近回答:2018-07-29
  • X社区推广