Recently I’ve been involved in an online discussion related to data update techniques. Part of the discussion turned to the issue of data updates and change tracking. A question came up:
>1) The SP receives and updates all fields every time.
>2) Contention management is via a pessimistic locking mechanism. Effectively you lock the entire row/s when a user enters edit mode.
>3) Therefore you don't need dataset/entity change tracking.
My answers:1) Yes. We update all columns.
2) True. Yup we lock the row or in the case of a complex entity we lock the header row which implies locks on the child tables.
3) Yes. That is true we don’t need these mechanisms.
We do use a lock table that contains table/entity/process name, key, user, date, lock notes, etc....
Long lunch syndrome or dead workstation is done via the age of the lock.
Old locks can be overridden, in the case of the dead workstation the same user can repossess there lock.
We generally have a clean locks routine incase of bugs or buildup.
The reason...When we started doing database work (even in the foxpro days) we used the same approach. So we did our best to take it to the SQL world and it has seemed to work OK for us.
We also have the ability to turn locks off as well. In a lot (not all) of web apps we don’t worry about contention as much as the owner of the data is the one manipulating it. Like in amazon the customer owns the record until a certain point and they are allowed to make changes to their order/customer info/etc....
Some background… we do ALL database access via Stored Procedures. We have our own custom CRUD class that does all of this via a common stored procedure API.
Our update transactions update all columns regardless of what changed.
We have our own semaphore based locking system.
So just like in that forum I am lifting the skirt here as well and would like to see how the CodeBetter world at large does this:
How do you handle updates?
How do you handle conflict checking and resolution?
How do Nhibernate or any other data access tools do this?
