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,
,(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
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/，如需转载，请注明出处，否则将追究法律责任。