John,

I concur -- ideally you would have the optimistic locking infrastructure in place as a default, and throw pessimistic locking on top of that for an extra measure of conflict avoidance when you can. I'm starting to get a headache already... Because this would still leave the difficulty of intelligent and clear and simple end-user messaging when somebody changes data interrelated to data you are changing in the same record, and saves it before you do.

And yes, I also agree that it is probably best to update just the changed data, not the entire record/row...

I'm no .NET guru by any means, but I wonder: "Isn't there a framework level mechanism that handles all this? And if not, why not? People working with data centric applications run into these problems ALL THE TIME, so it SHOULD be built right into the core infrastructure of any language that needs to handle data, and .NET is certainly one of them."

Maybe I'm just missing something very obvious here. Maybe I should go get a beer.


Pertti

>Pertti, thanks for the age/retirement example.
>
>I'm wondering where you'd apply those rules- in a middle tier/BO or at the database? It would be safest at the database to make sure that split-second-apart saves don't break the rule. Either that or you'd need to semaphore the tier/BO managing the save, which would need to pull current data and apply changes against it for validation. Personally I'd be inclined to perform validation at the database.
>
>I take your point about the annoyance if somebody else sneaks in and marks a user "retired" while you are trying to reduce the age. Unless the new "retired" value is updated on screen, it wouldn't be much fun. KG also gave an example of an insurance company that presumably has the same sort of issue.
>
>I'm not convinced that this forms a rationale to use pessimistic locking all the time, however.
>
>IMHO it's best to implement change tracking and selective updates all the time. If you need pessimistic locking, add it over the top. Example- you might create a SP that checks a "lock" table for table and key and inserts a timestamped row for the current user if one doesn't already exist. You'd use a compound index to ensure that even locks requested at exactly the same time can't both succeed. If a row already exists for another user, the SP would check its age and nuke it if it's aged more than a defined amount. At save, you'd need to check again to make sure your lock hasn't been nuked and then you'd make your save and delete the lock. This is all "off the cuff" thinking at this point, obviously. ;-)
>
>Using the above technique, you can provide either optimistic or pessimistic locking using exactly the same classes, with no troublesome reverse-engineering to introduce alternative functionality after the event. The optimistic technique still works beautifully when you've wrapped it in a lock. If a customer using optimistic locking changes their mind and wants pessimistic locking for a specific screen, you can probably add that using a separate xml settings file.
>
>This is why I'm so interested in change tracking. IMHO it's *the* place to start, even if people feel they don't need it today because they use pessimistic locking. IMHO there's no downside to sending up just the "retired" flag if that's all the user edited. It's neither necessary nor desirable to save the entire row just because you've got a pessimistic lock. IMHO.