Correct way to deal with optimistic lock in jOOQ without records

Question

I want to implement optimistic locking in my project for concurrent requests. I'm using Java 17, Spring and jooq.

While reading the documentation, I found out that jOOQ has support for optimistic locking out of the box, but I decided to implement it myself, because I don't want to use jooq records (I am JdbcTemplate adept, and I really love this way of writing persistence layer). As a result, I expanded my table with a version column, and when updating an entity, I check that the version is not different and increment the version.

The main problem is that I don't understand how to work with exceptions correctly. When updating, I want to understand what went wrong: we didn't find the entity, or the version is different.

Now, I have something like this in my update method:

public void update(SomeEntity value) throws SomeException {
        boolean isUpdated = dsl.update(TABLE_NAME)
                .set(TABLE_NAME.FIELD1, value.getField1())
                .set(TABLE_NAME.LAST_UPDATE, OffsetDateTime.now())
                .set(TABLE_NAME.VERSION, TABLE_NAME.VERSION.add(1))
                .where(TABLE_NAME.ID.eq(value.getId())
                        .and(TABLE_NAME.VERSION.eq(value.getVersion())))
                .execute() > 0;

        if (!isUpdated) {
            throw new SomeException();
        }
    }

In this case, I can't understand what went wrong: there is no entity with such id, or the version defers.

I would greatly appreciate any advice or guidance on how to implement this functionality correctly.

Answer 1

jOOQ's own optimistic locking implementation doesn't distinguish between these two cases because the distinction isn't relevant (and it would be hard to get right with a single query and without pessimistic locks).

Why is it irrelevant? Given the assumption that you're not just passing around random IDs, the ID you have must have existed at some point, so the only reason why it doesn't exist anymore is because someone deleted the record. Hence, it is just another case of where optimistic locking should raise your SomeException, not much different from when the version has changed.