Using isolation level as READ_UNCOMMITTED or READ_COMMITTED, the same set of rows are returned even on updating one of the rows in another transaction. I checked in the pg_stat_activity logs, query is fired everytime if I am running the same query multiple times. So, there is nothing related to hibernate first level cache.
Here is the code:
@Transactional(isolation = Isolation.READ_COMMITTED)
public void checkTransactional() {
List<Subject> subjects = subjectRepository.findAllByTutorID(2l); // statement 1
List<Subject> subjects1 = subjectRepository.findAllByTutorID(2l); // statement 2
}
Table state before running above code.
id | name | tutorial_id ----+--------------+------------- 7 | Mohan | 2 8 | Sohan | 2
On running application: Step 1: After executing statement 1: result in the subjects has two entities [(7, "Mohan", 2), (8, "Sohan", 2)]
Step 2: Putting debugger before running statement 2: the row with id 8 is updated with name "Tohan" directly via commandline. update subjects set name = 'Tohan' where id = 8;
Table State: id | name | tutorial_id ----+--------------+------------- 7 | Mohan | 2 8 | Tohan | 2
Step 3: Resuming the application, and after executing statement 2: result in the subjects1 is again having the same two entities as in statement 1 [(7, "Mohan", 2), (8, "Sohan", 2)]
It seems, it is always working in repeatable_read isolation. Any thoughts why it is happening?
- The database isolation level is set to read_committed. ** The results are same even on using @Transactional(isolation = Isolation.READ_UNCOMMITTED)
After executing statement 2: result in the subjects1 should have been [(7, "Mohan", 2), (8, "Tohan", 2)]
