EclipseLink does not support read-only database connections [SPR-7891]

[spring-projects-issues]

Florian Feigenbutz opened SPR-7891 and commented

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.
These pools are established using EclipseLink's connection pool implementation and the MySQL replication driver.

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.:
@Transactional(readOnly = true, propagation = Propagation.REQUIRED)

Unfortunately the annotation's "readOnly" attribute is never transformed into read-only database connections in our environment.
It seems as if the EclipseLinkJpaDialect is missing to set this attribute in the getJdbcConnection method:

@Override
public ConnectionHandle getJdbcConnection(EntityManager em, boolean readOnly)
    throws PersistenceException, SQLException {
    AbstractSession session = (AbstractSession) getSession(em);
    // The connection was already acquired eagerly in beginTransaction,
    // unless lazyDatabaseTransaction was set to true.
    Connection con = session.getAccessor().getConnection();
    return (con != null ? new SimpleConnectionHandle(con) : null);
}

Calling the connections's setReadOnly method would fix the issue and enable the MySQL replication driver to address the correct DBMS:

...
    Connection con = session.getAccessor().getConnection();
    con.setReadOnly(readOnly);
    return (con != null ? new SimpleConnectionHandle(con) : null);
}

Could this be a Spring bug or are we missing anything in our application configuration?
Is there any reason why the current getJdbcConnection implementation ignores the present "readOnly" argument?

---

Affects: 3.0.5

Issue Links:

• Spring's JDBC connection access disables shared cache in EclipseLink [SPR-7753] #12409 Spring's JDBC connection access disables shared cache in EclipseLink

7 votes, 8 watchers

[spring-projects-issues]

Florian Feigenbutz commented

Oh, I forgot to set a component when posting the issue and now I'm no more allowed to modify it.

Guess the correct component would be "SpringCORE" or maybe "SpringTX"?

[spring-projects-issues]

Oliver Drotbohm commented

Assigned to data access and transaction component.

[spring-projects-issues]

Florian Feigenbutz commented

Any ideas for this issue?

[spring-projects-issues]

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...

Juergen

[spring-projects-issues]

Florian Feigenbutz commented

Okay, I see the reason for not only adapting getJdbcConnection().

On the other hand it seems to work in my application.
I'm using a LocalContainerEntityManagerFactoryBean which configures EclipseLink to use its internal JDBC connection pool by providing the database credentials using setJpaProperties instead of injecting a DataSource:

<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="jpaProperties">
<props>
...
<prop key="javax.persistence.jdbc.driver">...</prop>
<prop key="javax.persistence.jdbc.url">...</prop>
<prop key="javax.persistence.jdbc.user">...</prop>
<prop key="javax.persistence.jdbc.password">...</prop>
...
<prop key="eclipselink.jdbc.read-connections.initial">...</prop>
<prop key="eclipselink.jdbc.read-connections.min">...</prop>
...
</props>
</property>
...

<!--
SETTING THE DATASOURCE INSTRUCTS ECLIPSELINK TO DISABLE ITS INTERNAL
CONNECTION POOLS BECAUSE IT EXPECTS THE EXTERNAL DATASOURCE TO BE POOLED
<property name="dataSource" ref="dataSource"/>
-->
</bean>

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():
...
// This is the magic bit. As with the existing Spring TopLink integration,
// begin an early transaction to force EclipseLink to get a JDBC Connection
// so that Spring can manage transactions with JDBC as well as EclipseLink.
...
Does this mean that EclipseLink will always use JDBC connections provided by the Spring Transaction Manager/JPA dialect? Or will use its own JDBC Connections anyway?

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?

Florian

[spring-projects-issues]

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 IsolationLevelDataSourceAdapter for your DataSource setup, which not only applies a transaction-specific isolation level but also a transaction-specific read-only setting to the JDBC Connection. If your EntityManagerFactory setup points to that DataSource adapter, you'll implicitly get the read-only setting applied whenever EclipseLink decides to fetch a JDBC Connection.

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.

Juergen