| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: enqueue wait events, locks and the meaning of id1/id2
First of all, let me say that usenet is great. Yesterday I posted a
question, and 15 minutes later I have several replies. Amazing. Thanks
to everyone making usenet so usable and valuable. :-)
Brian Peasland wrote:
> 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.
I modified the query a bit, to this:
SELECT
TRUNC(id1/POWER(2,16)) rbs,
BITAND(id1,65535)+0 slot, id2 seq
FROM v$lock
This gives me the following output:
RBS SLOT SEQ
---------- ---------- ----------
6 80 1195257
6 70 1192089
6 42 1193083
5 72 1060150
5 96 1055042
6 16 1190901
5 14 1056651
5 9 1058258
6 1 1196554
5 51 1057005
5 1 1056275
RBS SLOT SEQ
---------- ---------- ----------
6 9 1189959
Is it possible to join this info with e.g. v$transaction to find the object?
>
> 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.
Hmm, I cannot find a TM lock for each TX lock. As a matter of fact, I have zero TM locks. :-(
The number of TX locks varies, and for most of the time the lock is not blocking other sessions. I've monitored v$lock and other sessions are mostly able to aquire TM locks, but sometimes the TX lock changes from non-blocking to blocking, causing other sessions to wait for a long time (most likely timing out). I believe this is the cause for the long enqueue wait times we have.
Some more facts:
- We have one 8.0.5 instance running on one server (Tru64)
- We have two application servers(Oracle App server 4.0) on two other
servers(Solaris)
- The app servers are the processes holding the TX locks - The TX locks are helt until the app server process is restarted. - I've joined v$lock with v$session and v$sqltext, and all sessionsholding TX locks are/was running PL/SQL procedures. - We have lots of buffer busy requests as well, almost all of them of type Data Block.
My current working theory is that the app server for some reason fails to either commit or rollback a transaction, causing the TX lock to block other transactions. The app server also fails to return the db connection handle to the connection pool, causing the application to slow down and ultimately fail due to lack of connections. The restart of the app server process 'fixes' the symptoms, but not the cause.
The application running on the app servers is a mix of off the shelf SW (Oracle iStore v.3 or 4) and custom code written by consultants no longer available. I'm sure it'll be a lot of fun trying to debug that mess...
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.
As I wrote earlier, the TX lock has no matching TM lock, but I'll try to use the ID1 column for the TM lock for the blocked session. Hopefully that'll give me some clue.
Thanks a lot for your help. I appreciate it a lot. :-) Bjørn
>
> 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
 |
 |