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.

CodePudding user response：

To understand this, let's have a quick look on how hibernates optimistic locking works:

• 1: begin a new transaction

• 2: find an entity by ID (hibernate issues a SELECT ... WHERE id=xxx;), which e.g. could have a version count of 1

• 3: modify the entity

• 4: flush the changes to the DB (e.g. triggered automatically before committing a transaction):

  • 4.1: hibernate issues an UPDATE ... SET ..., version=2 WHERE id=xxx AND version=1 which returns the number of updated rows
  • 4.2: hibernate checks whether there was one row actually updated, throwing a StaleStateException if not

• 5: commit the transaction / rollback in case of the exception

With the repeatable_read isolation level, the first SELECT establishes the state (snapshot) which subsequent SELECTs of the same transaction read. However, the key here is that the UPDATE does not operate on the established snapshot, but on the committed state of the row (which might have been changed by other committed transactions in the meantime).

Therefore the update does not actually update any rows in case the version counter was already updated by another committed transaction in the meantime, and hibernate can detect this.

Also see:
https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
Repeatable Read isolation level SELECT vs UPDATE...WHERE

CodePudding user response：

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