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:
AddressAppHandlTranHdl Locks State Tflag Tflag2
0x0514188030 2 9 WRITE 0x00000000 0x00000
0x0514488034 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
AddressAppHandlAgentTid Priority Type State
0x04449BC034 3392 0 Coord Inst-Active
0x0444924030 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 NumAgents CoorTid Status
0x04AF808034 1 3940 Lock-wait
0x0384196030 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
页:
[1]