Re: Deep locking questions

Can you elaborate on "keep alive etc.,"?

• D

akolk - gelrevision.nl wrote:

> Doug,
>
> All resournces (locks, TX etc) are cleaned up by PMON if a processes exited
> ungrace full. If the process it self does a exit, it may be that the shadow
> is still around because it hasn't noticed the the client application is
> gone (Keep Alive etc comes into play here).
>
> Anjo.
>
> Doug Cowles wrote:
>
> > 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".
> > a) 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..
> > b) 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.
> > c) 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.
> >
> > - Dc.

Received on Thu Nov 25 1999 - 17:59:44 CST