On 29.06.2007 19:25, sorcius_at_gmail.com wrote:
> On Jun 29, 4:55 pm, Charles Hooper <hooperc2..._at_yahoo.com> 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%20loc...
>>
>> Mladen, thanks for the parameter hint.
>>
>> Charles Hooper
>> IT Manager/Oracle DBA
>> K&M Machine-Fabricating, Inc.
>
> Charles and Mladen,
>
> thank you for you support. Unfortunately I am stuck in a middle
> where my last lavel support (before issueing a SR to Oracle) says
> it's Bea's fault. Bea says it's Oracle's fault.
>
> I am afraid it is something related to undo segments but I wasn't
> able to reproduce the problem so far.
When searching metalink for "DX Lock" I stumbled across a bug entry that
had to do with blocking due to long running statements that use DX
locks. Could have been 3140038; the description looks pretty much like
what you have ("SELECT from DBA_PENDING_TRANSACTIONS can be blocked by a
long running SELECT") but it should be fixed in your version.
I also found bug 3571263 but that does not really seem related.
Documentation on the whole matter seems rather superficial so if you
find out more I'd be glad to see (we ran into a different issue lately:
mixing of loosely and tightly coupled TX branches in one DX forbidden;
this is an application issue of course). Concluding from your remarks I
reckon you work tightly coupled as the docs seem to indicate that for
loose coupling DX locks are not used.
Kind regards
robert
Received on Tue Aug 21 2007 - 11:53:19 CDT
