db2的锁操作命令及提示

1,获取锁方法：
1.1管理视图:
SELECT * FROM SYSIBMADM.SNAPLOCK with ur;
select * from SYSIBMADM.LOCKS_HELD with ur;  --拿到上述的agent_id，查询具体锁表的IP和应用程序

db2 list application | grep 58189

         --MASADIC db2jcc_applica 58189 192.168.210.33.59145.1405052017 NGDB 1

db2 "force application(58189)"   --杀掉此进程 

 

1.2 表函数:
SELECT * FROM TABLE(SNAP_GET_LOCK('SAMPLE',-1)) AS SNAPLOCK;
SELECT * FROM TABLE(SNAPSHOT_LOCK('SAMPLE',-1)) AS SNAPLOCK;

1.3 CLP:
get snapshot for locks for application applid appl-id
get snapshot for locks for application agentid appl-handle
get snapshot for locks on dbname

1.4 db2bp工具：
db2pd -inst db2 -db sample -locks

 

2.锁机制总结

场景1：（形成了死锁情况，一直等）

lock table test in share mode；--终端1操作

db2 -td@ -vf db2pl.sql   --终端2操作 （最终会被锁住）

lock table test in share mode； 终端3操作 （最终会一直等锁）