6

Imagine that you have a form with which you can edit some data, let's call it a Person form. Let's imagine that a user opens up this Person form, receives the current data that is stored in the database and starts editing it. During this time another user opens the same Person form, receives the same Person data, quickly makes some modifications and sends back the newly modified data this way persisting it.

Now the first user that opened up the Person form wants to persist his / her modifications, too. He / she submits the form, but there is a problem: the data that this user edited is not consistent with the database anymore / is outdated (because of the second user's modifications). What would you do now? How would you solve this consistency problem?

10

This is a pretty common problem, and one of the reasons that web applications tend to commit right away after any change. Sometimes the right answer is do nothing. You have to decide up front how you intend to do this in order of complexity:

  • Last commit wins (default behavior)
  • Actively lock the record to prevent edits
  • Update screen if changes detected
  • Resolve conflicts after attempted commit (last because it's least favorable)

The behavior you are experiencing now is that the last person to save changes wins. It's not always a wrong answer, particularly if the amount of data is pretty small.

Active locking can be a necessary pain if you are committing a lot of changes all at once. The overhead to be able to support proper record merging is pretty high, so it's easier to prevent other people from being able to edit the record. You'll need to naturally expire the locks if the user neglects to unlock the record.

Single Page Apps (or desktop apps) have the luxury of being able to provide a much more granular level of integration. It helps if you allow server push to anyone with that record open. When you have the ability to push change notifications, the UI can update the UI to reflect what is currently on the server. In the case where someone else is editing the code, you can at least see what is on the server next to what you typed. This is pretty complicated, but if you can send patches from the SPA to the back end and vice-versa, it's possible. Even better, if the UI only has one control in edit mode at a time, the changes are much more fine grained.

The last option is to use versioning on your data, and detect the version of the data that you are changing is old on the server. In that case, you can send back a response for the user to manually edit the conflicts. This is a pretty crappy user experience since it's on the user to hunt and find where things have changed.

Bottom Line

You have to choose which of the many ways to handle this problem fits your application best. Sometimes the user will request active record locks. Sometimes they will demand the highly interactive version. There's tradeoffs, so you'll have to work with your user community to really select what's going to work best from an effort/time perspective.

  • Actively locking the record on a web application is, at best, extremely difficult (if done properly). Detecting and resolving conflicts is all but a crappy user experience, you can highlight external and conflicting changes (the same way we do when merging source code). From UX POV dynamic update is messy if you have more than few fields, can you imagine you form changing in front of you while you're editing something? You need notifications it did happen ...imagine to edit your answer together with someone else and her changes automatically applied while you're typing elsewhere... – Adriano Repetti Aug 21 '18 at 11:51
  • 1
    @AdrianoRepetti, There are no clean solution. It's a messy problem. That's why you need to take time to design how it should operate. Sometimes timeliness is more important, sometimes not as much. Your user base may have different needs than the others. However, you are overstating the active locking difficulty. It requires extra fields in the data store to keep track of who locked it and when. After that it's a matter of checking the lock and deciding what to do when you discover the lock. – Berin Loritsch Aug 21 '18 at 12:10
  • Of course it depends on your use-cases (at least number of fields, complexity of edits, number of concurrent users and average edit session duration) but it's not IMHO a difficult UX problem, there are good well-estabilished and well-working patterns (which probably involve at least two of the solutions you listed). Locking...I don't say it's an extremely difficult issue but surely much more complex than, let's say, the other two you listed after. You have to deal with abandoned sessions, client-side and network errors...it'll make the lock alone unreliable... – Adriano Repetti Aug 21 '18 at 12:29
  • ...or that "...deciding what to do..." euristic pretty complex and possibly buggy. It's definitely one possible solution (or sometimes simply required) but it hardly can be used alone outside very few special cases. – Adriano Repetti Aug 21 '18 at 12:31
1

For most cases, I'm suggesting a version based collision resolving strategy. The version can be implemented as an additional field incrementing after commit. If somebody modified older (not the latest) version:

  1. Throw an exception - not allowed.
  2. Write some field by field objects merging code.
  3. Override with the latest form data.

1 and 3 pros and cons are clear. 2 needs some conflicts resolving logic.

Merging. The baseline that changed fields without conflicts applied from both changesets and conflicts are resolving field by field. Field conflicts resolving accounts on the same principles as in 1-3, recursively, if you have nested objects.

0

In short:

  • Add a column to the table that holds a "version number" for the record.
  • Retrieve this column along with the other fields you show to the User.
  • In the application, before submitting a change, perform a calculation on the "version number" value (just adding one works reasonably well).
  • Include the "version number" in any update statements. DO NOT perform this calculation in-line in the SQL (which, being executed server-side, undermines the whole point of the exercise).
  • In the Database, implement a Trigger that reacts to updates. In this Trigger, perform the same calculation on the "version number" value currently in the database record (the "old" value passed into the Trigger). If the result of this calculation does not match the one submitted by the application, reject the update (throw an Exception).
  • In the Application, catch this Exception and either reverse the update to the "version number" value or otherwise prevent the User from submitting the [now obsolete] data again.
  • Please do not use triggers for this, a simple UPDATE ... WHERE ... is enough to determine a conflict (eventually even without a timestamp, column with the advantage to silently merge non-conflicting changes) – Adriano Repetti Aug 21 '18 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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