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

"Bob Kilmer" <rprgrmr_at_yahoo.com> wrote:
>
> The most difficult part is trying to be sure that the begintrans, locks,
> commits and rollbacks all occur when they need to as you wind you way
> thru all the if, ands and buts of the code. This is not really the
> boolean's fault, but it is another detail that I look on with suspicion.

You are in the non-enviable position of having to maintain spaghetti code. you say rewriting it is beyond the scope of your task, yet I'm afraid making incremental fixes to it is not going to be much easier than re-writing it, because it is spaghetti code.

If you have one clear entry point (where you can first check the flag, and then set it), which it seems like you do, and one clear exit point (where you can unset the flag), which it doesn't seem like you have, then do you still have to hunt down and fix all the commits, rollbacks, and "select for updates"?

> Actually, there are three connections open on each client for the entire
> lifespan of the client; one using ADO with a client-side cursor, one
> using ADO and a server-side cursor, and one OO4O connection required
> because it supports array arguments to stored procedures, I am told. (Is
> this getting deep, or what?)

Yes, this is getting deep. That might be the first thing I'd fix.

> > > and issues a 'select for update' to lock
> > > the entity row just inserted. After clicking "Finish", the user has
> > > the option to enter additional information by opening other forms
> > > from buttons on the intake form.
> >
> > Why does it do a select for update before it knows if they will take
> > advantage of these options that require one?
>
> Just to keep other clients from modifying the data associated with this
> entity before the intake process can finish,

I'd give up on that. Once you release your lock, you have to assume someone could have snuck in before you re-gained the lock.

> and because these options
> are hang-on, after-thoughts that will manipulate the data in their own
> way. "IntakeExtras" they are called.

Let the "IntakeExtras" re-lock the data if they need to. Since adding the Extras is a separate transaction from the initial insertion, it shouldn't matter if once in a blue moon it has to wait for a different user who slipped in between the main Intake and the Extra, right?

>
> > > Because of the way things are - not
> > > necessarily because experts would agree this is a good idea - the
> > > code supporting these forms may need to access data locked by the
> > > 'select for update'.
> >
> > So what's the problem? You've locked the data for update, go ahead and
> > access it.
>
> I mis-spoke here or it is not that simple. It is not the same data, at
> least not all of it is. Some may be but other data is in other tables.
> Some may not be locked at all. I believe one of the problems is that
> these "extra" processes modify other tables and want to place their own
> locks on different sets of data using the same connection, and they can't
> do that with other locks active on the same connection.

Sure they can. One connection can take out as many locks as it wishes to, along as noone else has those locks. I think this goes to your original question:

"Can the client set a lock on Oracle db objects that is only a lock for others?"

Assuming by db object you mean a table row, this is exactly what a lock is, it only locks out other transactions. Perhaps the problem you have is that, with 3 connections per client, perhaps one connection will lock out another connection of the same client. Perhaps this the root of the problem?

> > I still don't get it. This is what locking does. Are each of your
> > modules operating on their own separate connection to the DB or
> > something?
>
> Well, I need to learn how to do this locking thing correctly or I have to
> toe the party line trying to follow the precident. You seem to be saying
> that I can lock all the data that needs to be protected from editing by
> other clients for virtually every modification I will need to make in an
> intake process, and do it in more or less one transaction, with one
> commit at the end. Is this so?

Yes, you can do this, if you don't mind it all being one transaction, i.e. all living or dying together.

> The current code seems to think that you can't avoid frequent commits,
> which resets locks. It drops one lock so another process can set its own.
> It locks data just so other clients can't modify it, not because it will
> necessarily change the data. It would be great to unify all this
> nonsense.

If a module is mandatory, i.e. the parent can't succeed unless the module does, then the module should inherit the transaction (and hence the locks) from the parent. If the module is optional, then it shouldn't inherit locks from it's parent, it should start a new transaction re-acquire locks when it starts, and commit when it ends (and of course the parent should commit for itself before it calls the optional module).

I know that your code isn't very modular, but you should try to make it more so. It sounds like you want to build a "super-lock" around the whole thing, rather than fixing the spaghetti. That might work to keep different clients from interfering with each other, but it won't prevent the code from internally interfering with itself, which I bet is a big risk.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB