RE: TX locks

From: Adams, Matthew (GE Consumer & Industrial) <MATT.ADAMS_at_GE.COM>
Date: Thu, 27 May 2004 14:45:24 -0400
Message-ID: <9E0E38DB4ACFAA4593AD6C4A45C9D5F0128FAD@LOUMLVEM01.e2k.ad.ge.com>

The blocker has the TX enqueue with lmode=3D6, the requestor=20 is requesting a shared lock (lmode=3D4). =20

I've narrowed the issue down to two the same two possibilities you mentioned:

The blocked transaction is attempting to insert the same PK/UK values and is waiting to see if the first one commits or rolls back.
Lack of an available ITL slot in either the table or index. =20 Since maxtrans is set to 255, I'm leaning away from this, although I am aware of the fact that if there is not enough space in the block=20 to increase the ITL from the default of one, it will wait rather than expand the ITL, eventually timing out with this 2049 error. I think it = would be happening a little less frequently if this were the problem.

Is there any definative method for determine which=20 scenerio is the problem?

Turning on a 10046 trace for the code will be difficult since the = purchased
app is using an app server (4 tiered architecture !!). I'd have to turn it on for everything as sessions connected to the database and=20 the trace files would eat up all available space on the server. I don't have access to the source code.

Examining the statements that the sessions in question have in the shared pool shows that they vendor IS=20 using bind variables for just
about everything, which is not a bad thing, but it prevents me from = seeing=20
what values each session is attempting to insert into the tables.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Diego Cutrone Sent: Thursday, May 27, 2004 4:55 PM
To: oracle-l_at_freelists.org
Subject: Re: TX locks

Matthew,

> Why would two transactions need the same TX enqueue? Is it because
> they are attempting to update the same row locally (which I have =
been=3D20
> unable to prove or disprove yet)? Is it because they are=3D20
> both going after the same rows remotely? Is it a lack of available =
=3D
> slots
> in the rollback segments (ie, not enough rollback segments)?

What kind of TX enqueue waits are you experiencing? TX-6 or TX-4? TX-6 enqueue waits would indicate that a transaction is trying to lock a = row
in X mode when that row is currently
being locked by another transaction in an incompatible mode. (most = surely
another TX-6 lock) . This is an application issue more than a database issue.

TX-4 is a complete different matter. I faced TX-4 problems twice. The = first
one was related to the lack of free ITL slots in a table (or index), I don't remember. And the second time the TX4 = wait
was caused when a session was trying to insert a key value (part of an unique index)
in a table after another session had already inserted the same value but = had
not yet commited. The second session waited in a TX 4 enqueue wait until the first one commited/rollback its transaction.

HTH.
Regards
Diego.

Original Message -----=20 From: "Adams, Matthew (GE Consumer & Industrial)" <MATT.ADAMS_at_GE.COM> To: <oracle-l_at_freelists.org> Sent: Thursday, May 27, 2004 7:11 AM Subject: TX locks

> I'm having a WHOLE lot of fun trying to track down
> the source of some ORA-2049 (timeout: distributed
> transaction waiting for lock) in a purchased app=3D20
> called Matrix. I have a number of questions I'm hoping=3D20
> someone can answer.
>
> Now, according to Metalink, this occurs when a session is=3D20
> waiting on a TX enqueue that another session is holding AND
> the waiting session is performing a distributed operation
> via a DB link.
>
> Also, according to Metalink (in a different document), TX
> enqueues are taken on particular slots in particular rollback
> segments.
>
>
> If a new connection does, as it's first statment, a read across
> a DB link, is a TX enqueue aquired immediately on a local =
rollback=3D20
> segment (as I think it is?)
>
> Why would two transactions need the same TX enqueue? Is it because
> they are attempting to update the same row locally (which I have =
been=3D20
> unable to prove or disprove yet)? Is it because they are=3D20
> both going after the same rows remotely? Is it a lack of available =
=3D
> slots
> in the rollback segments (ie, not enough rollback segments)?
>
> None of these scenerios seem very likely in this case, but I'm=3D20
> grasping at straws here.
>
>
> ----
> Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
> Just once, I wish we would encounter an
> alien menace that wasn't immune to bullets.=3D20
> - The Brigadier
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu May 27 2004 - 13:42:35 CDT