Re: DX Lock problem
- From: Mladen Gogala <mgogala.SPAM-ME@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 29 Jun 2007 20:44:59 +0200 (CEST)
On Fri, 29 Jun 2007 07:55:28 -0700, Charles Hooper wrote:
On Jun 29, 6:37 am, sorc...@xxxxxxxxx wrote:20lewis
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%20lock%
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
.
- References:
- DX Lock problem
- From: sorcius
- Re: DX Lock problem
- From: Charles Hooper
- Re: DX Lock problem
- From: sorcius
- Re: DX Lock problem
- From: Charles Hooper
- DX Lock problem
- Prev by Date: Re: any guess or real information on who is performing at openworld 2007 cust app?
- Next by Date: OCS 10g User in Germany or Europe?
- Previous by thread: Re: DX Lock problem
- Next by thread: Re: DX Lock problem
- Index(es):
Relevant Pages
|
