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

<ctcgag_at_hotmail.com> wrote in message
news:20030421152453.594$4L_at_newsreader.com...
> rprgrmr_at_yahoo.com (rprgrmr) wrote:
>
> > a. It is true that other client workstations [may] try to access that
> > data (edit info related to that entity).
> >
> > b. The code uses the fact that the entity's primary table row is
> > locked to infer that another client is updating the entity's records
> > so "do not proceed - either wait or tell the user the records are busy
> > - try again later." The code, meanwhile, maintains a boolean that it
> > intends to represent whether the row is locked or not. This boolean is
> > only as reliable as the programmer is diligent in its maintenance
> > across many code modules and procedures. It cannot be said that it
> > unequivocally represents the state of the lock at the database,
> > because it is not the database and it could be wrong. I think this
> > boolean is redundant and a maintenance headache and should be
> > eliminated.
>
> It sounds like you want to replace it with a flag column in the
> database which will have the same issues. It is still up to the
programmer
> to make sure that the flag column is in the proper state. There are three
> things that all need to be reconciled: Whether the row actually is locked
> (Oracle), whether the program thinks it is locked (state of a boolean
> variable in the application, or the state of a flag column), whether the
> logic of the application demands that it *should* be locked at this point
> to ensure integrity. If your boolean variable under the programmer's
> control stored in the program can't reconcile these things, why would a
> different variable under the programmers control stored in the database do
> a better job of it?

The boolean just tracks the setting of the lock in code. It's set true after a line of code that sets a lock. It gets set to false after a rollback or a commit. At times, the boolean is checked before a rollback is performed, presumably to avoid trying to rollback a transaction that may never have been initiated.

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.

I agree a DB flag would have its own issues, and I am not sold on it, but it has some potential advantages, at least conceptually: it would be in the database where the other clients could check it in lieu of locks, so the trouble of managing repeated transactions and locking would go away. A flag could be set once at the beginning of a long series of intake or modify processes and reset once at the end.

As it is, the code tries to maintain a lock on the data related to an entity throughout the intake process, but it actually drops it and picks it up again numerous, maybe dozens of times to accommodate every little bit of code's need to massage the data in its own way. Combined with very poor modularity, the code is very difficult to follow and difficult to modify. One routine intended to validate an entity actually *ends* by *beginning a transaction* for no better reason than the programmer knew that a transaction would be needed when control returned to the caller. Another (I'd say) accidentally recursive routine - a switch routine - starts by routinely rolling back the current transaction after having started it in its previous incarnation.

>
> > Allow me to describe an intake process in some detail. In an intake
> > process, the user opens an intake VB form and enters a unique
> > identifying number already assigned by an external business system.
> > The code updates a client_id/entity_id table with the entity id to
> > inform other clients (who will check this table before an intake) that
> > it will be handling this intake entity.
>
> Is there a commit at this point? If not, then the other clients aren't
> informed, as they can't see the entry. (Forgive me if a knowledge of VB
> forms would answer this question, I know nothing about VB forms.)

I don't have the code in front of me, but it must be that a commit occurs. The other clients know when one of them has begun to create a new entry.

>
> > The user enters additional
> > required information in the still open intake form. They click
> > "Finish," which does not close the intake form, but does trigger a
> > process whereby this form info is inserted (literally) into the
> > primary table and other tables in the DB.
>
> Is there a commit at this point?

Yes.

>
> > The id is then removed from
> > the client_id/entity_id table.
>
> Is there a commit at this point?

Yes.

>
> > The code then begins a transaction on a
> > perpetually open connection
>
> What connection were the things before this point occuring on?

On the same connection. It has been open since the app began executing.

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?)

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

> > 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. (Like I said earlier, I don't have the code in front of me. I am at home. I have a news server available from home, but only Google at work.)

> > So, the code in any new form opened will issue a rollback
> > to negate the lock while it modifys data it needs to modify.

> I don't get it. This new form must be on the same connection that
obtained
> the lock

Yes

>(otherwise doing a rollback on this connections wouldn't release
> the lock held by the other one), so why does it need to release it's own
> lock on the data before it can modify it?

Well, good question. I guess I started explaining that above. The lock is not on the totality of the data that will be modified. And it will not be modified all at once, or in one transaction, or with a single commit. These "extras" were originally separate tasks - they have their own place on the application menus - but they are tasks that manage some subset of properties of the primary entity, and they handle it in their own way. Later, these related properties were hung on the intake form (essentially AFTER the "OK" button click event, just between you and me) to allow the user to enter these properties at intake.

It is sort of like this: an app was developed for the front desk of a hotel for registering guests, but another app was developed for housekeeping where the staff enters the cleaning schedule and laundry needs. They each manage data about the same guests, but each was developed separately. So someone decided to hang the housekeeping software (and several other applets while we're at it) onto the registration software, but no one really integrated the data processing. I am struggling to find a better way, and trying to do this as an ancillary project to the tasks I am actually assigned.

> > Then, the
> > form code will begin a transaction and issue another 'select for
> > update' and return control to the calling routine. Once the user
> > actually ends the intake process, a final rollback releases the lock
> > on the entity.
>
> Wouldn't the final rollback also undo all the hard work you've done?

Not if it was executed properly. Commits have been issued when they were deemed necessary. Then another transaction started, another lock set. I suppose that boolean would be consulted, too, to see if we have started something to rollback. (It is so damned hard, not being the programmer to have built this haystack, to change things without getting something out of order.)

> >
> > I want to be as free as possible from having to lock and unlock
> > database objects explicitly. If I must lock db objects explicitly, I
> > want to lock them at the beginning of an editing session (an intake
> > process, say), still giving the editing session on the client holding
> > the lock complete access to data that is otherwise locked to others,
> > and then unlock them at the end. If I can rely on implicit locking, so
> > much the better. I can see a use for 'select for update' if an update
> > were actually pending, but having to set and unset this lock over and
> > over again across several code modules for the same overall process is
> > a bit much to easily keep track of.
>
> 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?

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.

> > I had an idea that it would be sufficient for clients to record
> > something in a table in the database to act as a flag to other clients
> > that indicates that a specific client is modifying data related to a
> > particular entity. I fully believe this would work, because I do not
> > believe that the locks currently being placed truly lock all the data
> > that is related to the entity being edited - the existence of the lock
> > merely acts as a warning to the other clients that a particular entity
> > is being edited, and, as a side effect, gets in the way of the
> > legitimate business of the client holding the lock. As is, every
> > client checks explicitly for a lock on an entity before proceeding. If
> > this is true, then it seems to me that a value in some table that
> > represents the fact of an entity being edited on a client would be
> > simpler but just as effective. A client need only set this value when
> > it intends to edit an entity (if it is not already off limits), do its
> > thing, then unset it at the end. Other clients could always check this
> > 'indication of editing' as they do now (flag or lock, who cares?).
>
> I don't see much of a difference between this and using locks to
accomplish
> the same thing, except that with locks, a dead session will automatically
> release it's lock, while with the flag, a dead session has to be
> explicitly cleaned up to free the rows it had "locked" when it croaked.

I agree.

Thanks sincerely for you comments.
Bob

>
> Xho
>
> --
> -------------------- http://NewsReader.Com/ --------------------
> Usenet Newsgroup Service New Rate! $9.95/Month 50GB
>
Received on Mon Apr 21 2003 - 20:57:09 CDT