Spring Data JPA : A transaction with isolation READ_COMMITTED does not see data committed in another transaction

Question

So, I have a very simple Spring Boot-based web application.

The database has one table, user, with columns id and username and one record: (1, 'Joe').

I also have the following classes:

User - an entity mapped to the table user

UserRepository - a Spring Data JPA repository for it

UserService + DefaultUserService - a service layer with CRUD methods

UserController - a controller with two methods: get and update

Application - the main class (with @EnableTransactionManagement annotation on it)

So, what I'm trying to do is to test transaction isolation level READ_COMMITTED. I'm sending two simultaneous requests:

To /update, which updates a user, setting its name to Jack, then puts a current thread to sleep for 5s and then commits the transaction.
To /get, which repeatedly reads the same user for 10 times making a nap for 1s after every attempt.

The issue is that even after the transaction for (1) has been committed, (2) keeps returning an old value - Joe. If I try to send another request to /get after that, it returns Jack, as expected, so the issue happens only if transaction (2) has started before transaction (1) has committed a change to the DB.

You may find some code for reference below.

Service:

@Service
public class DefaultUserService implements UserService {

    ... //fields & constructor

    @Override
    @SneakyThrows
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public User read(Long id) {
        User user = userRepository.findById(id).get();
        Thread.sleep(1000);
        return user;
    }

    @Override
    @SneakyThrows
    @Transactional
    public User update(Long id, User update) {
        log.info("Entering update method for user {}", id);
        User user = read(id);

        user.setUsername("Jack");
        user = userRepository.save(user);

        log.info("User {} updated, falling asleep for 5s", id);
        Thread.sleep(5000);

        return user;
    }
}

Controller:

@RestController
public class UserController {

    ... //fields & constructor

    @RequestMapping("/update")
    public User update() {
        User user = userService.update(1L, new User("Jack"));
        log.info("UPDATED: {}", user);
        return user;
    }

    @RequestMapping("/get")
    public User get() {
        User user = userService.read(1L);
        for (int i = 0; i < 10; i++) {
            log.info("READ: {}", user);
            user = userService.read(1L);
        }
        return user;
    }
}

Log output:

04:37:52.915 [io-8781-exec-10] READ: ID: 1 :: Joe
04:37:53.151 [nio-8781-exec-1] Entering update method for user 1
04:37:53.919 [io-8781-exec-10] READ: ID: 1 :: Joe
04:37:54.152 [nio-8781-exec-1] User 1 updated, falling asleep for 5s
04:37:54.922 [io-8781-exec-10] READ: ID: 1 :: Joe
04:37:55.926 [io-8781-exec-10] READ: ID: 1 :: Joe
04:37:56.932 [io-8781-exec-10] READ: ID: 1 :: Joe
04:37:57.937 [io-8781-exec-10] READ: ID: 1 :: Joe
04:37:58.943 [io-8781-exec-10] READ: ID: 1 :: Joe
04:37:59.222 [nio-8781-exec-1] UPDATED: ID: 1 :: Jack
04:37:59.947 [io-8781-exec-10] READ: ID: 1 :: Joe
04:38:00.950 [io-8781-exec-10] READ: ID: 1 :: Joe
04:38:01.956 [io-8781-exec-10] READ: ID: 1 :: Joe

Responses returned by controllers are also different. For /update it's:

{"id":1,"username":"Jack"}

While for /get:

{"id":1,"username":"Joe"}

I'm using MySQL 5.7.18 and Spring Boot 2.1.0.

Any ideas about what I might be doing wrong/missing? Thanks in advance.

Good question. I am wondering if it possibly has something to do with OpenEntityMangerInViewFilter being enabled by default in Spring Boot. Can you set the following boot property and retry spring.jpa.open-in-view=false. See further stackoverflow.com/q/30549489/1356423 and static.javadoc.io/org.springframework/spring-orm/4.0.1.RELEASE/… — Alan Hay, Nov 29 '18 at 08:58
From your comments below you are making the assumption that your problems lie at the database level rather than the JPA level. Enabling SQL logging would be a good start in determining where the problem lies. https://stackoverflow.com/a/19299769/1356423 — Alan Hay, Nov 29 '18 at 10:10
Many thanks for the idea with disabling open-in-view, I've been suspecting that the things start to go wrong from controller. I was able to make REPEATABLE_READ working as expected with it. However, now I've got another issue: I can't force a reading transaction to ensure repeatable reads. So, when I set either REPEATABLE_READ or even SERIALIZABLE isolation for it, it keeps returning "Joe" only until the second transaction is committed, and then it starts returning "Jack" (even though I didn't leave the scope of that isolated transaction!) — Andrei Rykhalski, Nov 29 '18 at 10:34
@AlanHay First of all, thanks for that wonderful logging library, it's great. It showed me that a new transaction is opened everytime `findById` method is called and committed right after it returns a value. I know that `CrudRepository` methods are transactional by default, however no propagation is defined there, so it should use a default one - `REQUIRED`, I guess? Anyway, even after annotating repository methods with `@Transactional(propagation = Propagation.SUPPORTS)` (to be sure that a new trx is not created), I still wasn't able to get a valid repeatable read scenario — Andrei Rykhalski, Nov 29 '18 at 11:16

score 1 · Answer 1 · answered Nov 29 '18 at 04:55

1

Your code works correctly with Oracle but with MySQL its bit different. Read here about isolation levels in MySql

https://blog.pythian.com/understanding-mysql-isolation-levels-repeatable-read/

I believe if you change isolation level of your method to Read committed it should work as by default MySql has Repeatable-read which is handled bit differntly.

   @Transactional(Isolation.READ_COMMITTED)
    public User update(Long id, User update)

answered Nov 29 '18 at 04:55

Amit Naik

762
1
4
14

Thanks for sharing the article. It made me thinking of migrating to another DBMS with a bit more predictable isolation mechanism. Also, I tried to apply isolation rule to update method instead of get but with no effect, the result was the same – Andrei Rykhalski Nov 29 '18 at 09:31
i mean you should keep `Isolation.READ_COMMITTED` for both methods. It should work then – Amit Naik Nov 29 '18 at 09:38
Unfortunately, it doesn't – Andrei Rykhalski Nov 29 '18 at 10:26

score -1 · Answer 2 · answered Jun 19 '20 at 07:00

-1

Set:

@Override
@SneakyThrows
@Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.READ_COMMITTED)
public User read(Long id) {
    User user = userRepository.findById(id).get();
    Thread.sleep(1000);
    return user;
}

answered Jun 19 '20 at 07:00

Pedro Henrique

24
4

What are you trying to solve? Please add further information, instead of throwing a bunch of happy code lines in here. – Semo Jun 19 '20 at 07:22

Spring Data JPA : A transaction with isolation READ_COMMITTED does not see data committed in another transaction

2 Answers2