Lock + Insert..
425831Aug 23 2006 — edited Aug 27 2006Hi,
I have a job in my Oracle Forms 10g/ Oracle DB 10g application that runs every minute to identify if there is a Blocking Lock in the database.
I have been getting an INSERT statement as the cause of the blocking lock every day! How can an insert statement create a blocking?
I have not used LOCK TABLE tab1 IN EXCLUSIVE MODE;
The query I use in my job to get the Blocking SQL text is:
SELECT vs.SID, vs.username, vs.logon_time, vs.machine, vs.sql_hash_value,
vsql.piece, vsql.sql_text, vs.prev_hash_value
FROM v$session vs, v$sqltext vsql
WHERE SID IN (SELECT LPAD (' ', DECODE (vl.request, 0, 0, 1)) || SID
FROM v$lock vl
WHERE id1 IN (SELECT id1
FROM v$lock vl2
WHERE vl2.lmode = 0))
AND vs.sql_hash_value = vsql.hash_value
ORDER BY 2, 5, 6;
Please advice
Warm Regards
Sajan