ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle blocking issue with lock table in exclusive mode

Oracle blocking issue with lock table in exclusive mode

原创 Linux操作系统 作者:chncaesar 时间:2013-09-17 04:06:18 0 删除 编辑
A PL/SQL program previously worked in serial, but it hung when running in parallel.

select ses.SID, substr(SQ.SQL_TEXT,0),sq.LAST_LOAD_TIME,
ses.schemaname,
SES.LOCKWAIT, 
SES.BLOCKING_SESSION_STATUS,
BLOCKING_SESSION,SES.STATE, 
SES.EVENT, 
SES.EVENT#,
SES.SECONDS_IN_WAIT
,(select object_name from dba_objects where object_id=ses.ROW_WAIT_OBJ#) object_name
from V$SESSION SES inner join V$SQL SQ on
SES.SQL_ID=SQ.SQL_ID
order by sq.LAST_LOAD_TIME desc

It turned out every session was contending for the same object. Furthermore, the the following query result showed they were requesting exclusive lock but none succeeded.
Request/Lmode = 6 --> exclusive lock
Block = 1 --> Blocker
Block = 0 --> Blockee

SELECT sid, type, id1, id2, lmode, request,block FROM V$LOCK WHERE request > 0 order by sid;


I looked into procedure that upserts that specific objects, and found that before insert it places an exclusive lock explicitly by:

Lock table xxx in exclusive mode.

Yes, that's the reason why the program hung.

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

请登录后发表评论 登录
全部评论

注册时间:2013-07-30

  • 博文量
    102
  • 访问量
    899699