Oracle blocking issue with lock table in exclusive mode

chncaesar發表於2013-09-17
OracleBloC
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 --&gt exclusive lock
Block = 1 --&gt Blocker
Block = 0 --&gt 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章