Re: lock Oracle db objects, but only a lock for others?

Questions Embedded:

"Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message news:3EA2DF84.6075EA5_at_exesolutions.com...
> Bob Kilmer wrote:
>
> > Jim,
> > Thanks for your response. I am new to the app and the company. I didn't
> > invent the locking and unlocking chaos, I am trying to cure it. I do
read
> > the docs online and I will look at what you suggest, but it takes a
while
> > for it all to sink in and to find just the right answer and not just a
> > generic lesson in locking database objects.
> >
> > Now that I've finished being defensive, let me say the locking that the
code
> > uses is a 'select for update', sometimes with and without a nowait,
between
> > a begin trans and commit or rollback. But the code places a lock that
gets
> > in its own way! So it commits (or rollsback - who knows - On Error
Goto!) to
> > release the lock when it passes control to whatever code it thinks might
> > need access to the data that it would prefer to remain locked. And that
code
> > sets a lock again. (Or should. Or maybe not?) And this happens again and
> > again as the client opens and closes form after form getting or posting
> > data, locking and unlocking data, starting and ending transactions,
before
> > the finally the process is through.
> >
> > Besides all that, the app doesn't really lock all the data up for grabs.
The
> > app really just locks a mutually agreed upon row in a particular table.
This
> > lock then serves as a flag to other instances of the app not to fool
with
> > the data. Not that one couldn't fool with the other data that is not
locked.
> > So if all the clients are using this as a flag, they may as well use an
> > explicit flag, i.e., a flag table value, unless the app can set a
reasonable
> > lock that won't get in its own way, don't you think?. Intuition tells me
> > that this lock, unlock, lock unlock madness is bogus, especially when it
> > encumbers the code so, and isn't really locking all the data that is
> > exposed.
> >
> > Should the client be using db locking at all?
> >
> > Can the client set a legitimate lock that won't get in its own way?
> >
> > Would it be just as well to set a flag in a table of flags? (That is, in
> > effect, what is being done now - except it is a table of real data!)
> >
> > Is there another option that is cleaner, easier and more professional
that I
> > have yet to discover?
> >
> > I would prefer to be able to set a lock or a flag exactly once at the
> > beginning of a client input or edit process, do the input or editing
> > machinations, then unlock or lower the flag (once) when I am out of
there.
> >
> > Regards,
> > Bob
> >
> > <snipped>
> >
>
> I'm just guessing based on things in your post but what catches my eye are
> things like:
>
> begin trans

Is Beginning a Transaction (followed by a rollback or commit (depending on how the procedure followed through) when the process is complete) bad?

> On Error Goto

What is the correct way to Error Handle in VB6?

>
> If things like this have been written expect that the entire code base
needs to
> be rewritten.
>

Can you please give an example of a correct construction for VB6 and Oracle?

(or is it the order, begin trans followed by error handling) (or is it that vb6 should not be used with Oracle)

I guess I'm not clear on how it should be done.

Thanks.

> Daniel Morgan
>
Received on Sun Apr 20 2003 - 16:11:01 CDT