Re: Tx - row lock contention after implementing transaction management in application server
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: exriscer@xxxxxxxxx
- Date: Wed, 27 May 2009 08:16:46 -0500
Did they implement an 'entity bean' also, what mode are the locks kept in
(mode 4 or mode 6)?
On Wed, May 27, 2009 at 7:35 AM, LS Cheng <exriscer@xxxxxxxxx> wrote:
> Yup, that was the situation
>
>
> Regards
>
> --
> LSC
>
>
>
> On Wed, May 27, 2009 at 11:20 AM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>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@xxxxxxxxx]
>> Sent: Wednesday, May 27, 2009 4:38
>> To: Bobak, Mark
>> Cc: dd.yakkali@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
>> 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@xxxxxxxxxxxx
>> <mailto:Mark.Bobak@xxxxxxxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
>> [mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:
>> oracle-l-bounce@xxxxxxxxxxxxx>] On Behalf Of LS Cheng
>> Sent: Tuesday, May 26, 2009 3:19 PM
>>
>> To: dd.yakkali@xxxxxxxxx<mailto:dd.yakkali@xxxxxxxxx>
>> Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
>> 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@xxxxxxxxx<mailto:
>> dd.yakkali@xxxxxxxxx>> 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.'
Other related posts:
