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 older 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.
However, this approach has some problems which were made much worse by the advent of GUI and event-driven applications:
a. A possible long window of contention for that record.
If a user, for example, goes to lunch without completing an edit, that record remains locked until the user returns to complete the edit. Any other user who needs access to edit that record is locked out. For some records this can be a critical problem.
b. Holding open a transaction during user interaction.
In order to hold an exclusive lock, a transaction must be open. If the application supports letting a user navigate from an edit option to another part of the application, as is typical in a GUI application, then everything done in the second option is treated as a subtransaction to the current active transaction begun in the edit option. This makes it possible to undo work that is unrelated to the original task.
2. Pseudo Lock Immediately
Pseudo locking is an intermediate approach between immediate locking and optimistic locking. This can be done by locking a record exclusively when beginning an edit, and then downgrading to a share lock afterward. Doing this locking as an atomic activity before the user interaction keeps the transaction window very small.
This approach also opens a potentially long window of contention, but it prevents the inclusion of unrelated activity within a transaction in an event-driven application.
3. Optimistic Locking
On the other end of the spectrum is optimistic locking. When optimistic locking is implemented, a record is not locked until the user has acted to commit the record. This has the following impact.
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 problems associated with the immediate locking strategy are avoided, because no locking has taken place and no transaction is active.
This method is called optimistic because the application assumes 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 conflicts, the application must be written to handle a conflict when it does occur.
Before every commit the application must check for whether the record has been changed. If the record has not been changed, the commit can proceed normally. If the record has been changed, a commit conflict has occurred for the second user. A typical response is to inform the user of the problem and offer either to reload the new record for review, which will cause the second user's edits to be lost, or to allow a commit anyway, which will overwrite the first user's edits. 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 more difficult to program because of visualization limitations: how to show the data in conflict in the space where normally only one view exists.
Whether one approach is better than another depends on the application. Regardless of the approach, it is important to recognize and plan for the problem areas. |
