Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

测试案例,找出引起锁等待的语句-db2pd

Posted on 2010-02-09 14:14 Prayer 阅读(391) 评论(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)
 

只有注册用户登录后才能发表评论。
【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理