library cache lock | transaction enqueue lock 
Author Message
 library cache lock | transaction enqueue lock

Hi,

on an Oracle 8.1.7.3 on Linux database we have the following problem:

An application stops working because of locks. The app has 4 open sessions,
one session is blocking the others.
Session 1 waits for a library cache lock and blocks session 2 which waits
for a transaction enqueue lock.
Session 1 helds an exclusive transaction enqueue lock which is requested by
session 2. When I kill session 1 the
application reconnects to the database and I have the same situation like
before. Even shutting down the
application does not solve the problem. All but one session are closed. I
can't kill the one session with
"alter system kill session ....", I have to kill the dedictated server
process. Aftre doing that and restarting the app
everything works fine until the next blocking lock.
I know that a library cache lock will happen when I try to use a procedure
which is just compiling or something
similar, but the session are just doing updates.
Does anybody have an idea what's going on here?

Thank you in advance,

Knut



Mon, 29 Nov 2004 22:41:54 GMT
 library cache lock | transaction enqueue lock

If you look in $ORACLE_HOME/rdms/admin
for the script utllock.sql, this will create a couple
of views of library cache locks (and others) with
names like dba_kgllock and dba_lock_internal.

Next time you have a session waiting for a
library cache lock, check v$session_wait for
the p1 or p2 value, and compare it with the
lock identifiers in the internal lock views.
This could give you an important clue about
which object is the root cause of the problem.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Quote:

>Hi,

>on an Oracle 8.1.7.3 on Linux database we have the following problem:

>An application stops working because of locks. The app has 4 open sessions,
>one session is blocking the others.
>Session 1 waits for a library cache lock and blocks session 2 which waits
>for a transaction enqueue lock.
>Session 1 helds an exclusive transaction enqueue lock which is requested by
>session 2. When I kill session 1 the
>application reconnects to the database and I have the same situation like
>before. Even shutting down the
>application does not solve the problem. All but one session are closed. I
>can't kill the one session with
>"alter system kill session ....", I have to kill the dedictated server
>process. Aftre doing that and restarting the app
>everything works fine until the next blocking lock.
>I know that a library cache lock will happen when I try to use a procedure
>which is just compiling or something
>similar, but the session are just doing updates.
>Does anybody have an idea what's going on here?

>Thank you in advance,

>Knut



Mon, 29 Nov 2004 22:54:05 GMT
 library cache lock | transaction enqueue lock

says...

Quote:

>If you look in $ORACLE_HOME/rdms/admin
>for the script utllock.sql,

it's catblock.sql -- you need to run that before utllockt.  catblock has those
views defined in it.

Quote:
> this will create a couple
>of views of library cache locks (and others) with
>names like dba_kgllock and dba_lock_internal.

>Next time you have a session waiting for a
>library cache lock, check v$session_wait for
>the p1 or p2 value, and compare it with the
>lock identifiers in the internal lock views.
>This could give you an important clue about
>which object is the root cause of the problem.

>--
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk

>Next Seminars
>        UK            June / July
>        Australia      July / August
>http://www.jlcomp.demon.co.uk/seminar.html


>>Hi,

>>on an Oracle 8.1.7.3 on Linux database we have the following problem:

>>An application stops working because of locks. The app has 4 open sessions,
>>one session is blocking the others.
>>Session 1 waits for a library cache lock and blocks session 2 which waits
>>for a transaction enqueue lock.
>>Session 1 helds an exclusive transaction enqueue lock which is requested by
>>session 2. When I kill session 1 the
>>application reconnects to the database and I have the same situation like
>>before. Even shutting down the
>>application does not solve the problem. All but one session are closed. I
>>can't kill the one session with
>>"alter system kill session ....", I have to kill the dedictated server
>>process. Aftre doing that and restarting the app
>>everything works fine until the next blocking lock.
>>I know that a library cache lock will happen when I try to use a procedure
>>which is just compiling or something
>>similar, but the session are just doing updates.
>>Does anybody have an idea what's going on here?

>>Thank you in advance,

>>Knut

--

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp


Tue, 30 Nov 2004 01:14:30 GMT
 library cache lock | transaction enqueue lock
Jonathan,

I assume you meant catblock.sql. utllockt.sql (note the "t") gives you
a tree-like lock graph, for enqueue locks only, not library cache
locks.

One caveat about dba_lock_internal. It's very useful but it gives you
too much if you don't have a where clause when you select (of course).
But when you add a where clause that is slightly complicated, the
query never returns. I had to restrict with one condition and spool to
a text file, then search the file to find something I need. I think I
had this problem on 8.1.6, maybe 8.1.7.

Yong Huang

Quote:

> If you look in $ORACLE_HOME/rdms/admin
> for the script utllock.sql, this will create a couple
> of views of library cache locks (and others) with
> names like dba_kgllock and dba_lock_internal.

> Next time you have a session waiting for a
> library cache lock, check v$session_wait for
> the p1 or p2 value, and compare it with the
> lock identifiers in the internal lock views.
> This could give you an important clue about
> which object is the root cause of the problem.

> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk

> Next Seminars
>         UK            June / July
>         Australia      July / August
> http://www.jlcomp.demon.co.uk/seminar.html



Tue, 30 Nov 2004 03:32:05 GMT
 library cache lock | transaction enqueue lock

True on both counts.

I always have to look up the details before I do
this sort of thing, so I often get names wrong
(or "differently right" as it may soon become).

I actually tend to use the one which I think
is called dba_kgllock to start with, as it is
the minimal view without the extra UNIONS,
joins and garbage - there is also the benefit
that when trying to track the problem the
WHERE clause is dictated by one of the
parameters of the session wait - although
IIRC you could just restrict by session id
to keep it simple.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Quote:

>Jonathan,

>I assume you meant catblock.sql. utllockt.sql (note the "t") gives you
>a tree-like lock graph, for enqueue locks only, not library cache
>locks.

>One caveat about dba_lock_internal. It's very useful but it gives you
>too much if you don't have a where clause when you select (of course).
>But when you add a where clause that is slightly complicated, the
>query never returns. I had to restrict with one condition and spool to
>a text file, then search the file to find something I need. I think I
>had this problem on 8.1.6, maybe 8.1.7.

>Yong Huang



Tue, 30 Nov 2004 05:25:44 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Waited Too Long For Row Cache Enqueue Lock

2. waited too long for row cache enqueue lock

3. SMON Having lock on temporary segment (enqueue lock)

4. transaction enqueue lock

5. Library cache lock question

6. Library cache lock

7. Problem with enqueue locks

8. Transaction Locks: Rerunning transaction

9. DBMS_AQ.Enqueue gives incorrect enqueue time

10. Caching of execution plan & locks

11. Record Lock in cached update when using updateobject


 
Powered by phpBB® Forum Software