Re: Deep locking questions

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 - 03:51:35 CST