Re: How to remove Deadlocks on Oracle DB 7.3.3

I have observed a fault related to distributed transactions resulting in a row level deadlock that fails to automatically recover, even after all sessions have been disconnected for 48+ hours.

The error is caused by a distributed transaction timeout at a point after the statement is prepared but prior to execution. The message is:

ORA-01591: lock held by in-doubt distributed transaction {id}

If Srimal is encountering this type of error, then a manual recovery is required. It can be performed by the following sequence (using '1.2.3' as a sample id):

1. Determine the state of the transaction.

    SELECT * FROM sys.dba_2pc_pending

        WHERE local_tran_id = '1.2.3';

2. Either force a commit or rollback, as appropriate.

    ROLLBACK FORCE '1.2.3'; Note: I tried unsuccessfully to use Instance Manager to force a rollback when I encountered this error. Executing the statement via SQL Plus resolved the problem immediately.

See the Oracle documentation for Distributed Transactions for more detailed information on recovery.

Mike

"wv" <wvollenw_at_bellsouth.net> wrote in message news:39024ED5.F6689A98_at_bellsouth.net...
> I am assuming you are talking about a row level deadlock on a table....If
> so Oracle should detect and clean the deadlock. If you can't wait for
> Oracle to cleanup the deadlock you can always kill one or both of the
> sessions that have the data locked.
>
>
> Srimal wrote:
>
> > Hi,
> >
> > I am running a Database on Solaris Server.
> > Deadlock has occured to a one Row. I want that to recover immediately
> > without shutdown the Database and Restart it.
> > Can I do that and help me on that.
> >
> > Thanks in advance.
> >
> > Srimal
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
Received on Sun Apr 23 2000 - 00:00:00 CDT