Is there a 'best way' to handle table/record locking and editing in a
multi-user environment (not C/S)?

My Order application has a form with master records displayed in a
grid. The users can double click an Order in the grid which opens an
'edit' form showing the Order with its Details in a grid. Presently,
it opens the Order form with the Order table in the Browse state, but
I can see complications if 2 users edit the same Order at the same
time.

So is it a better design:
- when the user 'edits' an Order, open the form up so the Order table
is in edit state, thereby stopping another user editing this Order
(but they can still run reports on the table)?
- with the details table, nothing needed because by locking the edited
order, the same order's details cannot be accessed by another user?

Regards,
Pat

Pat

> Is there a 'best way' to handle table/record locking and editing in a
> multi-user environment (not C/S)?
I think there is no "best way" to do this but the way you want to do it.

> My Order application has a form with master records displayed in a
> grid. The users can double click an Order in the grid which opens an
> 'edit' form showing the Order with its Details in a grid. Presently,
> it opens the Order form with the Order table in the Browse state, but
> I can see complications if 2 users edit the same Order at the same
> time.
DBISAM has a property at session level called LockProtocol and by default it
is Pessimistic that means when you call the method Edit the engine locks the
records and it only unlock when you call the methods Cancel or Post. In that
way no other users can edit the same record but they can browse the record.

In other side, the other option of LockProtocol property is Optimistic that
means DBISAM do not lock the record when you call Edit method and lock only
at Post (lock the record, save the changes and unlock), in this way other
users can Edit the record but only one will save it. The others users will
receive the "famous" #8707 error (the record was changed or deleted by other
user).

I *ALL* of my applications I have used Optimistic locking without no issues.
In this way it is more clear to end users to understante what is happening,
because imagine the situation where one user put the table into Edit state
and leave the desk to drink a coffee.

Eduardo

>... The others users will
>receive the "famous" #8707 error (the record was changed or deleted by other
>user).
what logic do you use once the user gets that message? do you put the
Post in a

 try
     MyTable.Post
 except
    if error #8707
      begin
        MyTable.Refresh
        continue
     end
 end

Regards,
Pat

Pat

> what logic do you use once the user gets that message? do you put the
> Post in a
I simply show a message "Record was changed or deleted by another user" and
tell to users to restart the process. Sometimes you can use the code you
suggest but it depends on each situation.

Eduardo

>> what logic do you use once the user gets that message? do you put the
>> Post in a
> Sometimes you can use the code you suggest but it depends on each situation.

If the Refresh works, I may just do that.

>I simply show a message "Record was changed or deleted by another user" and
>tell to users to restart the process.

Does 'restart the process' mean the user looses what they just keyed
in? in other words, do they close the edit form they have been using
and open it back up again (and re-keyin the data)?

Thanks Eduardo.

Regards,
Pat

Pat


I built an 8708 eater ages ago. There's a good sample in the manual.

Roy Lambert

Pat

> Does 'restart the process' mean the user looses what they just keyed
> in? in other words, do they close the edit form they have been using
> and open it back up again (and re-keyin the data)?
As I said before, it depends on the situation. Sometimes the answer is *YES*
they need to re-type again the informations. But imagine the following
situation:

User 1:
Field 1: Content: TEST
Field 2: Content: VALUE

User 2:
Field 1: Content: TESTING
Field 2: Content: EVALUATE

User1 post the record before User2 and when User2 post the record he/she
will receive a #8708 error. If you call a Refresh you just clean the buffer
used and load the data from disk again then the situation of User2 will be:

User 2:
Field 1: Content: TEST
Field 2: Content: VALUE

You can develop a routine to handle previous data and compare what really
changes and assign the new values according some logic.

My experience with this has more than 10 years (or almost) using DBISAM and
it never cause me a problem neither an user complain.

Eduardo

"Pat" <pat@downunder.com> wrote in message
news:cjl2q3lqum6gdfigkebio2nddcgj72kdkd@4ax.com...
> Is there a 'best way' to handle table/record locking and editing in a
> multi-user environment (not C/S)?
>

Makes no difference if it is filesharing or C/S.

> My Order application has a form with master records displayed in a
> grid. The users can double click an Order in the grid which opens an
> 'edit' form showing the Order with its Details in a grid. Presently,
> it opens the Order form with the Order table in the Browse state, but
> I can see complications if 2 users edit the same Order at the same
> time.
>

It's a fairly common situation, and there is no perfect solution. You want
the ability to block other folks from accessing the master record.
Basically, what you would accomplish with paper files by taking the folder
containing this order to your desk while you work on it: nobody else can get
to it until you file it back.

IMO, first thing to do is to be SURE that it is a problem if two operators
update the same order at the same time. Many times after thinking the
problem through,  the answer is no.

> So is it a better design:
> - when the user 'edits' an Order, open the form up so the Order table
> is in edit state, thereby stopping another user editing this Order

Pessimistic locking is not good, IMO. I never use it.

> (but they can still run reports on the table)?
> - with the details table, nothing needed because by locking the edited
> order, the same order's details cannot be accessed by another user?
>

Depends on the application. If you for example update any field on the order
record when you modify line items (such as updating datetime of last update,
for example, or operator number - any change will do) then the second
operator will get an error when he tries to post, because something has
changed in the order record. At that time, he can refresh the order and
attempt to post again (which of course brings up the question, why bother in
the first place?) or restart his update, or whatever you decide.

Robert

Pat,

> Is there a 'best way' to handle table/record locking and editing in a
> multi-user environment (not C/S)?

C/S or not is not important here - the key is "multi-user". If it was
single-user, you would not need locking at all.
There is no "best way" to deal with locking - it always depends on the
number of possible concurrent users, the number of users that need to
simultaneously write to the table, the design of each application, the
nature of updates, etc.. For example, if only one user will
update/insert orders, even if many users need to read(reports, display)
from those tables at the same time, then you wouldn't need to worry
about the best way to deal with locking... almost anything will do.

DBISAM has 2 locking models (optimistic and pessimistic), but I must say
that I almost always use pessimistic locking.

> My Order application has a form with master records displayed in a
> grid. The users can double click an Order in the grid which opens an
> 'edit' form showing the Order with its Details in a grid. Presently,
> it opens the Order form with the Order table in the Browse state,

In my opinion, this is a good approach - you should only lock (Edit)
records at the moment (and if) they need to be changed (there are
exceptions to this "rule" - if you are interested I can later expand on
this).

> - with the details table, nothing needed because by locking the edited
> order, the same order's details cannot be accessed by another user?

I sometimes use this method and it works well. Of course you must always
remember to Edit the "master" before the "detail" table can be Edited,
to Cancel the "detail" when the "master" is Canceled, to Post the
"detail" when the master is Posted, etc...

Another method I sometimes use (means much more work but the ideal for
some complex cases) is to load the "detail" to a memory table when a
"master" record is Edited. All the editing of detail records is made on
the memory table. If then the user cancels changes, the memory table is
just discarded and the "master" table Canceled. If the user Posts the
"master", all "detail" records are deleted and replaced by the memory
table inside a transaction.

--
Fernando Dias
Easygate, Lda

> As I said before, it depends on the situation. Sometimes the answer
> is YES they need to re-type again the informations.

In our case that would not be admisible. We prefer locking the record,
because tables are very complex, some fields (blobs) are actually
entirely other small tables, etc. And yes, it happens that a user goes
out for lunch and leaves a record open, in which case ... we can't
help. It's a lot better to NOT LOOSE data than to have the second user
also go for a coffee

regards,
Lucian