Root Cause Analysis of Data Lookup Failure Due to Unrefreshed Snapshot in MariaDB Aurora Mode

An intermittent error occurred where member data INSERTed into the Master DB could be retrieved from the Slave DB, but not from other Sessions on the Master DB. Initially, Replica Lag was suspected, but debugging revealed that the query was being executed on the Master DB rather than the Slave, and the actual cause was COMMIT not being executed.

In InnoDB's MVCC (Multi-Version Concurrency Control) structure, when the REPEATABLE READ isolation level is applied, all reads within a transaction reference the Snapshot from the point of the first read. In a HikariCP environment with autocommit=false, query methods defined by developers (such as findByMemberNo) do not automatically have @Transactional applied, so no COMMIT occurred. As a result, it was normal behavior for a ROLLBACK to execute upon Connection closure, refreshing the Snapshot.

However, the project had jpa.open-in-view=true configured, so the Connection was maintained throughout the entire API request. When a subordinate method had @Transactional(readOnly=true) applied and was routed to the Slave DB, a COMMIT occurred upon that method's completion, refreshing the Snapshot for the Slave Session. In contrast, the Master Session's outerMethod had no @Transactional, so no COMMIT occurred, and the Snapshot remained unrefreshed.

As a result, the Slave DB retrieved the latest data, while the Master DB referenced the previous Snapshot and failed to see the new data. To resolve this, changing to the READ COMMITTED isolation level or using Locking Read were considered, but were ruled out due to the risk of Phantom Read and lock contention issues. Ultimately, the solution was to refresh the Snapshot on every request via @Transactional(readOnly=true) settings, or to clarify transaction boundaries by adjusting the open-in-view setting.