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.infoline - 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
