Progress Programming Tips

PPT-9  Record lock strategies

Tip:

Record locking is a major part of any multi-user system.  Here
are some approaches to manage multi-user database access.

1. Lock Immediately

One approach to locking is to lock the record as soon as an
edit begins.  This is usually done when the user has selected
an edit menu option after locating the desired record.  In
character applications such operations were common as access to
other parts of the system were usually limited until the edit was
finished.  This method guaranteed access to edit for one individual at
a time.

This approach has some problems though, problems which are made
much worse by the advent of GUI and event-driven applications.

a. Possible long window of contention for that record.

If the user goes to lunch without completing the edit that record
remains locked for the duration.  Any other user who needs access to
edit that record is locked out.  For some types of records this can be
a critical problem.

b. Holding open a transaction during user interaction.

A second problem is that to hold an exclusive lock you must
have a transaction open.  If your application supports letting
the user go to another area, typical in a windows app, then
everything done in that section is treated as a sub transaction
to the current active transaction.  This makes it possible to
undo portions that don't seem related to the original task.

2.  Pseudo Lock Immediately

Another approach which finds some middle ground between 1 and 3
is to implement Pseudo Lock Immediately.  This can be done by
ALWAYS locking a record exclusive, then downgrading to share when
entering an editing situation.  Doing this locking as an atomic
activity before the user interaction keeps the transaction window very
small.

This approach still has problem a above but does solve problem
b allowing for a safe windows style application.

3.  Optimistic Locking

On the other end of the spectrum from #1 is Optimistic Locking.
You implement optimistic locking by not locking the record
until the user has acted to commit the record.  This has the
following impacts.

a. It is possible for more than one user to edit the same
record at the same time, thus generating a commit conflict.

b. Both the a and the b problems with #1 are avoided as no
locking has taken place and no transaction is active.

This method is called optimistic because the application is being
optimistic that no conflict will occur.  The degree to which this
assumption is true varies greatly with each application and area
within the application.  Regardless of the frequency of the conflict
the application must be written to handle the conflict.

Before every commit the application must check for whether the
record has been changed.  If not then proceed normally.  If it has
then a commit conflict has occurred for the second user.    A typical
response is to inform the user of the problem and offer either to lose
their edits by reloading the new record for review or allow for a
commit anyway, overwriting the first user's edits. Obviously a third
approach is possible - a reload that merges the records and deals with
conflicts on a field by field basis.  The third option is hard because
of visualization limitations - how to show the data in conflict in the
space where normally only one view exists.


Regardless of your approach it is important to recognize and plan for
the problem areas.  Some will argue that one approach is better than
another but I would answer it is really application dependent.

Wisdom:

It is far better to think first and avoid causing someone grief
than to apologize for it afterwards.

Rod Gaither                  | rdg@worldinfo.com
World Information Systems    | (910) 333-2580  Voice
Greensboro, NC               | (910) 333-2584  Fax