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

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?

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

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

> The id is then removed from
> the client_id/entity_id table.

Is there a commit at this point?

> The code then begins a transaction on a
> perpetually open connection

What connection were the things before this point occuring on?

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

> 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.

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

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

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

> 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.

Xho

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