MyBatis: how to bypass a local cache and directly hit the DB on specific select

Question

I use MyBatis 3.1.
I have two use cases when I need to bypass MyBatis local cache and directly hit the DB.
Since MyBatis configuration file only have global settings, it is not applicable to my case, because I need it as an exception, not as a default. Attributes of MyBatis <select> XML statement do not seem to include this option.

Use case 1: 'select sysdate from dual'.
MyBatis caching causes this one to always return the same value within a MyBatis session. This causes an issue in my integration test, when I try to replicate a situation of an outdated entry.
My workaround was just to use a plain JDBC call.

Use case 2: 'select' from one thread does not always see the value written by another thread.
Thread 1:

SomeObject stored = dao.insertSomeObject(obj);
runInAnotherThread(stored.getId());
//complete and commit

Thread 2:

//'id' received as an argument provided to 'runInAnotherThread(...)'
SomeObject stored = dao.findById(id);
int count = 0;
while(stored == null && count < 300) {
    ++count;
    Thread.sleep(1000);
    stored = dao.findById(id);
}
if (stored == null) {
    throw new MyException("There is no SomeObject with id="+id);
}

I occasionally receive MyException errors on a server, but can't reproduce on my local machine. In all cases the object is always in the DB. So I guess the error depends on whether the stored object was in MyBatis local cache at the first time, and waiting for 5 minutes does not help, since it never checks the actual DB.

So my question is how to solve the above use cases within MyBatis without falling back to the plain JDBC?
Being able just to somehow signal MyBatis not to use a cached value in a specific call (the best) or in all calls to a specific query would be the preferred option, but I will consider any workaround as well.

Tosha

Answer 1

I don't know a way to bypass local cache but there are two options how to achieve what you need.

The first option is to set flushCache="true" on select. This will clear the cache after statement execution so next query will hit database.

    <select id="getCurrentDate" resultType="date" flushCache="true">
        SELECT SYSDATE FROM DUAL
    </select>  

Another option is to use STATEMENT level local cache. By default local cache is used during SESSION (which is typically translates to transaction). This is specified by localCacheScope option and is set per session factory. So this will affect all queries using this mybatis session factory.

Woollyheaded

The 'flushCache="true"' works indeed, thanks, credited. The second option, as you just noticed, is a global one, so I would avoid it. – Tosha

Answer 2

Let me summarize.
The solution from the previous answer, 'flushCache="true"' option on the query, works and solves both use cases. It will flush cache after every such 'select', so the next 'select' statement will hit the DB. Although it works after the 'select' statement is executed, it's OK since the cache is empty anyway before the first 'select'.

Another solution is to start a new session. I use Spring, so it's enough to mark a method with @Transactional(propagation = Propagation.REQUIRES_NEW). Since MyBatis session is tied to Spring transaction, this will cause to create another MyBatis session with fresh cache every time the method is called.

By some reason, the MyBatis option 'useCache="false"' in the query does not work.

Tosha

Answer 3

The following Options annotation can be used:

@Options(useCache=false, flushCache=FlushCachePolicy.TRUE)

Platter

Answer 4

Apart from answers by Roman and Alexander there is one more solution for this:

 Configuration configuration = MyBatisUtil.getSqlSessionFactory().getConfiguration(); 
            Collection<Cache> caches = configuration.getCaches(); 

           //If you have multiple caches and want a particular to get deleted.           
          // Cache cache = configuration.getCache("PPL"); // namespace of particular XML
            for (Cache cache : caches) { 
                Lock w = cache.getReadWriteLock().writeLock(); 
                w.lock(); 
                try { 
                    cache.clear(); 
                } finally { 
                    w.unlock(); 
                } 
            } 

Burgeon