DB2并发锁的问题诊断

最近有一项目经常出现DB2表锁情况，从存储过程，到应用程序，如果你靠去查代码还真是费劲，也记得以前有人问过我如何看DB2表的锁，这次都在这里一一讲清楚了，讲归讲，大家记得自己多去试试，实战是最佳的学习方法，我一直比较崇尚“从问题中学习结”。

―――Dream

第一步，打开Snapshot监控

 db2 update monitor switches using lock on statement on uow on

第二步，检查锁等待情况

 db2pd -db sample -locks wait showlocks

Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:33:05

Locks:

AddressTranHdlLockname Type Mode Sts Owner Dur

0x050A0240 6 02000600050040010000000052 Row ..X W 2 1

0x050A0DB0 2 02000600050040010000000052 Row ..X G 2 1

HoldCount Att ReleaseFlg

0 0x00 0x40000000 TbspaceID 2 TableID 6 PartitionID 0 Page 320 Slot 5

0 0x00 0x40000000 TbspaceID 2 TableID 6 PartitionID 0 Page 320 Slot 5

--Sts列 W 表示等待 G代表被授权获得锁

第三步，找到TransID对应的ApplID

 db2pd -db sample –trans

Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:34:47

Transactions:

AddressAppHandl[nod-index]TranHdl Locks State Tflag Tflag2

0x0514188030 [000-00030]2 9 WRITE 0x00000000 0x00000

0x0514488034 [000-00034]6 5 WRITE 0x00000000 0x00000

第四步，通过获取快照，定位锁的SQL

 db2 get snapshot for application agentid 30

 db2 get snapshot for application agentid 34

 接下来也可以通过db2pd分析，不通过snapshot

第五步，获得应用程序与相关代理信息

 db2pd -agents

Database Partition 0 -- Active -- Up 3 days 08:35:42

Agents:

Current agents: 2

Idle agents: 0

Active coord agents: 2

Active agents total: 2

Pooled coord agents: 0

Pooled agents total: 0

AddressAppHandl[nod-index]AgentTid Priority Type State

0x04449BC034 [000-00034]3392 0 Coord Inst-Active

0x0444924030 [000-00030]2576 0 Coord Inst-Active

ClientPid Userid ClientNm Rowsread Rowswrtn LkTmOt DBName

3916 USER_B db2bp.ex 43 43 NotSet SAMPLE

2524 USER_A db2bp.ex 153 14 NotSet SAMPLE

第六步，获取应用程序其他信息

db2pd -db sample -applications

Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:36:14

Applications:

AddressAppHandl[nod-index] NumAgents CoorTid Status

0x04AF808034 [000-00024] 1 3940 Lock-wait

0x0384196030 [000-00020] 1 2548 UOW-Waiting

C-AnchIDC-StmtUID L-AnchID L-StmtUID Appid

195 1 0 0 *LOCAL.DB2.061122195637

0 0 60 1 *LOCAL.DB2.061122195609

第七步，检查动态语句缓存的内容

db2pd -db sample -dynamic

Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:37:39

Dynamic Cache:

Current Memory Used 187188

Total Heap Size 1271398

Cache Overflow Flag 0

Number of References 2

Number of Statement Inserts 3

Number of Statement Deletes 0

Number of Variation Inserts 2

Number of Statements 3

Dynamic SQL Statements:

Address AnchID StmtUID NumEnv NumVar NumRef NumExe

0x056CEBD0 60 1 1 1 1 1

0x056CE850 180 1 0 0 0 0

0x056CFEA0195 1 1 1 1 1

Text

UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER'

SET CURRENT LOCALE LC_CTYPE = 'de_DE'

UPDATE EMPLOYEE SET SALARY = SALARY * 0.02

Dynamic SQL Environments:

Address AnchID StmtUID EnvID Iso QOpt Blk

0x056CECD0 60 1 1 CS 5 B

0x056D30A0 195 1 1 CS 5 B

Dynamic SQL Variations:

AddressAnchIDStmtUID EnvID VarID NumRef Typ

0x056CEEB0 60 1 1 1 1 4

0x056D3220195 1 1 1 1 4

Lockname

010000000100000001003C0056

01000000010000000100C30056

第八步，锁分析的重复获取

db2pd -db sample -locks wait showlocks -transactions -agents -applications –dynamic -file