Optimistic locking is a technique which is used to ensure that the original locked values in a row haven't changed since the time the values were read until the record is updated. This technique doesn't require physically locking a database row or page and can be used on databases which don't provide locking.

When Cayenne reads a record from the database, it records the original values in a 'snapshot'. Cayenne uses the snapshot values in the WHERE clause (for all attributes specified for optimistic locking in Cayenne Modeler) when it is time to UPDATE a record in the database. Because Cayenne uses real attributes instead of artificial ones (such as a version number column), it can detect changes that are made outside of Cayenne, such as a support person performing a DB modification and forgetting to increment the version number column. This makes it more robust in detecting changes.

For example, imagine you have a simple NAME table, which contains firstName, lastName, and primaryKey columns and you have specified optimistic locking on the firstName and lastName attributes in Cayenne Modeler. Suppose one of the rows has values of "john", "Smith", and 36. When Cayenne reads that record from the DB, the values will be stored in a snapshot. Then if a setFirstName("John") on this object is done and you commit the changes, Cayenne will issue SQL something like this:

UPDATE NAME
  SET firstname = 'John'
WHERE firstName = 'john' AND lastName = 'Smith' AND primaryKey = 36

Cayenne includes the original locked values in the WHERE clause to ensure they haven't been changed (either by another application or directly through SQL). Cayenne is also smart enough to know which values have changed (only firstName) and doesn't update the unchanged values.

Cayenne supports optimistic locking on both attributes and relationships (to ensure the join key hasn't changed). It is up to the developer to decide what to lock, though, using Cayenne Modeler. It isn't always useful or necessary to lock on every single value.

Useful Information If you are storing BLOBs, you probably do not want to lock on them since they can be rather large and send vast amounts of data to the server for an UPDATE (even if the BLOB isn't being updated). Typically, BLOB data would be stored in a separate table and you'd create a relationship to the row with the BLOB data.   Some databases (like Oracle) do not support optimistic locking on BLOB data.