Process Lock Issue while running Lot Allocation Batch

[Veeraragavan]

Hi

We are running the Lot allocation batches for ages. But recently we have included a branch plant in its data selection. And unfortunately report is failing for some reasons. When we tried to debug the report, understood the report got stuck at a particular select statement. When looking deep into the log it seems the batch is stuck due to a a process lock. Because the select statement works fine when ran independently.

I use following query to know the process lock details when the batch is in stuck status.


--------------------------------------------------------
Select lk.SID, se.username, se.OSUser, se.Machine,
DECODE(lk.TYPE, 'TX','Transaction', 'TM','DML',
'UL','PL/SQL User Lock', lk.TYPE) as lock_type,
DECODE(lk.lmode, 0,'None', 1,'Null', 2,'Row-S (SS)',
3,'Row-X (SX)', 4,'Share', 5,'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(lk.lmode)) as mode_held,
DECODE(lk.request, 0,'None', 1,'Null', 2,'Row-S (SS)',
3,'Row-X (SX)', 4,'Share', 5,'S/Row-X (SSX)',
6,'Exclusive', TO_CHAR(lk.request)) as mode_requested,
TO_CHAR(lk.id1) as lock_id1,
TO_CHAR(lk.id2) as lock_id2,
'' as owner,
'' as object_type,
'' as object_name,
lk.block,
se.lockwait
FROM v$lock lk, v$session se
WHERE (lk.type = 'TX') AND (lk.SID = se.SID)
union
Select lk.SID, se.username, se.OSUser, se.Machine,
DECODE (lk.TYPE, 'TX','Transaction', 'TM','DML',
'UL','PL/SQL User Lock', lk.TYPE) as lock_type,
DECODE (lk.lmode, 0, 'None', 1,'Null', 2,'Row-S (SS)',
3,'Row-X (SX)', 4,'Share', 5, 'S/Row-X (SSX)',
6,'Exclusive', TO_CHAR (lk.lmode)) as mode_held,
DECODE (lk.request, 0,'None', 1,'Null', 2,'Row-S (SS)',
3,'Row-X (SX)', 4,'Share', 5,'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR (lk.request)) as mode_requested,
TO_CHAR (lk.id1) as lock_id1,
TO_CHAR (lk.id2) as lock_id2,
ob.owner,
ob.object_type,
ob.object_name,
lk.Block,
se.lockwait
FROM v$lock lk, dba_objects ob, v$session se
WHERE lk.TYPE IN ('TM','UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id
--------------------------------------------------------
Attached sheet contains the lock details return by the above query.

I am trying to understand the details. Would be more helpful if someone can help me understand the query and throw some light on the issue that i am facing. Thanks in advance

Regards,
Veera