RE: TX locks
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 27 May 2004 11:34:49 -0400
Matt (I am sure your know most of this but for the record), the error is
telling you that the distributed transaction was attempting to update a row
that has been updated by another session that has not committed its work.
The other transaction may have been local or it may have also been a
distributed transaction.
If both transactions were local the second (your failing transaction) would
just go into a lock waiting state. Because the transaction is distributed
Oracle terminates the transaction after if waits the allowed amount of
time.
If the error happens again and is repeatable then if you can determine the
table and row being accessed by talking to the user who got the error and
then tracing their session (if possible) or searching through the code you
can log onto the remote instance and issue the hanging update/delete
statement. The session should then be lock-waited so you can use the normal
process of v$session to v$lock to v$transaction to find the holding session.
Then you can try to find out what the holding session was doing and why it
held the lock so long.
There are a couple of articles on the cooperative FAQ that Jonathan Lewis
hosts on his site on finding distributed sessions that may be of help:
Why does it seem that a select over a db link requires a commit after
execution?
http://www.jlcomp.demon.co.uk/faq/dblink_commit.html
How do I find distributed queries / transactions (either issued from or
connecting to this instance)?
http://www.jlcomp.demon.co.uk/faq/find_dist.html
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Adams, Matthew (GE
Consumer & Industrial)
Sent: Thursday, May 27, 2004 10:11 AM
To: oracle-l@xxxxxxxxxxxxx
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=20
called Matrix. I have a number of questions I'm hoping=20
someone can answer.
Now, according to Metalink, this occurs when a session is=20
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=20
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=20
unable to prove or disprove yet)? Is it because they are=20
both going after the same rows remotely? Is it a lack of available =
slots
in the rollback segments (ie, not enough rollback segments)?
None of these scenerios seem very likely in this case, but I'm=20
grasping at straws here.
----
Matt Adams - GE Appliances - matt.adams@xxxxxxxxxxx
Just once, I wish we would encounter an
alien menace that wasn't immune to bullets.=20
- The Brigadier
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
