Re: DX Lock problem

From: Mladen Gogala <mgogala.SPAM-ME_at_not-at-verizon.net>
Date: Fri, 29 Jun 2007 20:44:59 +0200 (CEST)
Message-ID: <pan.2007.06.29.18.39.51@not-at-verizon.net>

On Fri, 29 Jun 2007 07:55:28 -0700, Charles Hooper wrote:

> On Jun 29, 6:37 am, sorc..._at_gmail.com wrote:

>> On Jun 28, 6:13 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>> > On Jun 28, 10:48 am, sorc..._at_gmail.com wrote:
>>
>> > Google search:
>> >   oracle dx lock
>> > or
>> >   oracle dx lock commit rollback
>>
>> > Finds this page:
>> >  http://www.jlcomp.demon.co.uk/faq/find_dist.html
>>
>> > Charles Hooper
>> > IT Manager/Oracle DBA
>> > K&M Machine-Fabricating, Inc.
>>
>> Charles,
>>
>> that was clear to me... what is not clear is that I can't see the point
>> of acquiring an exclusive lock on an object (whatever the object is, I
>> smell it might be an undo segment) in exclusive mode and serializing
>> all other session.
>> The symptom is evident in production: once in a while we see this
>> exclusive mode lock of type DX, the database slows down and every
>> single query (select i mean) not necessarly issued from an xa
>> connection against, for example, the gv$global_transaction is there
>> forever.
>> I am pretty sure is a bug... but I need to reproduce it somehow.
>>
>> Yes, the distributed_lock_timeout is exactly 9000, as Mladen suggested
>> me, but i can't even force commit or rollback of that in doubt
>> transaction because I can't select from dba_pending_trans.
>>
>> g

>
> I will have to defer this question to someone who uses distributed
> transactions more frequently than I. I recall seeing similar DX locks
> when I experimented with queries in remote databases. Sessions would
> occasionally hang for no apparent reason. It seems like ghost sessions
> would also remain connected to the database, long after the calling
> session was terminated. At the time I located the above article that
> indicated a COMMIT or ROLLBACK was needed following a SELECT to clear
> the lock, and it seemed to make perfect sense.
>
> You might want to check the first two links on this search page:
> http://groups.google.com/groups?um=1&tab=wg&hl=en&q=oracle%20dx%20lock%
20lewis
>
> Mladen, thanks for the parameter hint.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

The paper I used when this has happened to me is the ML note 338880.1. Here is an excerpt:
" The Oracle distributed_lock_timeout limits how long distributed transactions will wait for a lock. This parameter was deprecated in 8i in favor of the '_distributed_lock_timeout' parameter (note the underscore), but was reinstated in 9i and above. This parameter should always be the largest, otherwise Oracle will time out first, and it's own mechanisms will attempt automatic recovery. The TPM then loses control of the transaction, and the transaction may go in-doubt and require manual intervention. ORA-1591 'lock held by in-doubt transaction' and other errors can result".

-- 
http://www.mladen-gogala.com

Received on Fri Jun 29 2007 - 13:44:59 CDT