Why does @Transactional isolation level have no effect when updating entities with Spring Data JPA?

Question

For this experimental project based on the spring-boot-starter-data-jpa dependency and H2 in-memory database, I defined a User entity with two fields (id and firstName) and declared a UsersRepository by extending the CrudRepository interface.

Now, consider a simple controller which provides two endpoints: /print-user reads the same user twice with some interval printing out its first name, and /update-user is used to change the user's first name in between those two reads. Notice that I deliberately set Isolation.READ_COMMITTED level and expected that during the course of the first transaction, a user which is retrieved twice by the same id will have different names. But instead, the first transaction prints out the same value twice. To make it more clear, this is the complete sequence of actions:

1. Initially, jeremy's first name is set to Jeremy.
2. Then I call /print-user which prints out Jeremy and goes to sleep.
3. Next, I call /update-user from another session and it changes jeremy's first name to Bob.
4. Finally, when the first transaction gets awakened after sleep and re-reads the jeremy user, it prints out Jeremy again as his first name even though the first name has already been changed to Bob (and if we open the database console, it's now indeed stored as Bob, not Jeremy).

It seems like setting isolation level has no effect here and I'm curious why this is so.

@RestController
@RequestMapping
public class UsersController {

    private final UsersRepository usersRepository;

    @Autowired
    public UsersController(UsersRepository usersRepository) {
        this.usersRepository = usersRepository;
    }

    @GetMapping("/print-user")
    @ResponseStatus(HttpStatus.OK)
    @Transactional (isolation = Isolation.READ_COMMITTED)
    public void printName() throws InterruptedException {
        User user1 = usersRepository.findById("jeremy"); 
        System.out.println(user1.getFirstName());
        
        // allow changing user's name from another 
        // session by calling /update-user endpoint
        Thread.sleep(5000);
        
        User user2 = usersRepository.findById("jeremy");
        System.out.println(user2.getFirstName());
    }


    @GetMapping("/update-user")
    @ResponseStatus(HttpStatus.OK)
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public User changeName() {
        User user = usersRepository.findById("jeremy"); 
        user.setFirstName("Bob");
        return user;
    }
    
}

Answer 1

There are two issues with your code.

You are performing usersRepository.findById("jeremy"); twice in the same transaction, chances are your second read is retrieving the record from the Cache. You need to refresh the cache when you read the record for the second time. I have updated code which uses entityManager, please check how it can be done using the JpaRepository

User user1 = usersRepository.findById("jeremy"); 
Thread.sleep(5000);
entityManager.refresh(user1);    
User user2 = usersRepository.findById("jeremy");

Here are the logs from my test case, please check SQL queries:

• The first read operation is completed. Thread is waiting for the timeout.

Hibernate: select person0_.id as id1_0_0_, person0_.city as city2_0_0_, person0_.name as name3_0_0_ from person person0_ where person0_.id=?

• Triggered update to Bob, it selects and then updates the record.

Hibernate: select person0_.id as id1_0_0_, person0_.city as city2_0_0_, person0_.name as name3_0_0_ from person person0_ where person0_.id=?

Hibernate: update person set city=?, name=? where id=?

• Now thread wakes up from Sleep and triggers the second read. I could not see any DB query triggered i.e the second read is coming from the cache.

The second possible issue is with /update-user endpoint handler logic. You are changing the name of the user but not persisting it back, merely calling the setter method won't update the database. Hence when other endpoint's Thread wakes up it prints Jeremy.

Thus you need to call userRepository.saveAndFlush(user) after changing the name.

@GetMapping("/update-user")
@ResponseStatus(HttpStatus.OK)
@Transactional(isolation = Isolation.READ_COMMITTED)
public User changeName() {
    User user = usersRepository.findById("jeremy"); 
    user.setFirstName("Bob");
    userRepository.saveAndFlush(user); // call saveAndFlush
    return user;
}

Also, you need to check whether the database supports the required isolation level. You can refer H2 Transaction Isolation Levels