Optimistic locking strategy

When a user gets an optimistic locking exception, his usual behavior is to confirm the changes he made overwriting the existing ones.

This behavior seems to imply that a 'last wins' approach is better, because what will really happen is that the last one will win.

This could be true if not for the fact that there are some fields in the database that have redundant information, for example a Customer balance. In this case, if two users read the current balance, increment it, and perform an update a 'last wins' approach will make one of the users to lose the changes and the balance will be inconsistent.

We are thinking on provide a better alternative. In DeKlarit you write the logic that updates the balance with declarative rules, something similar to:

CustomerBalance = CustomerBalance + Amount if insert;
CustomerBalance = CustomerBalance - Amount if Delete;
CustomerBalance = CustomerBalance - Amount.PreviousValue() + Amount if Update;

This means we know which fields have redundant information. If we agree that the user will always overwrite the changes, what we need to do is to overwrite all the fields but the CustomerBalance, and for the CustomerBalance, read the current value and apply the business rules.

This also solves a common scenario where the CustomerBalance is updated very frequently, and using a pure optimistic locking strategy will imply that you'll have a big chance of getting a conflict each time you try to update (i.e., the Coke inventory in Wal-Mart).

I'm not saying this would apply to all cases, but it's seems to be a much better approach than the usual optimistic locking (and of course than the 'last wins' approach) in a lot of situations.

4 Comments

  • But, what's PreviousValue? you can only tell what it is when you exclusively lock the row, read the value, perform the logic, write the value. Other threads however can perform a read no matter what you do with locking, using select hints.



    The only true way of doing correct concurrency is by pushing developers to use functionality locking, i.e.: one process/user can modify a given data-element/set of data-elements at a time. This will make sure NO-ONE loses work (optimistic or pessimistic locking both cause loss of work) and the process is more optimal, because the person who loses work is now able to do other work in that time.



    This will work, because it's pretty stupid if you think about it, to allow 2 or more users/processes alter the same data at the same time.

  • .PreviousValue is the value that the row had when read (.OriginalValue in the DataSet). I don't need to lock it to know it.



    Functionality locking adds a lot of complexity to the developer and my job is to make developer's life easier. Also, functionality locking and is similar to pessimistic locking in terms of scalability. Is better but similar.



    Using this approach the user never loses data, as he never gets an optimisic locking exception. Sure, the first one that updated the data loses it, but data can always be overwritten anyway if the second user reads is/updates it after the first one commited the changes. This approach is a 'last wins' that keeps the data consistent.

  • How about optimistically updating, checking for consistency. If any inconsistency is found, wait a random no. of seconds and redo the update

  • But after that number of seconds, it will fail again, won't it?

Comments have been disabled for this content.