Re: DX Lock problem

On Jun 29, 4:55 pm, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:
On Jun 29, 6:37 am, sorc...@xxxxxxxxx wrote:



On Jun 28, 6:13 pm, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:
On Jun 28, 10:48 am, sorc...@xxxxxxxxx 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.

g

.

Relevant Pages

  • Re: Processing batches, requesting comments with sqlldr and preapred UPDATes.
    ... We process a few thousand batches nightly, ... Other application can still see the table's contents but will not see the newly inserted rows until COMMIT. ... there is a DDL lock so that no one can alter the physical structure of the table until the transaction is complete. ... Oracle *does not* lock the table to stop others from reading committed data. ...
    (comp.databases.oracle.misc)
  • Re: row locking and inserts
    ... I am working in an application in which I want to lock a set of rows ... session 1> begin transaction ... session 1> commit ... You could get an Oracle DB fairly easily - download the free Oracle ...
    (comp.databases.oracle.server)
  • Re: DX Lock problem
    ... oracle dx lock commit rollback ... but i can't even force commit or rollback of that in doubt ... transaction because I can't select from dba_pending_trans. ...
    (comp.databases.oracle.server)
  • Re: Mein fehlendes =?ISO-8859-15?Q?Verst=E4ndnis_bei_Transaktion?= =?ISO-8859-15?Q?e
    ... > COMMIT || ROLLBACK; ... Es wird ein LOCK IN SHARE MODE gesetzt. ... Ob du ein SHARED LOCK oder ein exklusives Lock FOR UPDATE brauchst, ... > oder gar erst beim COMMIT in Zeile ...
    (de.comp.datenbanken.mysql)
  • Re: DX Lock problem
    ... oracle dx lock commit rollback ... calling session was terminated. ...
    (comp.databases.oracle.server)