Re: DX Lock in index
Date: Wed, 29 Apr 2009 11:05:45 +0200
Message-ID: <8580d4110904290205i15b6c398h6880a9190ee24143_at_mail.gmail.com>
After searching more for the problem. I started down the path of trying to find the session in the source DB that is responsible for the link and the DX lock.
THere are a bunch of queries around that looks at DB links, but littel information on how to find the sid and serial the connection came in from. It seems to boild down to the column x$k2gte.K2GTITID_ORA. It contains data such as MYDB.275c0045.329.19.35006, Where (I think) MYDB is the SID for the database a link comes from. Does anyone know if I can use the rest to find the sid in the database the connection came from? While I can find the SID in the local DB that holds the DX, I have not been able to figure out how to identify the sid of the session in the remote DB.
Mathias
On Tue, Apr 28, 2009 at 5:21 PM, Mathias Magnusson < mathias.magnusson_at_gmail.com> wrote:
> The problem is back and unrelated to the other lock...
>
> 10046 is hard to start when we don't know how to reproduce and can only see
> this after the block has occured.
>
> The event is enq: DX - contention. The row_wait_obj maps to a primary key.
> The block is shows for a file and block. row is 0, possibly meaning row zero
> or that the whole block is locked.
>
> P2TEXT for the event shows transaction entry #. Can I use that to somehow
> learn more about the block (and not just find the blocker)?
>
> Does anyone have any data on what this block means, how it is caused, and
> what the solution is? I realize that if it is similar to a normal TX block,
> you solve it by managing the transaction, but depending on the reason there
> may be multiple options for managing it.
>
> Mathias
>
>
> On Tue, Apr 28, 2009 at 3:28 PM, Vamshi Damidi <vamshireddy_1_at_hotmail.com>wrote:
>
>> mathias,
>>
>> enable 10046 events and see whats going with program you are running.
>> enable the event at session level.
>>
>> thanks,
>> Vamshi .
>>
>> *From:* Mathias Magnusson <mathias.magnusson_at_gmail.com>
>> *Sent:* Tuesday, April 28, 2009 9:19 AM
>> *To:* Vamshi Damidi <vamshireddy_1_at_hotmail.com>
>> *Cc:* oracle-l <oracle-l_at_freelists.org>
>> *Subject:* Re: DX Lock in index
>>
>> I understand that part, but why would the lock be registered against an
>> index? My understanding from the developers is that this lock on an index
>> was blocking. That is what confuses me, why would we get an index level lock
>> of type DX?
>> My current understaning is that the report on this was invalid. There was
>> another locking issue and it was reported to me as being the DX locks, while
>> the real issue was a normal local TX lock in the DB.
>>
>> We'll see if this surfaces again.
>>
>> Mathias
>>
>> On Tue, Apr 28, 2009 at 2:48 PM, Vamshi Damidi <vamshireddy_1_at_hotmail.com
>> > wrote:
>>
>>> hi mathias,
>>>
>>> this is related to distributed locking mechanism
>>>
>>> check your code logic once again it acquiring a lock on remote database
>>> and has to be commited or rolled back for the trasaction to complete on
>>> remote database.
>>>
>>>
>>> thanks,
>>> Vamshi .D
>>>
>>> *From:* Mathias Magnusson <mathias.magnusson_at_gmail.com>
>>> *Sent:* Tuesday, April 28, 2009 8:09 AM
>>> *To:* oracle-l <oracle-l_at_freelists.org>
>>> *Subject:* DX Lock in index
>>>
>>> List,
>>>
>>> I have just encountered a situation where we get blocking locks on
>>> indexes where the locktype is DX. I didn't think blocking occured on index
>>> for regular SQL. Does anyone recognize this or know of a document that goes
>>> into detail on DX locks. I'm not finding much detail on this locktype.
>>>
>>> I don't have a current lock situation to pull data from, but last time
>>> the locked object seemed to map to an index. Isn't DX supposed to be a
>>> transaction lock controlling the undo and other things required for a remote
>>> incomming transaction? That is, should I expect it to block data and/or
>>> index? I'm mostly looking for verification that the current conclusion that
>>> this was in an index is unlikely and we need to track the lock next time to
>>> try to find a target more natural for DX.
>>>
>>> Mathias
>>>
>>>
>>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 29 2009 - 04:05:45 CDT