Re: Locks in Oracle 9i

Jan Pompe <jan.pompe_at_gmx.de> wrote in message news:<blcbhn$ak73f$1_at_ID-137428.news.uni-berlin.de>...
> Hi Guido,
>
> > just to add a bit to what Daniel and Anurag
> > wrote: if you forget to code a commit in your
> > procedure, you can issue this statement manually
> > after the proc's completion. And I'm very curious
>
> Ok.
>
> > to know what you do in your application that
> > causes Oracle to lock a table where you don't
> > *need* the lock. Could you please describe what happens?
>
> My program does a lot of inserts, updates (>10.000) and some stored
> procedure calls. Sometimes it just stops when trying to do an update,
> because the table is locked. And when I kill the program the lock on
> that table is still there.
> But I can't reproduce this behavior at will and it didn't happen today.
> So maybe I just did too many things at the same time like running the
> program on the client and coding/testing new procedures on the host.
>
> Greetings - Jan

Look at v$lock and the server reference manual to see what kind of lock you actually have. If your SP's are doing updates, you could be locking yourself. If you have access to metalink, there are some scripts for detailing lock information there, as well as many places on the web. It may also be that you are locking the tables that keep track of object information when inserting, if your db has not been properly administered. Sometimes cascading recompilations can have screwy effects too.

If you have top sessions or something similar, you can show explain plan for the session with the lock to see what code is being executed.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/tue/business/news_1b30music.html