One of the typical challenges that a web application would encounter is concurrency. A data could be modified by multiple users all at the same time. The challenge is to persist the correct data.
For example, I took two mobile lines with my telco and I decided to inform the HR executive, Amy, about my new number. Somehow I decided to use the first number for business usage.
Amy logs into the HR application online and starts to enter my new number. Before she could save the entry, she was hijacked by her boss for a short discussion. At this time, for no particular reason, I felt that it would be better to use the second mobile number instead and decided to tell Amy. As she wasn’t around, I told her colleague, Betty. Betty did the change for me immediately. Satisfied, I left.
When Amy return, she clicked the save button and my mobile number was overwritten to the one that I do not wish to use instead.
Now, this problem could be avoided with several methods. We could have prevented Betty from saving the change. That is, we lock the record and prevent anyone else from editing it once someone gains access to it for editing. This is viable, but it is not productive. However, this approach is perfectly fine if we’re in a single user environment. That is, only Amy has access to perform the edit. If this is not true, then the approach is flawed operationally. What if Amy forgot to complete the update and left for vacation? My number would never be updated.
The other solution would be to use optimistic locking. Instead of preventing others from editing the record, we allow multiple edits to happen concurrently. However, during saving, we check if the save would be correct. Using the same example again:
- Amy edits my record, the system detects that my record was last updated at X time.
- Betty edits my record, the system detects that the last modified timestamp was still X as Amy hadn’t save. Betty is allowed to update my record. The system updates the last modified timestamp to Y.
- Amy attempts to save her changes. During the save operation, the system detects that the last modified timestamp is no longer X, and Amy is disallowed from saving.
Some folks use a version number for this approach, however, this would mean that our database design has to cater to this. I prefer to use timestamp as the column has practical uses and it is still acceptable for database design. Even if I were not to use locking anymore, the column is still valid and not unnecessary.