EclipseLink does not support read-only database connections [SPR-7891] #12547
We are using Spring and EclipseLink to establish two separated connection pools to replicated MySQL systems.
One connection pool contains "write" connections and should interact with the MySQL master only. The other connection pool contains "read" connections addressing the MySQL slaves.
This driver decides whether to address the MySQL master or slave host(s) based on the connection's "readOnly" attribute. Connections having "readOnly=true" will be executed on any of the configured slaves while "readOnly=false" will address the MySQL master system.
We use the Transactional annotation in order to control whether our methods should make use of read or write connections, e.g.:
Unfortunately the annotation's "readOnly" attribute is never transformed into read-only database connections in our environment.
Calling the connections's setReadOnly method would fix the issue and enable the MySQL replication driver to address the correct DBMS:
Could this be a Spring bug or are we missing anything in our application configuration?
7 votes, 8 watchers
The text was updated successfully, but these errors were encountered:
Juergen Hoeller commented
The problem here is that getJdbcConnection is being called for exposure of a JDBC Connection to JDBC-based access code (e.g. when using JdbcTemplate within a JpaTransactionManager transaction), not for initializing the underlying Connection at the beginning of a transaction. Calling setReadOnly wouldn't work reliably there since it'd be called too late (in the middle of a transaction), or even not at all.
The corresponding JpaDialect method for initializing a transaction is beginTransaction which also receives the readOnly flag through its TransactionDefinition argument. Unfortunately I am not aware of EclipseLink API which would allow us to set a readOnly flag for exposure on the JDBC Connection there. We can control early assignment of a Connection but don't get a callback for initializing that Connection...
Florian Feigenbutz commented
Okay, I see the reason for not only adapting getJdbcConnection().
On the other hand it seems to work in my application.
Could this setup be the reason why it works in my application?
Or could it be related to the following comment in org.springframework.orm.jpa.vendor.EclipseLinkJpaDialect.beginTransaction():
Retrieving a readonly session from EclipseLink could be possible using org.eclipse.persistence.internal.jpa.EntityManagerImpl.getReadOnlySession() but I'm not totally sure if this is what you were looking for?
Juergen Hoeller commented
Due to EclipseLink's architecture, applying the read-only setting to an underlying JDBC Connection is quite hard: In particular after the fix for #12409, EclipseLink may not fetch a JDBC Connection at all for read operations which can be satisfied through the shared cache. As a consequence, enforcing a JDBC Connection to call setReadOnly on it may have a net negative effect: It may bypass the shared cache to begin with, unnecessarily going to the database (to a slave then but still unnecessarily).
I'd rather advise to use Spring's
If there is anything else we can do here to improve the story for such a scenario, let me know. I'd also be interested in EclipseLink-specific improvements for read-only transactions at the Session level, i.e. to suppress unnecessary flushes etc.