Basically there exist two ways to implement optimistic locking. Both of them have advantages and disadvantages.
- One solution is to check the original state of every modified property against the current value in the database and only update the affected columns. This implies, that almost every update statement is a custom statement. You cannot use prepared statements to perform batch updates. This can be a major performance drawback.
The statements would look like this:
- The second possibility is to introduce some kind of update counter in your table. Before you update a record in the database, the current value of the update counter is checked against the base value, that was read by the client. If both values are equal, the update is performed and the counter increased. The update counter is either an integer or a timestamp.
This is a typical example for an update statement using an update counter:
The obvious advantage of this technique is the possibility to use prepared statements because the structure and the parameter list is the same for any given modified record in a table. But the chance is, that modifications are refused, even if they are not conflicting. The new data could be the same as in the persistet record, or only distinct properties could have been modified compared to the common base version. In both cases this solution would be quite frustrating or at least confusing from a users point of view.
If you implement optimistic locking with on of these patterns, you have to check against the number of modified records in your code, when you execute such a query. In pseudo code with a java like syntax this would look like the following snippet:
Neither the first nor the second obvious implementation is satisfying because of the mentioned drawbacks. Let's try to combine the advantages of both worlds at the expense of a slightly more complex pattern for update statements:
This implementation reduces the risk for wrongly refused updates to a minimum. It even allows special treatment of blob fields, that cannot be compared directly. Additionally you have the chance, to define special semantics for field groups, e.g. if any value in a group has been modified, the whole update can be denied, even if the concrete field that changed was not previously edited by another user.
I don't know about any implementation of this idea, but I am very interested in experience from the real world. Especially reports about performance differences and the influence on the overall usability are welcome.