The short answer is it really depends on your application/requirements.
For my personal preference, I like to lock on all meaningful columns
with the exception of blobs unless there is a compelling data/business
requirement to not do so. You also need to be careful of triggers that
update things behind the scenes, too. If you have a trigger that updates
a last-modified column, you wouldn't want to optimistically lock on that
column because the trigger is going to be changing the value behind your
So my understanding is that Cayenne leaves it to the application to
implement optimistic locking.
I'm not sure how to do that well with Cayenne. My approach would be:
1) lock all relevant rows
2) load the relevant rows as Cayenne entities
3) compare for differences with the unmodified state
4) if all goes well, trigger Cayenne's commit.
(1) means I need to know all rows that might be affected. Cayenne tells me
about entities; does it tell me about the rows associated with them? In
the presence of flattened relationships, too?
(2) means opening another Cayenne context and loading the data I guess.
That should work out of the box.
(3) means a deep traversal of object networks, while avoiding triggering a
Not sure which parts of the Cayenne API to inspect for that.
Also, not sure how to get that done without writing lots of boilerplate
(4) should work out of the box.
So steps (1) and (3) are the ones I don't know how to do.
There's an approach that halves the number of server roundtrips: Use
UPDATE table SET field = :value
WHERE pk = :id
AND field = :old_value
and check whether that returns an update count of 1 - if field got changed
by another process in the time between local read and update, the UPDATE
will find no record that matches the criteria, do nothing, and return a 0.
Is there a way to do that in Cayenne?
(This technique is the one that the Hibernate folks use for their
implementation of optimistic locking.)