LockModeType.OPTIMISTIC and Mysql's default isolation level REPEATABLE READ don't work together?

I am trying to learn JPA with hibernate and use MySQL as the db.

From my understanding,

LockModeType.OPTIMISTIC: The entity version is checked towards the end of the currently running transaction.

REPEATABLE READ: All consistent reads within the same transaction read the snapshot established by the first such read in that transaction

Is it true that LockModeType.OPTIMISTIC in hibernate does not work with MySQL's default isolation level?

Say I have the following code:

tx.begin();
EntityManager em = JPA.createEntityManager();
Item item = em.find(Item.class, 1, LockModeType.OPTIMISTIC);
// Assume the item here has version = 0
// Read the item fields etc, during that another transaction commits and made item version increased to version = 1
tx.commit(); // Here Hibernate should execute SELECT during flushing to check version,
// i.e SELECT version FROM Item WHERE id = 1 
em.close();

What I would expect is that, during flushing, Hibernate would throw OptimisticLockException because the version of the item is no longer 0. However, due to the isolation level, in the same transaction Hibernate would still see the item in version = 0 and not triggering OptimisitcLockExcpetion.

I tried to search but seems no one raised such question before, hopefully someone can help clear my confusion on OptimisticLock.

If your question is actually is there a flaw in HBN implementation (or JPA specification) related to the following statement:

If transaction T1 calls for a lock of type LockModeType.OPTIMISTIC on a versioned object, the entity manager must ensure that neither of the following phenomena can occur:

• P1 (Dirty read): Transaction T1 modifies a row. Another transaction T2 then reads that row and obtains the modified value, before T1 has committed or rolled back. Transaction T2 eventually commits successfully; it does not matter whether T1 commits or rolls back and whether it does so before or after T2 commits.
• P2 (Non-repeatable read): Transaction T1 reads a row. Another transaction T2 then modifies or deletes that row, before T1 has committed. Both transactions eventually commit successfully.

Lock modes must always prevent the phenomena P1 and P2.

then the answer is yes, you are correct: in case when you are performing computations based on some entity state, but you are not modifying those entity state, HBN just issues select version from ... where id = ... at the end of transaction and hence it do not see changes from other transactions due to RR isolation level. However I would not say that RC isolation level performs much better for this particular case: it's behaviour more correct from technical perspective but it is completely unreliable from business perspective because it depends on timings, so just do not rely on LockModeType.OPTIMISTIC - it is unreliable by design and use another techniques like:

• store data from different domains in different entities
• take advantage of @OptimisticLock annotation to prevent incrementing of version when it is not required (actually this will poison you domain model by HBN annotations)
• mark some properties as updatable=false and update them via JPQL update in order to prevent version increment

UPD.

Taking the P2 as example, if I really need T1 (only read row) to fail if T2 (modify/delete row) commits first, the only workaround I can think of is to use LockModeType.OPTIMISTIC_FORCE_INCREMENT. So when T1 commits it will try to update the version and fail. Can you elaborate more on how your provided 3 points at the end can help with this situation if we keep using RR isolation level?

The short story:

LockModeType.OPTIMISTIC_FORCE_INCREMENT does not seem to be a good workaround, cause it turns reader into writer, so incrementing version will fail both writers and other readers. However in your case it might be acceptable to issue LockModeType.PESSIMISTIC_READ which for some DBs translates into select ... from ... for share/lock in share mode, which in turn blocks only writer and blocks (or fails) current reader, so you will avoid the phenomenon we are talking about.

The long story:

When we have started thinking about some "business consistency" the JPA specification is not our friend anymore, the problem is they define consistency in terms of "denied phenomena" and "someone must fail", but does not give us any clues and APIs how to control the behaviour in the correct way from business perspective. Let's consider the following example:

class User {
  @Id
  long id;
  @Version
  long version;
  boolean locked;
  int failedAuthAttempts;
}

our goal is to lock user account when failedAuthAttempts exceeds some threshold value. The pure SQL solution for our problem is very simple and straightforward:

update user
  set failed_auth_attempts = failed_auth_attempts + 1,
  locked = case failed_auth_attempts + 1 >= :threshold_value then 1 else 0 end
where id = :user_id

but JPA complicates everything... at first glance our naive implementation should look like:

void onAuthFailure(long userId) {
  User user = em.find(User.class, userId);
  int failedAuthAttempts = user.failedAuthAttempts + 1;
  user.failedAuthAttempts = failedAuthAttempts;
  if (failedAuthAttempts >= thresholdValue) {
    user.locked = true;
  }
  em.save(user);
}

but that implementation has obvious flaw: if someone actively bruteforces user account not all failed auth attempts get recorded due to concurrency (here I'm not paying attention that it might be acceptable because sooner or later we will lock user account). How to resolve such issue? May we write something like:

void onAuthFailure(long userId) {
  User user = em.find(User.class, userId, LockModeType.PESSIMISTIC_WRITE);
  int failedAuthAttempts = user.failedAuthAttempts + 1;
  user.failedAuthAttempts = failedAuthAttempts;
  if (failedAuthAttempts >= thresholdValue) {
    user.locked = true;
  }
  em.save(user);
}

? Actually no. The problem is for entities which are not present in persistence context (i.e. "unknown entities") hibernate issues select ... from ... where id=:id for update, but for known entities it issues select ... from ... where id=:id and version=:version for update and obviously fails due to version mismatch. So we have following tricky options to make our code to work "correctly":

• spawn another transaction (I believe in most cases it is not a good option)
• lock entity via select query, i.e. smth. like em.createQuery("select id from user where id=:id").setLockMode(LockModeType.PESSIMISTIC_WRITE).getFirstResult() (I believe that may not work in RR mode, moreover following refresh call looses data)
• mark properties as non-updatable and update them via JPQL update (pure SQL solution)

Now let's pretend we need to add another business data into our User entity, say "SO reputation", how are we supposed to update new field keeping in mind that someone might bruteforce our user? The options are following:

• continue to write "tricky code" (actually that might lead us to the counterintuitive idea that we always need to lock entity before updating it)
• split data from different domains across different entities (sounds counterintuitive too)
• use mixed techniques

I do believe this UPD will not help you much, however it's purpose was to demonstrate that it does not worth to discuss consistency in JPA domain without knowledge about target model.