Re: help on distributed locks?

gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:
>We've been running our database application (Oracle 8.1.5 on NT)
>just fine for a couple weeks now.
>
>Suprisingly, we noticed the application come to a halt over the
>weekend and remain in this idle state ever since (3 days so
 far).
>
>The application is calling pl/sql stored procedures that move
>data from one oracle instance to another oracle instance via a
>dblink.
>
>In the pl/sql code of each procedure, I catch exceptions that
>any of the procedures throw and write them out to an error log
>table. I found this error in the log:
>
>----------------------------------------------------------
>ORA-02049 timeout: distributed transaction waiting for lock
>
>Cause: Exceeded INIT.ORA DISTRIBUTED_LOCK_TIMEOUT seconds
>waiting for lock.
>
>Action: Treat as a deadlock.
>
>----------------------------------------------------------
>
>Huh? Come again? What?
>
>All the procedures have explicit commits in them at very
>frequent intervals. There are no errors about failure to extend
>rollback segments etc... I'm at a loss here as to what is being
>locked.
>
>Additionally, Oracle's documentation says to treat this as a
>deadlock, however internally oracle does not do the same as my
>udump folder is empty (on both instances).
>
>I'm pretty sure that bouncing the databases will alleviate the
>situation(This is a dev environment) but I don't want to do that
>until I figure out what happened here.
>
>First of all, I don't know what exactly is locked. I checked
>the v$locked_object table on both instances and it was
>completely empty. However the V$lock table did show some
>exclusive and row locks being held by certain sessions. (I
>don't know what objects those sessions are locking )
>
>Additionally, when I try to update the code of one of my stored
>procedures (via "create or replace procedure...") I keep
>getting the following error:
>
>ORA-04021:timeout occurred while waiting to lock object
>
>So I know that my procedure is locked (I assume this puppy is
>running...but is in some kind of wait state...probably waiting
>on some other locked resource). I suspect other things are
>locked as well since the first thing each procedure does is
>insert a row into a log_table that contains the sysdate
>and "starting"...This table has no new records for the past
>three days (when the problem started happening)...However our
>application calls the procedures and it shows them as
>running...but they all seem to be in some strange wait state as
>they have been running for 3 days now whereas prior to 3 days
>ago they would usually complete in under 10 seconds.
>
>The log table itself is not locked as I can insert and update it
>fine.
>
>Does anyone have a clue what might be happening? Any way to
>find out the object that is locked and perhaps the statement
>that was issued that caused the lock?
>
>Or is there something else going on here? Specifically in
>regards to distributed transactions. At the least, I'm hoping
>someone can at least point me in the right direction?
>
>Thanks in advance,
>Gavin
>

Gavin, my best guess is that you have a runaway process holding a resouce. It could also have to do with multiple processes from both systems all wanting the same objects but going after them in different orders. In either case you need to find the lock holders to figure it out.

Try re-submitting your procedure that times out and exaiming the locks held and requested in v$lock for the session doing the compile. Then search id1 of v$lock for the requested but not held lock. This should show you who has the object and you can go from there. If this holding process has all its locks then you can look to see if it both the front-end and backgroud processes exist. If the foreground is missing you have found a runaway and can free up the lock by killing the background at the OS level. If it is waiting for one of its locks you will need to see which process has it and repeat this process. Because distributed objects are involved you will want to look at both v$lock tables.

If the number of processes is small you might just want to start by looking for runaways by validating that all listed sessions have both a front-end and back-end process.

Good luck.

• Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice. --

  ---

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Tue Aug 01 2000 - 00:00:00 CDT