DB2并发锁的问题诊断

wstlwl

最近有一项目经常出现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