Concurrency Control Methods. Is there a silver bullet?

Which concurrency control method do you use most of the time? "First Save Wins" ? (optimistic locking in ADO.NET) "Last Save Wins" ? (Overwrite the row, no matter what). Ever wondered what the difference between the two is when it comes to efficiency? Most people haven't and think "Last Save Wins" is BadTM and "First Save Wins" is GoodTM. But both make at least one person loose his work to preserve the work of another person. I read a thread today in the microsoft.public.dotnet.framework.adonet newsgroup where one person asked how he could make the SQL generator in Visual Studio.NET to use the Last Save Wins method and another person stepped in and bashed him for using a not very smart concurrency control method like Last Save Wins. But does it matter which one you pick when both are as inefficient as you can possibly make them to be (i.e. someone looses work) ?

What is a concurrency control method?
A concurrency control method is a method to regulate multiple requests (transactions if you will) for modification of a shared resource, for example a source file in a source control system or a table row in a database. Several different methods are known and each have their pro's and con's. Not all methods are usable in all areas of software development: the concurrency control methods often used in source control systems (if conflict then merge) is not usable most of the time in a database world. However, when there are more than a few methods, which one is the best? To answer that question, we first have to define what 'best' means in this context. Let's see why we should think about using a concurrency control method in the first place to formulate our answer.

Most developers just write the software they have to write and focus on a single user using the software, interacting f.e. with a shared resource, probably a database. When multiple users reference and modify data in a shared resource, the resource manager (f.e. SqlServer or Oracle) should take care of the issues related to the multiple transactions accessing the same table or row, or at least that's how a lot of developers think. In a way this is understandable because the developer is a user of the resource manager, and the resource manager should have that kind of functionality, right? Well, in a way it should but if it does have it, do you want the functionality in the way it is provided to you?

A simple example of a common problem: Joe and Jack want to modify the same row in a database. They do that using the same tool. Joe starts the functionality to edit the specific row and starts working on it. Jack opens the functionality a little later, but because he's the quicker thinker he beats Joe in completing the modifications and saves his work to the database first. When Jack is done editing the specific row, Joe finally completes his modifications of the same row and saves is work too.

Now... what should happen?

  1. Joe should be able to overwrite Jack's data. Jack looses his work. (Last Save Wins)
  2. Jack's work should be preserved, Joe shouldn't be able to save his work and thus looses his work. (First Save Wins)
Though call. If you like Joe, you probably would opt for option 1, if you like Jack you obviously opt for option 2 and if you are their boss, you should look for another tool because this one makes you loose money: the used application has a not so clever concurrency control mechanism implemented: more than one person can edit the same data. If the tool had option 1. implemented, the loss of Jack's data gets unnoticed, which can be very bad. If the tool had option 2. implemented, the loss of Joe's data is notified at the end of a modification process, like "Thanks for the work you've done, pal, but I can't use it. Throw it away and count to 10.".

Besides the emotional stress this can bring (this is no joke, there are applications who have screens which require 10 or more minutes to complete and at the end the user gets a notification that it was all for nothing because he can't save the work due to a nice concurrency control mechanism), the time spend by Joe on the useless modifications can be spend better. Both methods are inefficient: always at least one person looses time and work. Other known methods, like 2-phase locking (2PL / Strict 2PL), Multi-version-concurrency-control (MVCC in Oracle), other database-locking variants, they all cause the loss of work at the end of a workflow cycle, when the work performed is stored.

So there is no best method?
If you focus on the resources in the database and use low level methods to prevent multiple changes on a shared resource on that low level in the application stack, there is no best method: all are causing the loss of work in some form. The only true method that works is the method of good organization and an application which helps you with that, sometimes called Functionality Locking. In the example above, Joe and Jack are both modifying the same row. If you didn't wonder "Why?", start this article at the top and re-read it. If you did, you are on the right track. You see, the less concurrency you create in your application, the less problems it will bring. If Jack, who starts later with the same functionality, isn't able to start the editor for editing the same datarow, because Joe is already busy with the row, he can do something else with his time, because the modifications he has to make will or be lost or will cause that someone else looses his work.

It would be better if Jack then would be notified that a user "Joe" was already working on that row, which then would enlighten an inefficient way of working in the organization, which is fixed by transferring the work Jack had planned for that datarow to Joe, because he's working on that row already. This can be implemented with a common gateway, today we call them webservice. A common gateway to your shared functionality among users can control functionality locking for one application and also for more applications who want to modify the same shared resource.

So, every time you write code to pop up a window to notify the user he can't save his work because another user has already modified the rows he tries to overwrite, ask yourself this: "wouldn't it be better to notify the user before he started working on that rows?". Software is not created to keep us busy (ok, some is ;)), it is created (or better: should be created) for making processes more efficient and thus organizations which have a lot of such processes should perform more efficient. Software which tosses away even a fraction of the work done by a single employee because some other employee has done the same already, is inefficient and should be replaced, since it doesn't help the processes be more efficient, on the contrary, software should help the organization with the implementation of more efficient ways to perform the processes encountered in the organization.


  • Interesting...! I have to admit I usually say "Don't use functionality locking (your words) except when you really have too!" and that because of runtime efficiency. But this was a refreshing viewpoint!

    To see it your way, how do you tune the solution so that the second user isn't kept from doing what he wants too, for longer time than absolutely necessary? I usually use a custom lock table for this and I use a time interval for when the lock isn't valid anymore and that way I deal with ctrl-alt-del, for example. Do you have a better solution to this?

    Best Regards,



  • It depends on your application. If you are using a webapplication frontend, you're stuck in the request-response paradigm, so there is no other way then to lock using the mechanism you describe (which is commonly used, f.e. in CMS-es).

    Another scheme can be where you still have the lock-table where you store locks on functionality, but a service which manages the locks and communicates with clients bi-directionally. Clients will notify the service that they're freeing a lock and the service can then notify another client waiting in line to enable the functionality.

    But indeed, wmost of the time you are forced to implement a time-period to the lock on the functionality as well. However, if the organization is organized well and the software streamlined as well, it shouldn't be very common that a user tries to start functionality that's locked by another user.

  • Damn... gotta learn to be more to the point! :-)

    I wanted to end by asking if my concerns were legit because I do agree.... optimistic/pessimistic locking really doesn't cut it 100% of the time.

    Interesting post Frans!

  • (My previous comment was targeted to Jimmy)

    Interesting thoughts, Dave! How functionality locking is implemented can change from organisation to organisation. I can imagine a boss can overrule a lock on a given set of functionality or an admin can unlock a set of functionality at any given time, making the workflow continue. It is all about keeping the workflow going. If someone stalls it, it has to be tuned, both on the organisational side and on the software side.

  • Frans, yet again... study the Fitch and Mathers sample. For what I can remember it provides a nice, solid, clean way to stop people from bashing the broker.

    public enum Blah {

    Pending = 0;


    Nice post... the writing style reminded me of your friend Fowler, to the core of the problem in mummy_understandable communicative language ;)

  • Fitch & Mathers? hmm. Well like I said, it can be done on a lot of ways. I haven't looked into F&M a lot, but if they build in functionality locking, it's a nice real life example. :)

  • Great article, Frans! Reminds me of a Bill Vaughn quote - debating concurency resolution strategies is like arguing about where to stack the bodies after the accident, instead of putting up the traffic signals to prevent the accident in the first place.


    Handling concurrency on the front end allows for some big optimization gains at update time, such as batch updates (in the case of editable datagrids, for instance, a user can be editing several rows at a time). My favorite strategy for that is to pass in all the update rows in XML, use OPENXML in a SP to dump them into a temp table, delete the old rows and add the new ones (all in a transaction). The same procedure can just as easily free the row locks when it's done.

  • Are you suggesting to have something like pesimistic locking but without really locking the database? Like storing a row in a table that says 'I'm updating Customer #1'?

  • Mmm... I don't like it, it's a double edge sword....

    What happens if a programmer 'forgets' to check the table? What happens if there is a totally different application that you cannot control that is using the same database?

    The only way to implement it safely would be to use database locks, and we all know it won't scale.

    I have some ideas, I'll blog on them as soon as I can find some time ;)

  • If you take into account what can happen when a programmer 'forgets' something he should do, any given example of good technology will fail. I don't see that as an argument. Locking parts of an application's gui or inner works because the current user is not allowed to work with it is not an easy way out, I admit. It's far easier to just let the database figure it out, which will lead to systems which are not that efficient (as I wrote). You don't need database locks. You can write a singleton which manages the 'locks'. RDBMS-es are not using different systems for managing their locks.

    Using optimistic locking or similar to prevent concurrency problems is fighting the symptoms of badly designed software. It's better to design it RIGHT so concurrency problems are not an issue. If I might add: every major CMS vendor implements functionality locking, they lock in their guis editable items so other editors can't work on them while an editor is editing these items.

Comments have been disabled for this content.