DB version: 10.2.0.3
Could someone provide the possible reasons for this type of lock. I have an update locks atleast once or more a day with this type of lock and will not be finished until I kill off the session.
I have run query below and also several queries to diagnose and also using diag and tuning packs.
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
The output is like:
1234 is blocking 1235
But, the session 1234 and 1235 are accessing two independant tables and there are NO referential constraints defined between these two. Also, when I try to get a sql_text for 1234, there is no output displayed. However, the session 1235 is running an update for one record.
Could someone let me know the possible reasons for this. I'm unable to find a solution. The update query runs so often and it's not blocking all the updates. There are only a few updates one or two per day will be blocked by other sessions and will not be completed until I kill off the session.
Thanks in advance for your suggestions.