使用游标的要小心了,游标在OPEN时会加锁:
实例:
表t1,数据如下:
ID MEMO
- -- ----
1 a1
2 b1
3 hhh
4 d1
5 e1
DB2默认锁定超时-1(不限制),方便测试修改为20(秒)
db2 update db cfg for sample using LOCKTIMEOUT 20
在第一个DB2 CLP窗口执行更新
连接到数据库:db2 connect to sample
执行更新:db2 +c "update t1 set memo = 'hhh1' where id =3"
查看锁:
C:\>db2pd -db sample -locks
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:40:56
Locks:
Address TranHdl Lockname Type Mode Sts Owner
Dur HoldCount Att ReleaseFlg rrIID
0x7FC70480 2 53514C43324832307F4760B841 Internal P ..S G 2
1 0 0x00000000 0x40000000 0
0x7FC70700 2 02001300060000000000000052 Row ..X G 2
1 0 0x00000000 0x40000000 0
0x7FC70400 2 02001300000000000000000054 Table .IX G 2
1 0 0x00002000 0x40000000 0
表加意向互斥锁,更新的行(id = 3)加互斥锁
在第二个DB2 CLP窗口定义游标并打开:
连接到数据库:db2 connect to sample
定义游标:db2 "declare c1 cursor for select * from t1 for read only" (v8)
db2 "declare c1 cursor for select * from t1 with rs"(v9.7)
注:v8的游标定义,在版本8上open时锁等待,在版本9上不会出现锁等待,本实例主要说v9.7
db2 "declare c1 cursor for select * from t1 with rs"
db2 +c "open c1"
光标停止不动,在另外的DB2 CLP窗口查看锁及锁等待,信息如下:
锁:
C:\>db2pd -db sample -locks
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:43:34
Locks:
Address TranHdl Lockname Type Mode Sts Owner
Dur HoldCount Att ReleaseFlg rrI