Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Deep locking questions

Deep locking questions

From: Doug Cowles <dcowles_at_nospambigfoot.com>
Date: Thu, 25 Nov 1999 00:34:49 -0500
Message-ID: <383CCA79.CA6DA50C@nospambigfoot.com>


I have recently gotten a complaint from some developers that when their code
exits non gracefully, a lock is held on a table that prevents them from doing anything
when they get everything started again. Since they are developing, they have not
coded graceful exits (i.e. rollbacks or commits) from their framework code wherever
there should be, and therefore are ending up with a situation where there is some sort
of vestige of a "select yadayda from yaadada for update nowait" when they try again.
They are using a custom framework (as opposed to a package like enterprise java
beans or something) writting in java, that is providing a transaction locking mechanism.
One initial comment - if anyone can respond - I have noticed when they are messing
around that the JDBC sessions show up as osuser "oracle" in v$session, and are a little
tricky to trace to whoever was the instigator. (if anyone can confirm this I would
appreciate).
But.. on to the locking questions...
Under what circumstances can a lock be left "hanging around".

  1. does it definitely require a leftover process and/or session , or not?
    (we are using oracle 8.1.5 on AIX 4.2.3) I'm reasonably confident that
    I can tie a lock to a specific SID, but if the developers are claiming that their application is exiting, I'm at a bit of a loss as to why there would still be a session and/or process..
  2. Assuming some sort of leftover lock, does Oracle clean these up? If so, under what sort of time-frame, and can I adjust it with init.ora parameters or other means.
  3. If I want to get rid of a lock (TX I would assume), do I have to do this by killing sessions? Or, is there a way to force the realease of a lock?

Next, if anyone cares to comment on my approach to this matter. I have insulated the developers from the data model in the sense that they log in as a
user that has only the privilege to connect, and create a synonym. These synonyms point
to where I work on the data model, and therefore they cannot mess with tables or do
any damage etc.,

    What I'm thinking of doing is making a procedure that they can execute, that takes
a table name as a parameter, and then the procedure will hunt down the object id,
and blow away any sessions with locks on that table (from v$lock).
(Question:

what privilege will I have to grant the table owner to allow an "alter system kill session")

Lots of questions. Appreciate anyone's comments on any of the above issues.
Hope everyone is having a nice Thanksgiving.

Received on Wed Nov 24 1999 - 23:34:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US