Hibernate reads dirty data in MySQL

Question

I have the following code in Java (Servlet) in a function (Wrapped with @Transactional(rollbackFor = Exception.class):

ModelAccount account = hibernateSession.get(**Class<T> type**, **int id**, new LockOptions(LockMode.PESSIMISTIC_WRITE));

When I run this function concurrently via 2 different threads, I notice that the first thread that passes this line gets for example (account.balance = 100). The second thread hangs until the first one commits. In my case, the first thread is updating the balance to 200. When the locked thread resumes, it still reads account.balance = 100 instead of the new value.

---

• My isolation level on mysql:

  SHOW GLOBAL VARIABLES LIKE 'tx_isolation';
  Variable_name   Value
  tx_isolation    REPEATABLE-READ
  

• No isolation specified on hibernate.

  ---

EDIT If I manually lock the row of the account, the hibernate code still acts the same even when the isolation level is READ-COMMITED on the database level and on the hibernate level @Transactional(rollbackFor = Exception.class, isolation = Isolation.READ_COMMITTED)

One last thing, when the run is executed I notice in the logs the following:

Hibernate: select id from accounts where id =? for update

This clearly indicates that hibernate did not fetch the whole details of the account but instead issues the command to lock it while using the data he has before.

Answer 1

Technically, that's not dirty data. Dirty reads are only possible on READ_UNCOMMITTED, so what you see here is repeatable reads, while you want non-repeatable reads instead.

The PESSIMISTIC_WRITE takes an exclusive lock on the selected database row, so no other thread can select this record until the first transaction ends (either commit or rollback).

The reason why the second transaction sees a value of 100 is because of the REPEATABLE_READ isolation level. InnoDB uses MVCC, and under REPEATABLE_READ, it guarantees that you see the records as if they were when the transaction has started.

If you want to read the latest value, you need to switch to READ_COMMITTED.

But even in READ_COMMITTED, you might still get a value of 100 if Hibernate has already cached that entity in the first level cache. Hibernate provides application-level repeatable reads, so once you load an entity, you'll get the same entity reference no matter how many times you try to load it.

Update

Make sure the @Transactional isolation level is taken into consideration too. If you're using JTA, that might be ignored.

If Hibernate has previously loaded the entity, it will not reload it unless you issue a refresh.

ModelAccount account = hibernateSession.refresh(ModelAccount.class, id, new LockOptions(LockMode.PESSIMISTIC_WRITE));

Answer 2

If you are using the @Transactional annotation, make sure you have the right propagation strategy. Take a look at section 16.5.7 in the documentation: http://docs.spring.io/spring/docs/current/spring-framework-reference/html/transaction.html

You may resolve the issue by specifying a REQUIRES_NEW propagation strategy.