The SQL repository can use a system of optimistic locking to maintain consistent versions of repository items. This optimistic locking system can be used in combination with any repository caching mode: disabled, simple, locked, and distributed.

To use the optimistic locking system for an item descriptor, add a version property to the item descriptor and a corresponding version column to the primary database table for the item descriptor. This version property must use data-type="int" or data-type="long" and the database column must be of a type that is compatible with the repository int or long type. The version property is identified in the item-descriptor tag with an attribute named version-property, the value of which is the name of the version property. For example:

<item-descriptor name="news" version-property="version">
  <table name="business_news" id-column-names="id"/>
    <property name="version" data-type="int"/>

The value of the version property is incremented every time the item is updated. Its value starts as 0 when the item is created, is set to 1 when the item is added, and is incremented in each subsequent update.

The version number for a particular item is read and associated with that transaction the first time that item is referenced in a transaction. If you try to update the item from a transaction whose version does not match the current version number in the database, a ConcurrentUpdateException is thrown to abort that update. This exception is a subclass of RepositoryException.

Here is a sample scenario that shows how the SQL repository uses the version property to implement optimistic locking:

This can be very useful for simple and distributed caching modes where there is a possibility of overwriting another Dynamo’s changes.

You can take advantage of optimistic locking in pages that include forms. Often in a form, you read the data in one transaction and update the data in another transaction. There is a possibility that another process might try to update an item in an intermediate transaction. To handle this case, you can place the version property value as a hidden field in your form. Then, you can either check that it is still the same yourself after you start the transaction which updates the item, or you just set the version property (along with the other properties in the item) and deal with the ConcurrentUpdateException when it occurs. For example, you can include in a page a hidden input tag like this:

<input type="hidden" bean="FormHandler.value.version">

You can also use a RepositoryFormHandler, which can set a version property just like it sets any other property of a repository item.