Suggestion to support LazyConnectionDataSourceProxy

[ttddyy]

Hi,

I would like to suggest a support for LazyConnectionDataSourceProxy in spring-boot.

Here is my opinion:

Transaction management in spring:
When a transactional method(@Transactional) is called, the configured impl of PlatformTransactionManager kicks in and starts/prepare transaction logic(call AbstractPlatformTransactionManager#doBegin).

In the implementation of starting transaction(e.g: JpaTransactionManager, HibernateTransactionManager), when @Transactional definition has non default isolation level or readonly flag is set to true, it acquires a physical connection and set isolation level and/or readonly flag to the connection in its preparation phase. (call to DataSourceUtils#prepareConnectionForTransaction)
Since this happens before invoking the actual target method, this consumes a connection from pool(underlying DataSource) regardless of the method really needs a connection or not.

It is problematic especially on high traffic application with:

• Many readonly transactional methods (or any non-default transactional methods)
  • This holds connection longer than it needs to do
• High hits for hibernate 2nd level cache
  • The method may not even need a connection

It is better to delay getting the physical connection as much as possible.

LazyConnectionDataSourceProxy in spring solves this problem. When isolation-level, read-only flag, etc methods are called, this proxy internally keeps those values. Physical connection acquisition is delayed until connection is really required.

Currently, spring-boot doesn't have any support for LazyConnectionDataSourceProxy.
My suggestion is to add a configuration of LazyConnectionDataSourceProxy in spring-boot.
Probably, application property with spring.datasource.lazy-connection=true would wrap the datasource with LazyConnectionDataSourceProxy. I also think it is beneficial to turn this on by default.
LazyConnectionDataSourceProxy should be recommended more, in general, IMO.

Thanks,

[rpsandiford]

HI, all.

While it would be great to have this in place, I was able to write some code to wrap an underlying Data Source in a LazyConnectionDataSourceProxy. This code specifically handles Hikari, but could be modified for the other data sources Spring Boot uses, or for some other data source all together if wanted.

It's conditional so that you can control it by a property setting ("my.datasource.lacy-connections"). (We have this in a shared library for our own Spring Boot starters, that we use across multiple applications)

And - it should be fairly easy to yank once Spring Boot provides this functionality natively.

My testing shows it works well, and interacts nicely with the Spring Boot auto-configured transaction handling and JdbcTemplate (along with NamedParameterJdbcTemplate)

/**
 * Creates a Lazy DataSource, wrapped around a Hikari pool if and only if:
 * <p>
 * <ul>
 *     <li>HikariDataSource is on classpath</li>
 *     <li>LazyConnectionDataSourceProxy is on classpath</li>
 *     <li>spring.datasource.type is 'com.zaxxer.hikari.HikariDataSource', OR spring.datasource.type
 *     is not defined.</li>
 *     <li>my.datasource.lazy-connections is true, OR my.datasource.lazy-connections is not defined.</li>
 * </ul>
 * <p>
 *
 * <p>
 * This class is needed since Spring Boot Autoconfigure has no mechanism to wrap the DataSources it knows about
 * inside a Lazy proxy.  If / When Spring Boot provides this functionality natively, this class should 'go away'.
 */
@Configuration
@SuppressWarnings({"PMD", "checkstyle:hideutilityclassconstructor"})
public class JDBCConfig {

    /**
     * Compound condition checker - checks that we have all these conditions:
     * <ul>
     *   <li>Hikari is the data source type, either explicitly, or because spring.datasource.type is not present</li>
     *   <li>my.datasource.lazy-connections is true, or is not present</li>
     *   <li>required classes are in classpath</li>
     * </ul>
     */
    static class LazyConnectionCondition extends AllNestedConditions {
        /**
         * Constructor
         */
        LazyConnectionCondition() {
            super(ConfigurationPhase.PARSE_CONFIGURATION);
        }

        @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource",
                matchIfMissing = true)
        static class HikariCondition {
        }

        @ConditionalOnProperty(name = "my.datasource.lazy-connections", matchIfMissing = true)
        static class LazyCondition {
        }

        @ConditionalOnClass({HikariDataSource.class, LazyConnectionDataSourceProxy.class})
        static class ClassCondition {
        }
    }

    /**
     * Stolen from Spring Boot DataSourceConfiguration.java
     *
     * @param properties DataSourceProperties
     * @param type       Data Source class (e.g. HikariDataSource)
     * @param <T>        Data Source class from type
     * @return Configured DataSource
     */
    @SuppressWarnings("unchecked")
    protected static <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
        return (T) properties.initializeDataSourceBuilder().type(type).build();
    }


    /**
     * Lazy Connection Pool configuration
     */
    @Configuration
    @Conditional(LazyConnectionCondition.class)
    static class LazyDataSource {

        /**
         * Configure Lazy Connection Datasource on top of Hikari Datasource
         *
         * @param properties properties starting with spring.datasource.hikari
         * @return Lazy Connection pool DataSource bean.
         */
        @Bean
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.hikari")
        @ConditionalOnMissingBean(name = "dataSource")
        public DataSource dataSource(DataSourceProperties properties) {
            LOG.debug("******************** configureLazyDatasource ****************");

            // Create hikariDataSource (stolen from DataSourceConfiguration)
            HikariDataSource hikariDataSource = createDataSource(properties, HikariDataSource.class);
            if (StringUtils.hasText(properties.getName())) {
                hikariDataSource.setPoolName(properties.getName());
            }

            // Wrap hikariDataSoource in a LazyConnectionDataSourceProxy
            LazyConnectionDataSourceProxy lazyDataSource = new LazyConnectionDataSourceProxy();
            lazyDataSource.setTargetDataSource(hikariDataSource);
            return lazyDataSource;
        }
    }

}

[nwwerum]

We had the same problem and used @rpsandiford's suggestion above.
One problem with the above is, that any Hikari specific properties (e.g. spring.datasource.hikari.maximum-pool-size) will not be applied because @ConfigurationProperties tries to call matching setters on the Bean (here the LazyConnectionDataSourceProxy) and this does not have the correct (Hikari specific) setters. We solved this by using two beans (the raw HikariDataSource to apply the properties to and a @Primary LazyConnectionDataSourceProxy for use by the application):

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariDataSource hikariDataSource(DataSourceProperties properties) {
        HikariDataSource hikariDataSource = createDataSource(properties, HikariDataSource.class);
        if (StringUtils.hasText(properties.getName())) {
            hikariDataSource.setPoolName(properties.getName());
        }
        return hikariDataSource;
    }

    protected static <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
        return (T) properties.initializeDataSourceBuilder().type(type).build();
    }

    @Primary
    @Bean
    public DataSource dataSource(HikariDataSource hikariDataSource) {
        // Wrap hikariDataSource in a LazyConnectionDataSourceProxy
        LazyConnectionDataSourceProxy lazyDataSource = new LazyConnectionDataSourceProxy();
        lazyDataSource.setTargetDataSource(hikariDataSource);
        return lazyDataSource;
    }

(you may need to disable boot datasources by adding @EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class}) )

[jhoeller]

We got some related efforts in Spring's core JDBC support for 6.1.2:
spring-projects/spring-framework#29932
spring-projects/spring-framework#19688
spring-projects/spring-framework#21415

In addition to enabling late-bound connection pool routing, a lazy connection setup goes along nicely with virtual thread scaling scenarios: LazyConnectionDataSourceProxy can be a significant optimization for connection pool contention, in particular for common Hibernate operations that can be entirely processed against the second-level cache - for which we never obtain a target connection at all then.

[kicktipp]

We need this, too! We love LazyConnectionDataSourceProxy but it is difficult to configure. I just tried to switch to the new docker-compose support. Now I am doing it like this with the help of @nwwerum

It would be so nice to have a property to activate lazy connections. It is really a performance boost, in my opinion.

@Configuration
@RequiredArgsConstructor
// https://github.com/spring-projects/spring-boot/issues/15480
public class DataSourceConfig {

    private final Optional<JdbcConnectionDetails> jdbcConnectionDetailsOptional;

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariDataSource hikariDataSource(DataSourceProperties properties) {
        if (jdbcConnectionDetailsOptional.isPresent()) {
            // develpment/integration there is a docker-compose connection available
            var jdbcConnectionDetails = jdbcConnectionDetailsOptional.get();
            log.info(jdbcConnectionDetails.getJdbcUrl());
            return properties
                    .initializeDataSourceBuilder()
                    .type(HikariDataSource.class)
                    .url(jdbcConnectionDetails.getJdbcUrl())
                    .build();
        } else {
          log.info(properties.getUrl());
          HikariDataSource hikariDataSource = properties
                 .initializeDataSourceBuilder()
                 .type(HikariDataSource.class)
                 .build();
          if (StringUtils.hasText(properties.getName())) {
              hikariDataSource.setPoolName(properties.getName());
          }
          return hikariDataSource;
      }
    }

    @Primary
    @Bean
    public DataSource dataSource(HikariDataSource hikariDataSource) {
        LazyConnectionDataSourceProxy lazyDataSource = new LazyConnectionDataSourceProxy();
        lazyDataSource.setTargetDataSource(hikariDataSource);
        return lazyDataSource;
    }
}

[rajadilipkolli]

Hi @philwebb , team meeting was removed on March 18. Out of curiosity what was decided. Since there are improvements in Spring framework , Can we expect any forward work on this ticket?

[rajadilipkolli]

One work around that works and supports @ServiceConnection and all other spring boot autoconfiguration as well is

@Configuration(proxyBeanMethods = false)
class LazyConnectionDataSourceProxyConfig implements BeanFactoryPostProcessor {

    /**
     * Processes the bean factory after its standard initialization.
     * This method:
     * 1. Registers a new LazyConnectionDataSourceProxy as the primary datasource
     *
     * @throws BeansException if the "dataSource" bean is not found
     */
    @Override
    public void postProcessBeanFactory(@NonNull ConfigurableListableBeanFactory beanFactory) throws BeansException {
        DefaultListableBeanFactory listableBeanFactory = (DefaultListableBeanFactory) beanFactory;

        if (!listableBeanFactory.containsBean("dataSource")) {
            throw new BeansException("Required 'dataSource' bean is not defined") {};
        }

        // Register LazyConnectionDataSourceProxy as a primary bean
        listableBeanFactory.registerBeanDefinition(
                "lazyConnectionDataSourceProxy", createLazyConnectionDataSourceProxyDefinition(listableBeanFactory));
    }

    private GenericBeanDefinition createLazyConnectionDataSourceProxyDefinition(
            DefaultListableBeanFactory listableBeanFactory) {
        GenericBeanDefinition beanDefinition = new GenericBeanDefinition();
        beanDefinition.setBeanClass(LazyConnectionDataSourceProxy.class);
        beanDefinition.setInstanceSupplier(() -> new LazyConnectionDataSourceProxy(
                listableBeanFactory.getBean("dataSource", DataSource.class)));
        beanDefinition.setPrimary(true); // Set this bean as primary
        return beanDefinition;
    }
}

[philwebb]

@rajadilipkolli I actually can't remember the outcome of the team meeting I'm afraid. We've not had the time to really investigate the feature in any detail yet.

[cdprete]

Are there any news on this?
It would be great to have this supported out of the box.

[LuizGC]

It would be awesome if we also could add the read-only db properties in application conf. Since the LazyConnectionDataSourceProxy has the method to set it setReadOnlyDataSource.

https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/datasource/LazyConnectionDataSourceProxy.html#setReadOnlyDataSource(javax.sql.DataSource)