Posted on 2009-08-07 00:26
Prayer 阅读(456)
评论(0) 编辑 收藏 引用 所属分类:
DB2
测试环境：
db2 V9.5
参考书籍《db2数据库性能调整和优化》牛新庄著 242页
还是利用上一篇引起死锁的那个表，来测试如果找到引起锁等待的sql语句，为了模拟效果，设置locktimeout为-1
还是打开三个窗口,
窗口1：
$ db2 +c "insert into deadtable values(1)"
DB20000I The SQL command completed successfully.
窗口2：
$ db2 +c "select * from deadtable"
引起等待
窗口3：
打开snapshot监控，重置计数器
$ db2 get snapshot for database on sample|grep -i lock
Locks held currently = 7
Lock waits = 0
Time database waited on locks (ms) = 555940
Lock list memory in use (Bytes) = 5632
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 1
Lock Timeouts = 0
Internal rollbacks due to deadlock = 0
Number of MDC table blocks pending cleanup = 0
Memory Pool Type = Lock Manager Heap
第一步：
$ db2pd -db sample -locks showlocks wait
Database Partition 0 -- Database SAMPLE -- Active -- Up 9 days 15:28:01
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg
0x0770000030352600 7 00030005000000000280000652 Row .NS W 2 1 0 0x00 0x00000001 TbspaceID 3
TableID 5 PartitionID 0 Page 640 Slot 6
0x0770000030351940 2 00030005000000000280000652 Row ..X G 2 1 0 0x08 0x40000000 TbspaceID 3
TableID 5 PartitionID 0 Page 640 Slot 6
--Sts列 W 表示等待 G代表被授权获得锁
第二步：
由第一步输出中的TranHdl对应到应用的AppHandl。
$ db2pd -db sample -transactions
Database Partition 0 -- Database SAMPLE -- Active -- Up 9 days 15:33:01
Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn LogSpace
SpaceReserved TID AxRegCnt GXID
0x07700000302A1F80 3699 [000-03699] 2 3 WRITE 0x00000000 0x00000000 0x0000032CBCF4 0x0000032CBCF4 110
163 0x000000005E12 1 0
0x07700000302A2F00 3700 [000-03700] 3 0 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0
0 0x00000000525D 1 0
0x07700000302A3E80 3701 [000-03701] 4 0 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0
0 0x000000005E19 1 0
0x07700000302A4E00 3702 [000-03702] 5 0 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0
0 0x00000000525F 1 0
0x07700000302A6D00 3704 [000-03704] 7 4 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0
0 0x000000005DFF 1 0
0x07700000302A7C80 3835 [000-03835] 8 0 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0
0 0x000000005E14 1 0
得到TranHdl 2 和7 分别 对应到应用的AppHandlTranHdl 3699 （窗口1） 和3704（窗口2）
第三步：
$db2 get snapshot for application agentid 3704
其中有
ID of agent holding lock = 3699 --引起3704锁等待的agentid
Application ID holding lock = *LOCAL.db2inst1.090628013214
Lock name = 0x00030005000000000280000652
Lock attributes = 0x00000000
Release flags = 0x00000001
Lock object type = Row
Lock mode = Exclusive Lock (X)
Lock mode requested = Next Key Share (NS)
Name of tablespace holding lock = IBMDB2SAMPLEREL
Schema of table holding lock = DB2INST1
Name of table holding lock = DEADTABLE
Data Partition Id of table holding lock = 0
Lock wait start timestamp = 07/08/2009 01:49:56.209476
第四步：
定位引起锁等待的sql语句
$db2 get snapshot for application agentid 3699
其中有
Dynamic SQL statement text:
insert into deadtable values(1)