DB2查找阻塞的session

下面是一个测试：
session 1：
db2 +c --登陆数据库，这里要加上选项+c,也就是不会做隐式提交事务。否则每执行一条SQL就提交了。
db2 => create table test00(a int, b varchar(10));
DB20000I The SQL command completed successfully.
db2 => commit;
DB20000I The SQL command completed successfully.
db2 => insert into test00 values(1,'AA');
DB20000I The SQL command completed successfully.

接着session 2执行：
db2 => select * from test00;
这时，这个session是hang在那里的。这点与oracle不同的，oracle不阻塞读的，但DB2会阻塞. 接下来就要找到被那个session阻塞。

[@more@]

在另一个窗口里，用DB2的工具db2pd来查看:
LinuxHost 28: db2pd -db sample -locks showlocks wait

Database Partition 0 -- Database SAMPLE -- Active -- Up 1 days 23:20:14 -- Date 11/12/2009 01:44:19

Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg
0x292A0248 2 02000D00040000000000000052 Row .NS W 5 1 0 0x0000 0x00000001 TbspaceID 2 TableID 13 RecordID 0x4
0x2929F898 5 02000D00040000000000000052 Row ..X G 5 1 0 0x0008 0x40000000 TbspaceID 2 TableID 13 RecordID 0x4
这里可以看到有两个事务，都在对象table ID 13将加锁/已加锁，具体是哪个对像可以能过"select tabschema,tabname,tbspace from syscat.tables where tableid=13"来查到。这两个事务，都是对这个对象加得行级锁，事务2加NS(Next Key Share)锁，但状态Sts是W,表明在等待，事务5刚加了独占锁X,状态是G,表明加锁成功。通过这里基本上就可以找到哪个事务阻塞了其它的事务。通过事务ID，可以找到相应的AppHandle:

LinuxHost 29: db2pd -db sample -transactions 2

Database Partition 0 -- Database SAMPLE -- Active -- Up 1 days 23:23:41 -- Date 11/12/2009 01:47:46

Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn LogSpace SpaceReserved TID AxRegCnt GXID
0x29229580 1326 [000-01326] 2 5 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0 0 0x000000001A9F 1 0
--事务2，对应的AppHandle是1326
LinuxHost 30: db2pd -db sample -transactions 5

Database Partition 0 -- Database SAMPLE -- Active -- Up 1 days 23:24:09 -- Date 11/12/2009 01:48:14

Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn LogSpace SpaceReserved TID AxRegCnt GXID
0x2922B500 551 [000-00551] 5 3 WRITE 0x00000000 0x00000000 0x00000426E4B9 0x00000426E4B9 108 166 0x000000001AA1 1 0

找到AppHandle/session,就可以分析，相关的事务具体在执行什么：

db2 => get snapshot for application agentid 1326
Application handle = 1326
Application status = Lock-wait
...
Agents waiting on locks = 1
Maximum associated agents = 1
...
Blocking cursor = YES
Dynamic SQL statement text:
select * from test00 --当前正在执行的SQL。
....
ID of agent holding lock = 551 --正在被这个session堵塞了。
Application ID holding lock = *LOCAL.inst.091112064201
Lock name = 0x02000D00040000000000000052
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 = USERSPACE1
Schema of table holding lock = testuser
Name of table holding lock = TEST00
Lock wait start timestamp = 11/12/2009 01:43:42.483704

db2 => get snapshot for application agentid 551;
...
Agents waiting on locks = 0
Maximum associated agents = 1
...
Blocking cursor = NO
Dynamic SQL statement text:
insert into test00 values(1,'AA')--这里的SQL是当前在执行，或刚执行的SQL。有时可能显示的SQL跟db2pd显示的锁的对象没关系.
...

从这里，就完全可以确定是session 551阻塞session 1326.

如果能够通过db2pd把阻塞进程，被阻塞进程的关系非常明显的显示出来就好。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45188/viewspace-1028677/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45188/viewspace-1028677/