Optimistic Locking Revised

When you implement concurrency control in a business application, you have to decide if you use pessimistic or optimistic locking. For some types of applications optimistic locking is sufficient, because in the most common cases users would modify different resources, thus only a few of them would rant about lost changes. If you use optimistic locking, subsequent conflicting changes will be denied and the user has to update his view on the data and redo his modifications prior to saving the resources.

Basically there exist two ways to implement optimistic locking. Both of them have advantages and disadvantages.

1. 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:
   
   <br />UPDATE table_name SET<br /> data1 = :data1,<br /> data2 = :data2<br />WHERE pk = :pk<br /> AND data1 = :old_data1<br /> AND data2 = :old_data2<br />
   
   
2. 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:
   
   <br />UPDATE table_name SET<br /> data1 = :data1,<br /> data2 = :data2,<br /> data3 = :data3,<br /> blob = :blob,<br /> update_count = update_count + 1<br />WHERE pk = :pk<br /> AND update_count = :update_count<br />
   
   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:

<br />Statement s = findUpdateStatementFor(<br /> businessObj.getType());<br />s.setParametersFrom(businessObj);<br />int affectedRecords = s.exec();<br />if (affectedRecords == 0) <br /> throw new ConcurrentModificationException(<br /> businessObj, <br /> findLastModification(businessObj));<br />

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:

<br />UPDATE table_name SET<br /> data1 = CASE<br /> WHEN 1 = :data1_changed THEN :data1<br /> ELSE data1 END,<br /> data2 = CASE<br /> WHEN 1 = :data2_changed THEN :data2<br /> ELSE data2 END,<br /> data3 = CASE<br /> WHEN 1 = :data3_changed THEN :data3<br /> ELSE data3 END,<br /> blob = CASE<br /> WHEN 1 = :blob_changed THEN :blob<br /> ELSE blob END,<br /> update_count = update_count + 1<br />WHERE pk = :pk<br /> AND (update_count = :update_count <br /> OR (<br /> ( 0 = :data1_changed <br /> OR data1 = :data1 <br /> OR data1 = :old_data1)<br /> AND<br /> ( 0 = :data2_changed <br /> OR data2 = :data2 <br /> OR data2 = :old_data2)<br /> AND<br /> ( 0 = :data3_changed <br /> OR data3 = :data3 <br /> OR data3 = :old_data3)<br /> AND <br /> ( 0 = :blob_changed))<br />

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.