Consider an e-commerce site, where Alice and Bob are both editing the product listings. Alice is improving descriptions, while Bob is updating prices. They start editing the Acme Wonder Widget at the same time. Bob finishes first and saves the product with the new price. Alice takes a bit longer to update the description, and when she finishes, she saves the product with her new description. Unfortunately, she also overwrites the price with the old price, which was not intended.

In my experience, these issues are extremely common in web apps. Some software (e.g. wiki software) does have protection against this - usually the second save fails with "the page was updated while you were editing". But most web sites do not have this protection.

It's worth noting that the controller methods are thread-safe in themselves. Usually they use database transactions, which make them safe in the sense that if Alice and Bob try to save at the precise same moment, it won't cause corruption. The race condition arises from Alice or Bob having stale data in their browser.

How can we prevent such race conditions? In particular, I'd like to know:

  • What techniques can be used? e.g. tracking the time of last change. What are the pros and cons of each.
  • What is a helpful user experience?
  • What frameworks have this protection built in?
  • You've already given the answer: by tracking the date of change of objects and comparing it to the age of the data that other changes try to update. Do you want to know something else, e.g. how to do it efficiently? – Kilian Foth Nov 25 '14 at 10:16
  • @KilianFoth - I've added some info about what I'd particularly like to know – paj28 Nov 25 '14 at 10:19
  • 1
    Your question is in no way special to web applications, desktop applications can have exactly the same problem. The typical solution strategies are described here: stackoverflow.com/questions/129329/… – Doc Brown Nov 25 '14 at 11:43
  • 2
    FYI, the form of locking you mention in your question is known as "optimistic concurrency control" – TehShrike Nov 25 '14 at 19:50
  • Some discussion related to Django here – paj28 Nov 26 '14 at 13:29
up vote 14 down vote accepted

You need to "read your writes", which means before you write down a change, you need to read the record again and check if any changes where made to it since you last read it. You can do this field-by-field (fine-grained) or based on a timestamp (coarse-grained). While you do this check you need an exclusive lock on the record. If no changes were made, you can write down your changes and release the lock. If the record has changed in the meantime, you abort the transaction, release the lock and notify the user.

  • This sounds like the most practical approach. Do you know of any frameworks that implement this? I think the biggest problem with this scheme is that a simple "edit conflict" message will frustrate users, but attempting to merge the changesets (manually or automatically) is difficult. – paj28 Nov 25 '14 at 15:26
  • Unfortunately, I don't know any frameworks that supports this out of the box. I don't think an edit confilict error message will be perceived as to frustrating, as long as it doesn't come up to often. Ultimately, it depends on the user load of the system wether you just check the timestamp or implement a more complex merging function. – Phil Nov 26 '14 at 8:28
  • I maintained a PC distributed database product that used the fine-grained approach (against its local database copy): if one user changed the price and the other changed the description - no problem! Just like in real life. If two users changed the price - 2nd user gets an apology and tries their change again. No problem! This does not require locks except during the moment when data is being written to the database. It doesn't matter if one user goes to lunch while their change is on the screen and submits it later. For remote database changes, it was based on record timestamps. – user251748 Apr 10 '17 at 14:45
  • 1
    Dataflex had a function called "reread()" which does what you describe. In the later versions, it was safe in a multiuser environment. And, indeed, it was the only way to get such interleaved updates to work. – user251748 Apr 12 '17 at 22:42
  • can you give an example of how to do this with sql server?\ – l--''''''---------'''''''''''' May 23 at 18:59

I have seen 2 main ways:

  1. Add the timestamp of the last update of the page the use is editing in a hidden input. When committing the timestamp is checked against the current one and if they don't match it has been updated by someone else and return an error.

    • pro: multiple users can edit different parts of the page. The error page can lead to a diff page where the second user can merge his changes in the new page.

    • con: sometime large parts of effort gets wasted during large concurrent edits.

  2. When a user starts editing the page lock it for a reasonable amount of time, when another user then tries to edit he gets an error page and has to wait until either the lock expires or the first user has committed.

    • pro: edit efforts are not wasted.

    • con: an unscrupulous user can lock a page indefinitely. A page with an expired lock may still be able to commit unless otherwise dealt with (using technique 1)

Use Optimistic Concurrency Control.

Add a versionNumber or versionTimestamp column to the table in question (integer is safest).

User 1 reads record:

{id:1, status:unimportant, version:5}

User 2 reads record:

{id:1, status:unimportant, version:5}

User 1 saves record, this increments the version:

save {id:1, status:important, version:5}
new value {id:1, status:important, version:6}

User 2 tries to save the record they read:

save {id:1, status:unimportant, version:5}
ERROR

Hibernate/JPA can do this automatically with the @Version annotation

You need to maintain the state of the read record somewhere, generally in session (this is safer than in a hidden form variable).

  • Thanks... particularly helpful to know about @Version. One question: why is it safes to store the state in the session? In that case I'd be worried that using the back button could confuse things. – paj28 Nov 27 '14 at 8:50
  • Session is safer than a hidden form element as the user wouldnt be able to change the version value. If that's not a concern , then ignore the part about session – Neil McGuigan Nov 27 '14 at 8:56
  • This technique is called Optimistic offline lock and it is in SQLAlchemy as well – paj28 Nov 27 '14 at 9:07
  • @paj28 - that link for SQLAlchemy doesn't point to anything about optimistic offline locks, and I can't find it in the docs. Did you have a more helpful link, or just pointing people at SQLAlchemy in general? – dwanderson Oct 30 '17 at 20:28
  • @dwanderson I meant the version counter part of that link. – paj28 Oct 30 '17 at 22:08

Some Object Relational Mapping (ORM) systems will detect which fields of an object have changed since being loaded from the database, and will construct the SQL update statement to only set those values. ActiveRecord for Ruby on Rails is one such ORM.

The net affect is that fields the user didn't change are not included in the UPDATE command sent to the database. People who update different fields at the same time don't overwrite each others changes.

Depending on which programming language you are using, research which ORMs are available, and see if any of them will only update columns in the database marked "dirty" in your application.

  • Hi Greg. Unfortunately, this doesn't help with these kinds of race conditions. If you consider my original example, when Alice saves, the ORM will see the price column as dirty and update it - even though the update is not desired. – paj28 Nov 26 '14 at 8:50
  • 1
    @paj28 The key point in Greg's answer is "fields the user didn't change". Alice didn't change the price, so the ORM wouldn't try to save the "price" value to the database. – Ross Patterson Nov 27 '14 at 1:12
  • @RossPatterson - how does the ORM know the difference between fields the user changed and stale data from the browser? It doesn't, at least without doing some extra tracking. If you want to edit Greg's answer to include such tracking, or submit another answer, that would be helpful. – paj28 Nov 27 '14 at 8:42
  • @paj28 some part of the system has to know what the user did, and only store the changes the user made. If the user changed the price, then changed it back, then submitted, this should not count as "something the user changed", because they didn't. If you have a system which requires this level of concurrency control, you have to build it this way. If not, not. – user251748 Apr 10 '17 at 14:49
  • @nocomprende - Some part, sure - but not the ORM as this answer says – paj28 Apr 12 '17 at 18:45

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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