1.模拟锁等锁超时情况
1>[test@demo SQLOGDIR]$ db2 get db cfg for testdb|grep -i lock
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(4096)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(60)
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Lock timeout (sec) (LOCKTIMEOUT) = -1
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Block non logged operations (BLOCKNONLOGGED) = NO
Lock timeout events (MON_LOCKTIMEOUT) = NONE
Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
Lock wait events (MON_LOCKWAIT) = NONE
Lock wait event threshold (MON_LW_THRESH) = 5000000
Lock event notification level (MON_LCK_MSG_LVL) = 1
更新锁超时的时间:
db2 update db cfg for testdb using locktimeout 10
操作测试的表:
[test@demo ~]$ db2 "select * from tb1"
ID NAME
----------- ----------
1 yo
2 yo2
3 yo3
6 yo6
4 record(s) selected.
2>第一个会话窗口执行:
db2 +c "update tb1 set name='yo11' where id=1"
另打开一个会话窗口查看锁信息:
test@demo ~]$ db2pd -db testdb -locks ----->下面可见拿到了id=1的X行锁和tb1的IX表锁
Database Partition 0 -- Database TESTDB -- Active -- Up 0 days 00:53:32
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x9C0A2F00 10 02000400040000000000000052 Row ..X G 10 1 0 0x00000000 0x40000000 0
0x9C0A3480 10 4141414141504161B72AF0B441 Internal P ..S G 10 1 0 0x00000000 0x40000000 0
0x9C0A3380 10 02000400000000000000000054 Table .IX G 10 1 0 0x00002000 0x40000000 0
3>第二个会话窗口执行:
db2 +c "update tb1 set name='yo22' where id=2"
另一个窗口查看锁等信息:
db2pd -db testdb -locks showlocks wait -tra -app -dyn
-locks表示显示锁相关信息;showlocks显示锁的详细信息如行锁所在的表ID,表空间ID
和数据页slot;wait只显示锁等状态的信息而忽略其他锁;-tra=transactions显示事务
信息;-app=applications显示应用相关信息;-dyn=dynamic sql显示动态SQL相关信息
[test@demo ~]$ db2pd -db testdb -locks wait
Database Partition 0 -- Database TESTDB -- Active -- Up 0 days 00:37:57
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x9C0A1700 2 02000400040000000000