Re: enqueue wait events, locks and the meaning of id1/id2

In the case of a TX lock, the ID1 and ID2 columns do not directly translate to a specific object. Rather, it points to a specific transaction in your rollback segment. The TX lock does not directly lock the object. The following query can help you determine which transaction is requesting this lock:

SELECT TRUNC(id1/POWER(2,16)) rbs,

       BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot, id2 seq FROM v$lock
WHERE sid=8 AND type='TX';

Use the appropriate SID in the above query for your session.

Note that when a transaction gets a TX lock, it also has a TM lock which stops DML on the locked object. So for the same SID, you should find a TM lock. For the TM lock of that SID, the ID1 column is the OBJECT_ID found in DBA_OBJECTS. This is a good way to find out which objects a session has locked.

HTH,
Brian

Bjorn Augestad wrote:
>
> Hello,
>
> Statspack telles me that my Oracle instance (8.0.5) has lots of enqueue
> wait events and each event is quite costly, averaging 2200 ms.
>
> The enqueue lock type is TX, with lock mode 6 (exclusive).
>
> I'm trying to figure out the names of the objects locked, but cannot
> find any documentation on how to interpret v$lock.id1 and id2 when the
> lock type is TX. Can anyone please help?
>
> Thanks in advance.
> Bjørn

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"