在数据库中使用sqc编程(动态及静态)时,当游标声明中的sql使用with ur方式时,数据库内部执行过程中发现依旧会使用NS锁,易导致数据库内部锁超时及死锁现象的发生。
问题现象:case1:游标定义如下:EXEC SQL DECLARE DynPara2Cur_cur CURSOR FOR SELECT * FROM UM_TXNDB.TBL_UMTXN_DYN_PARA WITH UR;
当一个事务对表格发起update命令,并且不提交,此时执行包含上述游标语句的程序,程序会处于锁等待状态,现象如下图:
快照信息如下:
Application handle = 1175
Application status = Lock-wait
Status change time =
Application code page = 1386
Application country/region code = 1
DUOW correlation token = *LOCAL.abc.100716043348
Application name = curtest
Application ID = *LOCAL.abc.100716043348
Sequence number = 0001
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =
ID of agent holding lock = 8
Application ID holding lock = *LOCAL.abc.100715120255
Lock name = 0x000200020000000D0000000052
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 = UM_TXNDB
Name of table holding lock = TBL_UMTXN_DYN_PARA
Lock wait start timestamp = 07/16/2010 12:33:48.744331
case2:游标定义如下:EXEC SQL DECLARE DynPara2Cur_cur CURSOR FOR SELECT * FROM UM_TXNDB.TBL_UMTXN_DYN_PARA FOR READ ONLY WITH UR;
当cursor中带有FOR READ ONLY时 ,运行时将不加锁
(静态sql和动态sql问题一致,但命令行上无论游标如何定义,均不会加锁)
问题分析:目前IBM对此还没有最终答复,从IBM官方文档上发现在对数据库查询时,没有更新或删除计划的情况下,建议带上FOR READ ONLY,以上测试现象可能就是这条建议的初衷?原文如下:
For more efficient processing of data, the database manager can block data for read-only cursors when retrieving data from a remote server. The use of the FOR UPDATE clause helps the database manager decide whether a cursor is updatable or not. Updatability is also used to determine the access path selection as well. If a cursor is not going to be used in a Positioned UPDATE or DELETE statement, it should be declared as FOR READ ONLY.
问题影响及解决方法:影响:在游标不带for read only选项的前提下,仅以脏读with ur方式对数据库进行读取操作依旧会加锁(NS),当被访问对象上已有insert,update,delete等操作时,可能引发锁超时或死锁,给系统的并发性带来影响。
解决方法:在只读游标后带上FOR READ ONLY选项。
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞0
添加新评论0 条评论