- Aurora cluster with
Master with READ_COMMITTED isolation level
Read replica (uses REPEATABLE_READ isolation by default in Aurora)
- We set auto-commit to false on the Hikari connection pool
- We use Spring with @Transactional(readOnly=true)
When we execute the getCount method multiple times we always get the same count, even though items are added to the some_table between invocations of getCount.
This is caused by not performing a commit after the invocation of getCount.
In the MariaDbConnection.commit method the protocol.inTransaction() returns false.
So the st.execute("COMMIT") is never actually invoked, although Spring tries to commit the transaction.
Since Aurora read instances are always REPEATABLE_READ, and we use auto-commit false, the transaction will be kept alive without a COMMIT, which explains the stale date.
When we use driver version 2.3.0 we don't see this issue since protocol.inTransaction() correctly indicates the transaction is active, and a commit occurs at the end of the getCount method.
Starting from version 2.4.0 we see this erroneous behaviour.
We have currently reverted back to version 2.3.0.