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

Cool Visualization

Digg Visualization

Check out the other styles at that same link - fun stuff!

Automation and the Console

When I'm writing software, I prefer to work with an editor and a set of consoles. Editor for code and consoles for building/testing. Works great in almost any language. Who needs those stinkin IDEs :)

One of the downsides to this is that the test/code cycle can get broken up by continually having to switch windows. With a full blown IDE you just hit some magic key and it all happens. What I wanted was this magic to happen but still keep the flexibility of having the 2 different tools.

Enter AutoHotKey. AutoHotKey allows you to script a whole bunch of windows stuff and it also allows you to setup system wide hot keys. So, I wrote a simple script that looks like this:

^+e::
WinGetActiveTitle, Title
IfWinExist Console
{
  WinActivate
  SendInput {Up}
  SendInput {Enter}
  WinActivate, %Title%
}

This script is set to register as the global hot key Control-Shift e. What is does it get/store the current window title, activate the console window, press the up key (to activate the last command), press enter and then reactivate the previous window. Works like a charm. Since I got it working I'm unsure how I lived without it.

Now that I know how to do it, I'm thinking of all kinds of global hot keys that I might be able to use.

Watir Bug (kinda sorta)

I stumbled upon this a while back and it caused me some grief. Today I saw some questions on the Watir NG that seem to be about the same problem.

Consider this irb session:

irb(main):001:0> require 'Watir'
=> true
irb(main):002:0> include Watir
=> Object

irb(main):003:0> ie = IE::start_process('www.google.com')
=> #<Watir::IE:0x2c7d86c ....>
irb(main):006:0> ie.div(:id, 'gbar').id

=> "gbar"
irb(main):007:0> ie.div(:id=>'gbar').id
=> "gbar"
irb(main):008:0> ie.text_field(:name, 'q').id

=> ""
irb(main):009:0> ie.text_field(:name, 'q').name
=> "q"
irb(main):010:0> ie.text_field(:name=>'q').name

TypeError: {:name=>"q"} is not a symbol 
irb(main):011:0>

Accessing an input element with a hash fails. Input elements in Watir currently only support a single search item and this is the cause of the confusion.

This patch will solve the problem:

require 'watir'

module Watir::Container
 alias :old_locate_input_element :locate_input_element

 #form elements do not allow the hash syntax for how/what.

 #this change here allows it for single element hashes by 
 #just converting the hash into a distinct how and what and then 
 #passing it on
 def locate_input_element(how, what, types, value=nil)
   how, what = *how.to_a[0] if (how.kind_of? Hash) && (how.length == 1)
   old_locate_input_element(how, what, types, value)
 end

end

All it does is convert a hash into the multiple args (how and what) that the input element look up requires. It doesn't do anything to allow multiple look up values for input elements.

Update: Turns out this this is a know issue.