Re: DX Lock problem

On Thu, 28 Jun 2007 09:13:59 -0700, Charles Hooper wrote:

On Jun 28, 10:48 am, sorc...@xxxxxxxxx wrote:
Hello folks,

does anybody know the meaning of the v$lock.id1 column for a lock of
type 'DX'?
We are facing some problems w/ this type of lock. Sometimes a session
hangs after acquiring a DX lock in exclusive mode serializing
other sessions, any queries againts views related to distributed tx
([g]v$global_transaction,
dba_pending_transactions, ...) hang there forever. Killing the session
holding the lock doesn't help cause the lock is acquired with the same
mode
by another session. The only solution so far has been to shut the
instance.
I am trying to replicate the problem in our development environment
using a couple of
application witten in Pro*c but even if during some dml operation I can
see a DX exclsuive
lock, I havent't been able so far to stall the instance :(. If you guys
know what excatly means that column, or better if you know what Oracle
do
during an distributed (xa) transaction would be greatly valuable for my
debug activity.

The production env wher i get the error is: 9iEE 9.2.0.8 (2 nodes RAC)
Solaris 9 64
Bea Web Logic Server 8.1.4 (TM)

Thanks
g

Google search:
oracle dx lock
or
oracle dx lock commit rollback

Finds this page:
http://www.jlcomp.demon.co.uk/faq/find_dist.html

Quoting:
-----------------
Whenever a distributed query or DML statement is issued from an instance
a Distributed Transaction lock, DX, is taken on the transaction and is
visible through v$lock:

ADDR KADDR SID TY ID1
---------------- ---------------- ---------- -- ----------
070000000015CC48 070000000015CC68 16 DX 29

This entry will remain until the issuer either commits or rollbacks the
transaction. This means that a query on a remote object is a transaction
and it takes a rollback segment entry to support the two- phase commit
feature (see References). But while the above query on v $lock will show
distributed queries from this instance to a remote database it does not
show queries from the remote instance to this instance.
-----------------

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Of course, the problem is probably distributed_lock_timeout. I've seen
that with several WL installations: distirbuted lock expires while WL
still has the transaction going and the result is "in doubt" transaction.
The solution is to increase distributed_lock_timeout to 900 (15 min).

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

Relevant Pages

  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Can extra processing threads help in this case?
    ... SQLite for just the financial aspect of the transaction. ... in DATAFILE sharing. ... CReader LOCK() ...
    (microsoft.public.vc.mfc)
  • Re: controlling lock order in transactions
    ... Andrew J. Kelly SQL MVP ... > ROLLBACK TRANSACTION; ... > find in the clients sp that could be causing the dead lock is the join on ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: Tansaction/locking MSSQL2000
    ... >transaction behavior in single statements [not a BEGIN TRANSACTION ... >tables I need to lock so there is not a race later]? ... If the table is already held by an exclusive lock ... When the transaction is finished (due to ROLLBACK/COMMIT, releasing the ...
    (microsoft.public.sqlserver.programming)