I sort of promised to post my final solution to this problem and then of course forgot all about it. Now I finally remembered it and I had the time to write the post. So if someone should stumble upon this post at a later time perhaps this will help you a bit.

I finally ended with the optimistic locking solution; basically because it seemed simplest to implement. I thought quite hard about it and still believe that the row-locking solution could be implemented, but as you can see of the discussion above, not everyone agrees.

The optimistic locking is implemented by adding a column containing the current version of a row. This number is incremented each time the row is updated. The version number is included as an hidden field in the HTML page and when the user submits his/her update the value of the hidden version field is compared with the version number in the database. If the version number in the database is larger than the one in the hidden field some other user have performed an update.

This is quite easy to implement and this solutiuon was suggested by several of the posters (this was also the solution I used before starting the thread). The problem that remains to solve is what to do when the conflict is discovered.

I many cases it is ok to just inform the user that the update could not be performed and ask them to refresh the page. In my case this was not satisfactory as the user could use a very long time retrieving the necessary information from different sources and filling out the form. By refreshing the page they could lose a lot of work. To give them a more acceptable solution the following was implemented.

When the version numbers for a row conflict, each column in the row will be treated separately. This is so that only real conflicts at the column level need to be considered by the user. For each column there a four interesting possibilities:

1. The value was not changed by the user and was not changed in the database.
2. The value was changed by the user, but was not changed in the database
3. The value was not changed by the user, but was changed in the database
4. The value was changed by the user and was also changed in the databasse.

Case 1. is trivial and there is not conflict. There is no reason to bother the user with this case.

Case 2. and 3 requires some thinking and what you want to do depends on the application. Often these conflicts can be handled automatically by the system by choosing the most recent value. For case 2 and 3 that means the value of the user or the value in the database, respectivly. In my application such a conflict could in rare cases lead to inconsistent data, so I choose to let the user decided, but with a default suggestion already made.

Case 4 is a clear conflict and must be decided by the user.

To implement this you need three lists (or hashes) of values. One list contains the value submitted by the user, one contains the values presented to the user originally (that is the values in the database at the time the user loaded the page) and the last list contains the current values in the database. For those of you that like code, this is the loop I used to check for the conflicts. The code is not complete.

#mapping between columns with conflicts and their new values.
my %conflicts = ();

#mapping between the columns with warnings and their new values. 
my %warnings  = (); 

while ( my ( $columnname, $new_value ) = each %$new_values ) {

    #We know there is a conflict on VERSION and it is not interresting
    next if $columnname eq 'VERSION';

    my $old_value      = $old_values->{ $columnname };
    my $database_value = $database_values->{ $columnname };

    #all the values are the same, no conflict
    next if $database_value eq $new_value;

    #changed by user and updated in database, a sure conflict.
    if ( $new_value ne $old_value && $database_value ne $old_value ) {
        $conflicts{ $columnname } = $new_value;
        next;
    }

    #no other test was true, then either changed by user or 
    #in the database. That consitutes a warning.
    $warnings{ $columnname } = $new_value

}
[download]

Hopefully this was a useful explanation from someone with the same problem. BTW I will probably not keep checking this thread so don't expect any answers :)

As an side note I can mention that if you use the SQL Server datatype ROWVERSION you need to convert it to a string before placing it on the HTML page and comparing it. The following code will convert it correctly on a standard Intel box given that version number is stored in the variable $version.

my $hex_string = unpack "H16", $version;
[download]