Oracle Database Discussions

Hello
I've experienced a massive lock (SELECT * FROM v$lock where block > 0;)
in one of our batch process . I manage to trace the UPDATE statement that was responsible to this lock
the problem is that it was causing an "enq: TM - contention" on a child table of this parent table which has a FK constraint to this parent table .
only by adding an index on the FK constraint column on the child table the lock disappear. now the UPDATE statement didn't update the PK column of the parent table!!
can u look at this statement and tell me if this is possible.
I'm working on Linux Oracle 10.2.0

parent table - AC_ENTRY

PK column of parent table: ID

child table: AC_GL_INTERFACE

child table FK constraint column: ENTRY_ID

FK constraint : CONSTRAINT "AC_GL_INTERFACE_FK3" FOREIGN KEY ("ENTRY_ID") REFERENCES "AC_ENTRY" ("ID") ENABLE,

the UPDATE statement that was locking :

update ac_entry
set ac_entry.*entry_status* =:1
where ac_entry.transaction_id = :2
AND ac_entry.id in (select b.id
from AC_ENTRY b, AC_ACCOUNT, T_ACCOUNT_TYPE
where b.transaction_id = :3
and b.ACCOUNT_ID = AC_ACCOUNT.id
and AC_ACCOUNT.ACCOUNT_TYPE = T_ACCOUNT_TYPE.ID
and T_ACCOUNT_TYPE.PAYABLE_ACCOUNT_FLAG = :4);


the index that released the lock:
CREATE INDEX AC_GL_INTERFACE_FK3 ON AC_GL_INTERFACE (ENTRY_ID)


from ADDM report:

FINDING 1: 88% impact (134853 seconds)
--------------------------------------
Contention on table lock waits was consuming significant database time.

RECOMMENDATION 1: Application Analysis, 88% benefit (134853 seconds)
ACTION: Investigate application logic involving DDL and DML on provided
blocked objects.
RATIONALE: The TABLE "*AC_GL_INTERFACE*" with object id "182988"
was locked for a significant time.
RELEVANT OBJECT: database object with id 182988

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Application" was consuming significant database
time. (96% impact [147049 seconds])

Edited by: yaronkalatian on Nov 17, 2010 10:51 AM

Edited by: yaronkalatian on Nov 17, 2010 10:51 AM