Should I lock rows in my cloud DB while they're being edited by a user

Question

I am creating a desktop application that persists the data in the cloud. One concern I have is a beginning to edit an item in the application and leaving it for a while causing the data to become stale. This can obviously also happen if 2 people try to edit the same item at the same time. When they finish up their editing and want to save the data I would either need to overwrite what currently exists in the database or check that they started editing after the last change and either force them to discard their changes or perhaps give them the option to risk overwriting someone else's changes.

I thought about adding a fields is_locked and lock_timestamp to the DB table. When a user begins editing the item the row would change is_locked to true and set the lock timestamp to the current time. I would then have some amount of time for which the lock is held (ex. 5 minutes). If anyone else tries to edit the item they would receive a message saying the item is locked and when the lock automatically expires. If the user walks away while editing the lock would automatically expire after a relatively short period of time and once it does the user would be warned that the lock has expired and be forced to restart the edit after the data is refreshed.

Would this be a good method for preventing overwriting stale data? Is it overkill (I don't expect the application to be used by more than a few people concurrently on a single account).

(Another concern I have is 2 people getting a lock for the same item, however I believe that is a race condition I am comfortable with.)

Answer 1

One thing that will help you here is terminology. What you are describing here is called 'pessimistic locking'. The main alternative to this approach is 'optimistic locking'. In pessimistic locking, each actor must lock the record before updating it and release the lock when the update is complete. In optimistic locking, you assume that no one else is updating the record and try. The update fails if the record was changed by some other actor.

Optimistic locking is generally preferable if the chance of two actors updating the same thing at the same time is low. Pessimistic is typically used when that chance is high or you need to know your update will be able to succeed before your start. In my experience, optimistic locking is almost always preferable because there are many problems inherent in pessimistic locking. One of the biggest issues is touched on in your question. Users may lock a record for editing and then leave for lunch. Your mitigation will help with that but the user experience will be no better than the optimistic approach and is likely to be much worse. For example, one user opens a record, starts updating it and their boss shows up at their desk. Another user tries to edit the record. It's locked. The second user keeps trying and after 5 minutes, the lock expires and the second user updates the record. The first user gets back to the screen, tries to save and is told they lost their lock. Now in the same scenario with everything the same except using optimistic locking, the first user's experience is pretty much the same but the second user doesn't wait 5 minutes.

The scheme you lay out would be greatly improved by implementing optimistic locking for the lock value but my hunch is that optimistic locking is probably OK for the whole thing and you can get rid of the is_locked field.

You don't provide what 'cloud DB' you are using. You should probably look into the features of that to see if there are built-in features for this before implementing your own solution.

Here's the basic recipe: instead of a is_locked field, have a version number field. When you retrieve the record, you pull the current version of the record. When you update, you make the update contingent on the version field matching what you retrieved and increment it on success. If the version doesn't match, the update has no effect and you report it back as a failure.

Answer 2

From @JimmyJames's answer, we can see how the question is really about user experience.

It all depends on context. How much time and effort does it take to update a record? How often do multiple users want to update the same document?

For example, if your users usually take a few seconds to update the record and there is little contention then optimistic locking is probably the way to go. At worst, a user will have to spend a few more seconds, maybe up to a minute, to update a document.

If your document is highly contentious but well-structured, maybe you can allow them to lock individual fields in 30-second increments, showing a timer or an unobtrusive notification to allow them to extend the lock.

However, if your users spend a considerable amount of time or effort then you should consider other approaches. Is your record well structured? Could you show the users a comparison (a diff) between the version on the server and the version they are trying to save? Can you highlight the differences? Can you let them merge the changes? Think of the experience you want to have with your source-control tools. You really don't want to be forced to write all that code again!

You can look at Google Docs for additional inspiration. Perhaps you can show the users a notification that a new version has been saved. Maybe you can show them how many people have the record open so that they may choose to come back at a less contentious time.