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

背景与环境:DB2 v10.5 ,未安装DB2 PureScale特性;两台aix S814小机做了HA双机集群;带曙光存储;每个整点或半点后十分钟,开始有集中量的数据要由应用程序写入数据库,应用程序处理很慢,平均每300毫秒处理一条记录,查看快照信息发现此时有几百个数据库锁;据目前观察,最高的时候能有700...显示全部

背景与环境:

  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

查看其它 3 个回答anikikong的回答

anikikonganikikong课题专家组数据库运维工程师中国民生银行

db2pd -d dbname -wlocks 查看锁等待的现象。应用持有大量锁没关系,只要不发生锁升级和锁等待就没事。db2的锁是放在内存里的,获取锁的开销并不大。集中大量的数据写入慢更可能是出现在其他瓶颈上。例如latch,io等。

银行 · 2018-05-25
浏览2183

回答者

anikikong
数据库运维工程师中国民生银行
擅长领域: 数据库灾备双活

anikikong 最近回答过的问题

回答状态

  • 发布时间:2018-05-25
  • 关注会员:4 人
  • 回答浏览:2183
  • X社区推广