Data Concurrency With a Version Lock

I ran into an interesting bug with using a version lock to control changes against a record in a database.

The system in question uses optimistic concurrency control to ensure that users do not overwrite each others changes. This is implemented as a version column in the database which is an integer column that gets incremented with each update of the row. When a record is updated, the version number of the current (in memory) record is compared against the record in the database if they are not the same then an exception is raised and the update is aborted. All pretty standard stuff.

This all worked well except for one problem. The typical usage pattern looked like this:

1. User views object with version X - the object is loaded from the DAL and given to the view, the view displays the relevant data from the object and stores the object ID in a hidden form variable.
   
2. User updates object by posting the view - the object is loaded from the DAL, updated and saved back to the database via the DAL. During this save, the version numbers are compared to be sure they are the same.
   

There were test cases that validated this behavior and it seems correct but it isn't. The implementation is only partially correct. The problem is in the second load of the object. Consider this sequence:

1. User A views obj with ver 1 at 10:00 AM
2. User B views obj with ver 1 at 10:05 AM
3. User B saves obj with ver 1 at 10:06 AM
4. User A saves obj with ver 1 at 10:10 AM -- this should fail

Step 4 should fail and it doesn't. It doesn't fail because the controller simply reloads the object (based on ID) and gets version 2, updates it and saves it. The DAL doesn't see any problem because the version numbers of the object being updated and the record stored in the database match.

What this implementation really checks for is the case where two users update the same record simultaneously between the second read/update cycle. While this can happen, it really isn't the common case and the time span here is likely milliseconds. In other words, the chances of this actually catching a concurrency violation are pretty small.

Fixing the problem is fairly simple, it requires the view also store the current version number along with the ID. Then the controller can request that the DAL load an object with the provided ID and version number. If that object no longer exists, then a concurrency violation has occurred and the user should be notified to resolve the issue. If the object was loaded, then the object can be updated and then saved via the DAL using the same check described above. Having both checks in place should do the trick.

This particular application is a C#/ASP.net application. It occurred to me that the ruby on rails framework has a feature which provides the same kind of concurrency check (lock_version) and I wondered how active record implemented the check.

I fired up a new rails application and generated a simple scaffold. It appears to me that rails is doing exactly the same thing and unless you include the version_lock in your views (or session I guess) it doesn't work. This is a bit confusing because most references to version_lock seem to suggest that just having the column defined in your schema is enough to get the concurrency check and don't really mention the fact that the 'working' version_lock must be available during the update. In fact, the lock_version example given in Agile Web Development with Rails is a good example of the problem I describe at the beginning of the post and only covers the smallest case.

Note, I'm not picking on rails, it just happened to be the first place I looked for an example :)