Re: Tx - row lock contention after implementing transaction management in application server

On Wed, May 27, 2009 at 7:35 AM, LS Cheng <exriscer_at_gmail.com> wrote:

> Yup, that was the situation
>
>
> Regards
>
> --
> LSC
>
>
>
> On Wed, May 27, 2009 at 11:20 AM, Bobak, Mark <Mark.Bobak_at_proquest.com>wrote:
>
>> Right, it's possible for FKs to cause TX row lock contention, but, if they
>> do, it has nothing to do with whether the FK column(s) is(are) indexed in
>> the child table.
>>
>> For example, if you do:
>> create table parent(p_id number primary key);
>> create table child(c_id number primary key, p_id number references
>> parent(p_id));
>>
>> Now, child(p_id) is not indexed.
>>
>> If you do:
>> insert into parent values(1);
>> into a session, and DO NOT COMMIT
>>
>> and now in a second session, do:
>> insert into child values(1,1);
>>
>> the second session will wait on the first, with 'TX - row lock
>> contention'.
>>
>> The problem is that the value 1 in the parent is in an indetermined state.
>> So, the second session will wait until the first either commits or rolls
>> back. If it commits, the second session will successfully insert a row. If
>> the first session rolls back, the second will encounter ORA-2291 'integrity
>> constraint violated - parent key not found'.
>>
>> Note that this behaviour has nothing to do with whether the FK is indexed
>> in the child table.
>>
>> Hope that helps clarify my point,
>>
>> -Mark
>>
>>
>>
>> ________________________________________
>> From: LS Cheng [exriscer_at_gmail.com]
>> Sent: Wednesday, May 27, 2009 4:38
>> To: Bobak, Mark
>> Cc: dd.yakkali_at_gmail.com; oracle-l_at_freelists.org
>> Subject: Re: Tx - row lock contention after implementing transaction
>> management in application server
>>
>> I thought that too until last week when a customer was loading data
>> (parent and child tables) and hit tx contention and when we disabled the FK
>> the tx contention went away
>>
>>
>>
>> --
>> LSC
>>
>> On Tue, May 26, 2009 at 9:35 PM, Bobak, Mark <Mark.Bobak_at_proquest.com
>> <mailto:Mark.Bobak_at_proquest.com>> wrote:
>>
>> I don’t think that’s his problem. If it were unindexed FKs, the waits
>> would be on TM enqueues, not TX enqueues.
>>
>>
>>
>> -Mark
>>
>>
>>
>> From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>
>> [mailto:oracle-l-bounce_at_freelists.org<mailto:
>> oracle-l-bounce_at_freelists.org>] On Behalf Of LS Cheng
>> Sent: Tuesday, May 26, 2009 3:19 PM
>>
>> To: dd.yakkali_at_gmail.com<mailto:dd.yakkali_at_gmail.com>
>> Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
>> Subject: Re: Tx - row lock contention after implementing transaction
>> management in application server
>>
>>
>>
>> Doesnt that sound like your FKs are not indexed?
>>
>>
>>
>> --
>> LSC
>>
>>
>> On Tue, May 26, 2009 at 8:41 PM, dd yakkali <dd.yakkali_at_gmail.com<mailto:
>> dd.yakkali_at_gmail.com>> wrote:
>>
>> Hello everyone,
>>
>>
>>
>> After our application folks implemented transaction management in the app,
>> I am seeing a bunch of seesions waiting with "Tx - row lock contention" on
>> an insert statement. we found that the parent table insert is not commited
>> and hence the child record insert is hanging as both these statements are
>> using different oracle sessions for some reason. This continues for
>> eternity, until the app server is killled and restarted.
>>
>>
>>
>>
>>
>> Sun Java Enterprise Server, hibernate, oracle 10.2.0.4 RAC.
>>
>>
>>
>>
>>
>> Now here is the question: Our java app server folks are asking me to give
>> them bind variable values of the statement that is hanging. We have a
>> connection pool which is 132 connections size. Is there any way to get the
>> bind variable values after the fact, i.e while it is waiting for the parent
>> to commit?
>>
>>
>>
>>
>>
>>
>>
>> Thanks
>>
>> Deen
>>
>>
>>
>>
>>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l