TechTips: Locking and multi-user considerations in Access, dBase, Paradox/BDE

Nearly every application that we write has to deal with multiple-user access
in some way or another.  But Microsoft Access, dBase, and Paradox each handle
this issue in a different way.  And furthermore, you as an application
designer have many choices for how you choose to handle your multi-user
implementation.  Some of these choices are easy and obvious, simply a scale-up
of the single-user methodologies.  But there are other choices you can make ...

MIXED OBJECTIVES:
The tradeoff faced by a database-system designer is this...  how to facilitate
the needs of users who are entering or updating information simultaneously,
while placing minimal impact on the browsing activity that most users are
doing at the same time.

Every database will perform updates or inserts by assembling the changed
record in local memory, then "posting" the changed record to the database all
at once -- or, in the case of a transaction, posting several changed records
all at once.  This way, no one ever sees a half-finished version of a record.  
But what about that browsing user?  Shall we allow him to see the record at
all?  How much risk shall we take that someone is changing the same
record at the same time?  There are pessimists, and there are optimists.

MOSTLY RED OR MOSTLY GREEN?
"Pessimistic" locking assumes that the odds of two people trying to change the
same record at the same time is very great.  The record is locked before any
changes to it are made, and the lock is kept from the time the record is
retrieved, the entire time changes are being made, and until the changed
record is released.  No one else can attempt to change the record at the same
time; certain operations can't read it, either.  Paradox and dBase, operating
in local mode, operate in this way.

The trouble with pessimistic locking, so to speak, is that the table is
spotted with locks, there's a fairly large amount of locking going on, and if
a particular user's machine crashes there may be "ghost locks" left on the
table.

"Optimistic" locking, which is usually used by SQL servers, takes a different
approach.  A record is not locked while it is being modified.  Rather, the
machine that is performing an update must -check- to see if the record is
still present and unchanged when posting the updated changes to it.  If the
record has changed, or of course if it has been deleted, then the update is
unsuccessful and the client has to deal with it somehow.

Optimistic locking reduces the actual duration of a lock to as short a window
as possible:  while the update is actually being made.  So it maximizes
concurrency.  The updates that don't lend themselves well to optimism are
usually carried out by procedural (stored-proc or trigger) code directly on
the server.

SO WHAT DOES ALL THIS MEAN FOR MY APP?
When you are designing a multi-user application for good performance, the
biggest thing you need to keep in mind is that "your client's computer -is- a
computer, so use it as such."  Even when the DBMS you are using does not
formally recognize optimistic vs. pessimistic locking, you can still use
coding techniques that (a) minimize the duration and scope of locks on the
data; and that (b) care as little as possible whether the data is changed or
changing.

Your client computer has its own hard-disk drive; its own local storage and
tables.  You can write code to retrieve, say, an order from the server, then
*let it go* while the order is being updated.  The order record is not locked
at this time.  When the user is completely finished updating the record, then
you go back to the server.  "Bam!"  You get the record, be sure the change
number on the record hasn't changed... you post your updates (incrementing the
change-number so others can see that the record has changed) ... you add your
new records in one fell swoop and then "bam!" you're out.  If your DBMS
supports transactions then you wrap the whole change in a transaction and
commit or rollback at the end.

If the record has changed, which is probably unlikely for most apps (hence the
case for optimism), then and only then do you fall back and regroup.

AND FOR ME?
I guess the final point of this TechTip would be that multi-user apps and
single-user apps really do use different techniques.  The techniques that most
products make easy and convenient are those appropriate for single-user
desktop environments.  Records are locked, pages are locked, and they stay
that way for a long time but who-cares-there's-only-one-user.  Pessimistic.  
You can't do that with client/server.

Single-user apps that are being upscaled also commit the other deadly sin of a
multiuser system, and that is "browsing."  You can't let your users just
browse through a table... not with a list, not with a combo-box, not with
anything.  You've got to approach the server with a certain reverence ;-) ...
"this is what I want, not everything, just this."  Be as specific as possible,
make every transaction count, and you'll be rewarded with a sturdy application
that is easily upscaled.

---
Mike Robinson; Sundial Services, Scottsdale AZ
 (602) 946-8259; fax (602) 874-2068;  http://www.sundialservices.com
 "Your computer CAN do what you want!"  {tm}
 Serving the desktop database marketplace since 1992.