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.)

  • 1
    "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" - using a database transaction around the acquisition of the lock should prevent such race conditions.
    – Jules
    Jan 9, 2018 at 7:36
  • Most database engines (of the relational sort, at least) have built in support for this. It's a very common concept in the RDBMS world, and even "toy" databases handle it well enough, given that you tell it you want optimistic or pessimistic locking. Anyway, I don't think it's something you need to build yourself by any means: check the options on your db engine to see how to work with it.
    – jleach
    Jan 9, 2018 at 19:11
  • The fact that your application is a desktop application (a fat client) doesn't make much difference. It can be designed in the same way as a highly-available, multi-instance server application.
    – Hosam Aly
    Jan 16, 2018 at 7:06

2 Answers 2


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.

  • This is a very helpful answer. Can you explain why pessimistic locking is discouraged when theres a small chance of "collisions". Is it purely because it requires going to the database to even start editing, or because of extra complexity or some other reason? Thanks!
    – yitzih
    Jan 8, 2018 at 21:27
  • 3
    @yitzih Some suggested reading: Fowler et al, Patterns of Enterprise Application Architecture. There is an entire chapter on this question, with good discussion of all the options and reasons for choosing them.
    – Jules
    Jan 9, 2018 at 7:34
  • 2
    @Jimmy - Your answer is fine. In some cases, reporting an error back my not be sufficient. Consider a case when a user spends a few minutes updating a record. In that case, just failing may not be good enough, one may want to give the user the opportunity to merge their inflight changes with the ones committed by the other user. I should have said optimistic locking may require conflict resolution depending on your requirements.
    – Jon Raynor
    Jan 9, 2018 at 16:26
  • 3
    It's worth mentioning that pessimistic locking is a lot easier to explain to the client. If you write a message that the client cannot access the record because it is currently being edited by another, the client both understands just fine and generally accepts it. If you instead tell the client after saving that save wasn't possible because it was already updated by another user, 7/10, the client will be frustrated and/or expect explanations for this strange program behavior.
    – Neil
    Jan 10, 2018 at 8:08
  • 2
    @Neil That's true and I think it's often why pessimistic locking is used when optimistic is more appropriate. Often the workflow makes it very unlikely that there is a collision.
    – JimmyJames
    Jan 10, 2018 at 14:57

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.