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

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
On Error Goto

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

Daniel Morgan Received on Sun Apr 20 2003 - 12:57:24 CDT