Document LazyConnectionDataSourceProxy for IsolationLevelDataSourceRouter usage with DataSourceTransactionManager

[spring-projects-issues]

springrain opened SPR-15121 and commented

IsolationLevelDataSourceRouter.determineCurrentLookupKey returns the result was null, since AbstractPlatformTransactionManager.getTransaction (TransactionDefinition definition) before calling doBegin(Object transaction, TransactionDefinition definition), setCurrentTransactionIsolationLevel(Integer isolationLevel) is not set, but get a connection, it's bug.

---

Affects: 4.3.4

Referenced from: pull request #1291

[spring-projects-issues]

Juergen Hoeller commented

IsolationLevelDataSourceRouter is primarily intended for JTA scenarios where Connection lookups only happen after transaction begin. With DataSourceTransactionManager, the isolation level is applied to the retrieved Connection directly, so it is not really designed to interact with such DataSource-specific isolation setup.

Are you trying to optimize the switching overhead there? Why are you using IsolationLevelDataSourceRouter to begin with?

[spring-projects-issues]

springrain commented

I used to read and write the database separation,I don't have to use JTA

<bean id="dataSource" class="org.springframework.jdbc.datasource.lookup.IsolationLevelDataSourceRouter">
<property name="targetDataSources">
<map>
<entry key="ISOLATION_READ_UNCOMMITTED" value-ref="dataSourceWrite"/>
</map>
</property>
<property name="defaultTargetDataSource" ref="dataSourceRead"/>
</bean>

[andrei-ivanov]

I've hit a similar issue, where I'm trying to use the readOnly status of the transaction to determine which dataSource to use, just to see that AbstractPlatformTransactionManager#prepareSynchronization is invoked just after doBegin, which obtains the connection (using a JpaTransactionManager) 😥

[andrei-ivanov]

is there any chance that moving the prepareSynchronization call before doBegin might work?

[andrei-ivanov]

@jhoeller ?

[lukago]

I have similar problem to @andrei-ivanov, any updates on this? @jhoeller

[jhoeller]

Returning to DataSource setup issues for a bit of a JDBC theme in 6.1.2, including new sharding support and several refinements to Spring's DataSource adapters : see https://github.com/spring-projects/spring-framework/issues?q=is%3Aopen+milestone%3A6.1.2+label%3A%22in%3A+data%22+

I am inclined to address the isolation level scenario here through an extension of SmartDataSource with a dedicated getConnection(int isolationLevel, boolean readOnly) method that DataSourceTransactionManager can call if the target DataSource implements it. IsolationLevelDataSourceRouter and a similar routing DataSource based on @Transactional(readOnly=...) can then take this into account directly when specified. This would allow us to preserve the semantics of our existing transaction synchronization arrangement which we cannot easily bend towards earlier exposure.

As an alternative, LazyConnectionDataSourceProxy could be used to approach JTA-like late retrieval semantics which should make the existing transaction synchronization arrangement work with IsolationLevelDataSourceRouter as the target behind a LazyConnectionDataSourceProxy, since the actual target Connection will be retrieved after transaction begin then.

[jhoeller]

Experimenting with a few scenarios here, such an extension to SmartDataSource is rather involved in more complex setups, e.g. behind a JPA provider. In comparison to that, LazyConnectionDataSourceProxy is actually the simplest solution out: configuring both your transaction manager and your data access setup (JdbcTemplate or JPA setup) with a LazyConnectionDataSourceProxy conveniently allows for IsolationLevelDataSourceRouter to pick up a late-bound current isolation level within the transaction. And this comes with the extra benefit that connection contention is minimized and even avoided completely (with no connection ever fetched) for "empty" transactions which is quite common with JPA queries that can be answered from a cache.

As a consequence, I am going to turn this ticket into a documentation ticket for LazyConnectionDataSourceProxy.