Re: Tx - row lock contention after implementing transaction management in application server
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- Date: Wed, 27 May 2009 14:35:58 +0200
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
>
>
>
>
>
Other related posts:
