Avoid stale data from DB when using JPA?

Question

I am facing a problem similar to the one described in Invalidating JPA EntityManager session :

Problem: Getting stale data

We are running JPQL queries on an SQL database that is also concurrently changed by a different application. We are using JSF+Spring+EclipseLink running under Tomcat.

The class doing the JPQL queries is a singleton Spring bean, and uses an injected EntityManager:

@Component
public class DataRepository{
    @PersistenceContext
    private EntityManager entityManager;
    public List<MyDTO> getStuff(long id) {
        String jpqlQuery ="SELECT new MyDTO([...])";
        TypedQuery<MyDTO> query = entityManager.createQuery(jpqlQuery,MyDTO.class);
        return query.getResultList();
    }
[...]

(code paraphrased).

The problem is that this code does not see changes performed directly on the database. These changes only become visible if the Tomcat instance is restarted.

What we have tried

We assume that this behaviour is caused by the first level cache associated to the EntityManager, as described in the linked question. We found two solutions:

call entityManager.clear() before invoking createQuery (this is suggested in the linked question)
inject an EntityManagerFactor (using @PersistenceUnit), then create and close a new EntityManager for each query

Both solutions do what we want - we get fresh data.

Questions:

Are these two solutions correct? Which one is better?
In particular, can we safely call entityManager.clear() on the injected EntityManager, or will this somehow impact other code that also uses an injected EntityManager (in the same or in a different class)?
Is there a different, better approach? Can we somehow declare that we want to refresh the cache, or that we want a fresh EntityManager?

I assume this must be a fairly common problem (as it occurs whenever multiple apps share a database), so I thought there must be a simple solution...

I think it's not very common to share a database with multiple applications? It sounds like you'll be punished heavily with constantly reloading your EntityManager. Can't you serve the required data through a service instead? Or use some shared cache between the applications? — vertti, Jan 07 '13 at 16:00
@vertti: Good point. I did not realize JPA has such problems with using a shared database. Of course using a service or similar is possible, but it would mean a major change, and is unlikely to happen. — sleske, Jan 08 '13 at 00:59

score 6 · Answer 1 · answered Jan 22 '13 at 15:33

It looks like we have resolved the problem.

There were actually two problems:

While debugging the problem, in some cases we did not use a fresh EntityManager for every query. When using and re-using the same EntityManager, entities will apparently never be refreshed once they have been retrieved (unless refreshed explicitly using EntityManager.refresh()). This is apparently because once an entity has been loaded, it is stored in the persistence context (a.k.a. first-level cache) of the EntityManager. Doing this is necessary because the JPA spec requires that subsequent queries for the same entity return the same object instance. So in other words: as long as you use the same EntityManager, you will get stale data unless you refresh explicitly.
When we did use a fresh EntityManager for each query, things usually worked. However, we ran into a bug in EclipseLink, where stale data is returned for certain JPQL queries (involving constructor expressions and JOIN FETCH).

Short version

If you want to always get fresh data from a database that is modified from outside your program, then

use a fresh EntityManager for each query where you want fresh data
disable the second-level cache (<shared-cache-mode>NONE</shared-cache-mode> in persistence.xml)

score 2 · Accepted Answer · answered Jan 07 '13 at 19:36

2

Creating new EntityManagers is correct, the other isn't (see below).
Calling EntityManager#clear() is very dangerous in all but a single-threaded system.

...causing all managed entities to become detached...

Hence, if one thread works with attached entities while "your" thread clears the entity manager you have severe side effects.
Hhm, hard to tell. If the number of entities modified outside your application is low I'd work directly with the datasource and a JdbcTemplate for the respective operations.

answered Jan 07 '13 at 19:36

Marcel Stör

22,695
19
92
198

1

EntityManagers are not thread safe, so you should not be working on the same EntityManager in two threads anyway. Each thread should have its own EntityManager injected, as they represent different transactional contexts. – Chris Jan 07 '13 at 19:54
True. Just looked at the OP's code snippet to make sure he's indeed using it that way. – Marcel Stör Jan 07 '13 at 19:58
@Chris: Yes, true. However, we use Spring to inject the EntityManager, and Spring gives us "a shared, thread-safe proxy for the actual transactional EntityManager" ( http://static.springsource.org/spring/docs/3.2.x/spring-framework-reference/html/orm.html#orm-jpa-straight ). So thread-safety is not the problem. – sleske Jan 08 '13 at 01:03
2

The link implies you are in a transaction otherwise the proxy uses a new EM per operation. If this is correct, you might perform reads outside the transaction for better performance. Also note EclipseLink has a shared 2nd level cache; verify you are using isolated cache settings – Chris Jan 08 '13 at 12:23
@Chris: Actually no, we are not in a transaction (I believe - the query method is not annotated @Transactional). Still we sometimes get stale data. I'm still investigating this... – sleske Jan 08 '13 at 17:13

score 1 · Answer 3 · answered Jan 08 '13 at 14:45

1

To disable the shared cache in EclipseLink see,

http://wiki.eclipse.org/EclipseLink/FAQ/How_to_disable_the_shared_cache%3F

answered Jan 08 '13 at 14:45

James

17,965
11
91
146

We have already done that. However, the problem seems to be the first-level cache - as explained, everything's fine if we always use a fresh EntityManager, so shared cache is probably not the problem. – sleske Jan 08 '13 at 17:14

score 0 · Answer 4 · answered Apr 11 '17 at 11:43

0

This will do the magic entityManager.getEntityManagerFactory().getCache().evict(MyDTO.class);

answered Apr 11 '17 at 11:43

Christian Oliver

112
6

Avoid stale data from DB when using JPA?

4 Answers4