JPA JQL query after bulk UPDATE sees stale values

Question

I have an EclipseLink JPA demonstrative application, which issues a JPQL SELECT statement after executing a JPQL UPDATE statement. The SELECT statement "sees" stale data as follows:

• if no hints are provided, result data are stale
• if .setHint("javax.persistence.cache.storeMode", "REFRESH") is used, updated data are retrieved
• if .setHint("javax.persistence.cache.retrieveMode", "BYPASS") stale data are retrieved
• if I execute em.clear() before the query, the query retrieves the updated value (but this is obvious, I would like to know what happens when the persistence context is NOT cleared). However this points towards a level 1 cache problem.

I cannot understand where the JPQL SELECT gets the stale data from. They are not in the shared cache apparently (confirmed both by use of BYPASS hint and contains() method of the cache interface).

Experiments I did:

• adding where 1=1 to the stale-retrieving query to check if some query code caching occurs (no change)
• removing the hint from the second SELECT query, to check if there is a time problem with a possibly delayed database commit (the 2nd query also sees stale data).

Any idea?

    package examples.client;
    
    import examples.model.Employee;
    import java.util.List;
    
    import javax.persistence.EntityManager;
    import javax.persistence.EntityManagerFactory;
    import javax.persistence.Persistence;
    
    public class EmployeeUPDATEModification {
    
        public static void main(String[] args) {
            EntityManagerFactory emf = Persistence.createEntityManagerFactory("EmployeeService");
            EntityManager em = emf.createEntityManager();
    
            System.out.println("***INITIAL SALARY VALUES\n");
            List<Employee> initial = em.createQuery("SELECT e FROM Employee e", Employee.class).getResultList();
            for (Employee e : initial) {
                System.out.println(e.getSalary());
            }
            System.out.println("***TESTING BULK UPDATE\n");
            em.getTransaction().begin();
            em.createQuery("UPDATE Employee e SET e.salary = e.salary*2").executeUpdate();
            em.getTransaction().commit();
    
            System.out.println("***SALARY VALUES AFTER BULK UPDATE FROM INITIAL LIST\n");
            for (Employee e : initial) {
                System.out.println(e.getSalary());
            }
    
            System.out.println("\n***PRINTING THE SALARY FROM A QUERY WITHOUT HINT \n");
            List<Employee> result = em.createQuery("SELECT e FROM Employee e", Employee.class).getResultList();
            for (Employee e : result) {
                System.out.println(e.getSalary());
            }
    
            System.out.println("\n***CHECKING THE SHARED CACHE\n");
            for (Employee e : result) {
                System.out.println(emf.getCache().contains(Employee.class, e.getId()) ? e + " is in shared chache"
                        : e + " is NOT in shared cache");
            }
    
            System.out.println("\n***PRINTING THE SALARY FROM A QUERY WITH HINT \n");
            List<Employee> result3 = em.createQuery("SELECT e FROM Employee e", Employee.class)
                    .setHint("javax.persistence.cache.storeMode", "REFRESH").getResultList();
            for (Employee e : result3) {
                System.out.println(e.getSalary());
            }
    
            // close the EM and EMF when done
            em.close();
            emf.close();
    
        }
    }

Console

***INITIAL SALARY VALUES

[EL Fine]: sql: 2020-10-20 17:21:25.213--ServerSession(2092769598)--Connection(110651474)--Thread(Thread[main,5,main])--SELECT ID, NAME, SALARY FROM EMPLOYEE

100
200
300

***TESTING BULK UPDATE

[EL Fine]: sql: 2020-10-20 17:21:25.241--ClientSession(706665172)--Connection(110651474)--Thread(Thread[main,5,main])--UPDATE EMPLOYEE SET SALARY = (SALARY * ?)
    bind => [2]

***SALARY VALUES AFTER BULK UPDATE FROM INITIAL LIST

100
200
300

***PRINTING THE SALARY FROM A QUERY WITHOUT HINT 

[EL Fine]: sql: 2020-10-20 17:21:25.256--ServerSession(2092769598)--Connection(110651474)--Thread(Thread[main,5,main])--SELECT ID, NAME, SALARY FROM EMPLOYEE

100
200
300

***CHECKING THE SHARED CACHE

Employee id: 1 name: Piero salary: 100 is NOT in shared cache

Employee id: 2 name: Aldo salary: 200 is NOT in shared cache

Employee id: 3 name: Mario salary: 300 is NOT in shared cache

***PRINTING THE SALARY FROM A QUERY WITH HINT 

[EL Fine]: sql: 2020-10-20 17:21:25.271--ServerSession(2092769598)--Connection(110651474)--Thread(Thread[main,5,main])--SELECT ID, NAME, SALARY FROM EMPLOYEE

200
400
600

Console with finest logging level

***PRINTING THE SALARY FROM A QUERY WITHOUT HINT 

[EL Finest]: query: 2020-10-20 18:14:08.097--UnitOfWork(955611965)--Thread(Thread[main,5,main])--Execute query ReadAllQuery(referenceClass=Employee sql="SELECT ID, NAME, SALARY FROM EMPLOYEE")

[EL Finest]: connection: 2020-10-20 18:14:08.097--ServerSession(1006485584)--Connection(1482246673)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].

[EL Fine]: sql: 2020-10-20 18:14:08.097--ServerSession(1006485584)--Connection(1482246673)--Thread(Thread[main,5,main])--SELECT ID, NAME, SALARY FROM EMPLOYEE

[EL Finest]: connection: 2020-10-20 18:14:08.099--ServerSession(1006485584)--Connection(1482246673)--Thread(Thread[main,5,main])--Connection released to connection pool [default].

400
800
1200

PRINTING THE SALARY FROM A QUERY WITH HINT 

***

[EL Finest]: query: 2020-10-20 18:14:08.119--UnitOfWork(955611965)--Thread(Thread[main,5,main])--Execute query ReadAllQuery(referenceClass=Employee sql="SELECT ID, NAME, SALARY FROM EMPLOYEE")

[EL Finest]: connection: 2020-10-20 18:14:08.119--ServerSession(1006485584)--Connection(1482246673)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].

[EL Fine]: sql: 2020-10-20 18:14:08.119--ServerSession(1006485584)--Connection(1482246673)--Thread(Thread[main,5,main])--SELECT ID, NAME, SALARY FROM EMPLOYEE

[EL Finest]: connection: 2020-10-20 18:14:08.121--ServerSession(1006485584)--Connection(1482246673)--Thread(Thread[main,5,main])--Connection released to connection pool [default].

[EL Finest]: transaction: 2020-10-20 18:14:08.122--UnitOfWork(955611965)--Thread(Thread[main,5,main])--Merge clone Employee id: 1 name: Piero salary: 800 

[EL Finest]: transaction: 2020-10-20 18:14:08.122--UnitOfWork(955611965)--Thread(Thread[main,5,main])--Merge clone Employee id: 2 name: Aldo salary: 1600 

[EL Finest]: transaction: 2020-10-20 18:14:08.123--UnitOfWork(955611965)--Thread(Thread[main,5,main])--Merge clone Employee id: 3 name: Mario salary: 2400 

800
1600
2400

Answer 1

The answer you posted mentioned your issue - you have an entity read from the local context, and have made it stale with updates outside of the model object (your bulk update query). Every time you read from that EntityManager, you get the same stale employee instance data back - JPA requires it to maintain object identity, and since you could have changes in there that are just uncommitted, can't just wipe them out. So it will perform a full list operation, but when it sees an Emp id it already has cached/managed, just returns that instance as is.

The JPA spec states that bulk updates and deletes change things in ways that may not be visible in the context: section 4.10: "The persistence context is not synchronized with the result of the bulk update or delete. Caution should be used when executing bulk update or delete operations because they may result in inconsistencies between the database and the entities in the active persistence context. In general, bulk update and delete operations should only be performed within a transaction in a new persistence context or before fetching or accessing entities whose state might be affected by such operations."

You can resolve this by either forcing the refresh on your later queries, clearing the entity manager, or obtaining a new one after you commit your transaction. All reads then will use the data from the database.