| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: TX locks
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Adams, Matthew (GE
Consumer & Industrial)
Sent: Thursday, May 27, 2004 10:11 AM
To: oracle-l_at_freelists.org
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.
 |
 |