Investigating MyBatis SqlSession.clearCache() Ineffectiveness and Transaction Isolation Level Solution

The author noticed that calling org.apache.ibatis.session.SqlSession.clearCache() did not appear to clear the first‑level cache in MyBatis, as repeated queries within the same session still returned old values.

Initial attempts included searching the web and consulting the official MyBatis documentation, which indicated that clearCache() should clear the local cache when an update, commit, rollback, or close occurs.

To gather more information, the logging level was increased to DEBUG and then to TRACE using a Logback configuration:

<configuration>
  <contextName>mybatis</contextName>
  <appender name="stdout" class="ch.qos.logback.core.ConsoleAppender">
    <encoder>
      <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger %msg%n</pattern>
    </encoder>
  </appender>
  <appender name="file" class="ch.qos.logback.core.FileAppender">
    <file>mybatis.log</file>
    <encoder>UTF-8</encoder>
    <pattern>%d{YYYY-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
  </appender>
  <root level="TRACE">
    <appender-ref ref="stdout"/>
    <appender-ref ref="file"/>
  </root>
</configuration>

No useful cache‑clearing logs appeared, so the source code of clearCache() was examined. The method ultimately calls PerpetualCache#cache.clear(), which is a simple HashMap.clear() operation.

The author then inspected the selectOne() flow, noting that it delegates to selectList(), which builds a MappedStatement, creates a BoundSql, generates a CacheKey, and finally reaches BaseExecutor where the local cache is consulted.

Experiments showed that without an explicit sqlSession.clearCache() call, the second query retrieved a result from the local cache; with the call, the cache was empty, confirming that clearCache() works correctly.

Further tracing revealed that after the cache miss, MyBatis executes queryFromDatabase, which eventually invokes Statement.execute() and processes the ResultSet. The returned data was still the stale value, indicating the problem lay outside MyBatis.

Investigation of the MySQL transaction isolation level showed it was set to REPEATABLE‑READ :

mysql> SELECT @global.tx_isolation;
+-----------------------+
| @global.tx_isolation |
+-----------------------+
| REPEATABLE-READ      |
+-----------------------+

Because REPEATABLE‑READ holds a snapshot of data for the duration of the transaction, the second query saw the old value even after the MyBatis cache was cleared.

The solution is to change the transaction isolation level to READ COMMITTED :

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec);

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec);

After adjusting the isolation level, the stale‑data issue disappears, confirming that the MyBatis cache was functioning correctly and the root cause was the database transaction isolation setting.