Re: help on distributed locks?

I wonder if you can do this to get more insight. Run catblock.sql from rdbms/admin. Look at dba_lock_internal. Look for the lock type DX (Distributed TX) and DR (Distributed Recovery). I think dba_locks view shows the same thing as v$lock, but dba_lock_internal gives you more. Let us know what you see.

Yong Huang
yhuang_at_indigopool.com

gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote in message news:1ce18402.9d96ed8e_at_usw-ex0101-006.remarq.com...
> 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
>
>
>
>
> -----------------------------------------------------------
>
> 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