JPA with Isolation.READ_COMMITTED acts as REPEATABLE_READ

Question

I'm seeing a behaviours that is a little strange in my test JPA app.

Simple table;

id
count

@Transactional(isolation = Isolation.READ_COMMITTED) public void testCount() {

 // I randomly fetch a row which has a count > 4
 Counts counts = myRepo.findFirstCountGreaterThanOrderByRandom(4);

 // This is a findById with explicit lock (SELECT * FROM counts WHERE id = 1 FOR UPDATE)
 counts = myRepo.findById(counts.getId());
 counts.setCount(1);
 log.info("counts = {}, counts);

}

This is a simple piece of code that randomly fetches a row from the DB that has a count > 4 and then call again the DB with a findById using FOR UPDATE.

Now if I do this:

• put a break point on the myRepo.findById(counts.getId());
• I open a MySQL Terminal and enter a new transaction

     BEGIN
     UPDATE car SET count = 1; 

• In the code I step next, it will correct block until the MySQL transaction commits;
• In the MySQL Terminal I COMMIT;
• In the code the lock is released and it continues to the log.info

There to my surprised the count is 4... (and not the expected 1)

If I do the opposit test

• In the code I put a break after the FOR UPDATE on the log.info line
• I open a MySQL Terminal
• List item

    BEGIN
    SELECT * FROM counts where count > 4 LIMIT 1;
    SELECT * FROM counts where id = 1 FOR UPDATE // it will lock here, good

• In the code I continue and let it commit;
• In the MySQL terminal the select continues and the count is correct 1

Why does in the code the behaviour is like a repeatable read even though I specify READ-COMMITTED? In jpa the issue is clearly the first read because if I remove it and I directly select for update after the lock gets released I correctly get the right count of 1

I have enabled the all transactions debug logs and I can see the right READ-COMMITTED is opened.

I'm using Spring Boot 2.7.0 and MySQL 8.0

Answer 1

I can't believe this. The second select is reading the value from the entityManager cache? How can this is be possible.

adding entityManager.clear() just before the myRepo.findById(counts.getId()); yeild the correct count as expected.

@vlad-mihalcea your expertise is welcome here. Is this normal? Why are the values being read from the cache. It doesn't make any sense no?

How many application don't know this and are currently vulnerable to this issue ?