Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Deep locking questions
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".
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.