Introduction

In this article, we are going to see how we can use the LazyConnectionDataSourceProxy with Spring Data JPA to acquire the database connection as late as possible and, therefore, reduce transaction response time.

For an introduction to how Spring transactions manage database connections, check out this article as well.

Service Layer Connection Management

Let’s consider we have the following getAsCurrency service method:

@Transactional(readOnly = true)
public Product getAsCurrency(Long productId, FxCurrency currency) {
    FxRate fxRate = getFxRate();
⠀
    Product product = productRepository
        .findById(productId)
        .orElseThrow();
⠀
    FxCurrency productCurrency = product.getCurrency();
    if (!productCurrency.equals(currency)) {
        product.convertTo(currency, fxRate);
    }
⠀
    return product;
}

Because the getAsCurrency service method is annotated with the @Transactional(readOnly = true) annotation, Spring is going to acquire the database connection eagerly, as illustrated by the following sequence diagram:

By default, when starting a JPA EntityTransaction, the Spring HibernateJpaDialect wants to switch the underlying database connection to the read-only mode. However, in order to achieve this goal, it has to acquire the JDBC Connection first, and once a connection is acquired in a RESOURCE_LOCAL transaction, it can only be released after the current transaction is committed or rolled back.

The downside of fetching the database connection eagerly

In our example, the very first thing that the getAsCurrency service method does is to fetch the current FxRate currency values:

private FxRate getFxRate() {
    long startNanos = System.nanoTime();
⠀
    String fxRateXmlString = restTemplate.getForObject(
        FxRateUtil.FX_RATE_XML_URL, 
        String.class
    );
⠀
    FxRate fxRate = null;
    if (fxRateXmlString != null) {
        fxRate = FxRateUtil.parseFxRate(
            fxRateXmlString.getBytes(
                StandardCharsets.UTF_8
            )
        );
    }
⠀
    LOGGER.debug(
        "FxRate loading took: [{}] ms",
        TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - startNanos)
    );
⠀
    return fxRate;
}

And when calling the getAsCurrency service method, we get the following log output:

ProductService - FxRate loading took: [748] ms

Query:["select p.id,p.currency,p.name,p.price from product p where p.id=?"], 
Params:[(1)]

Calling an external web service takes time, and our foreign exchange currency service is no different. However, since the database connection is acquired eagerly, it means that the connection is held for the duration of calling the external service, meaning that, for almost 750 milliseconds, we deprive other concurrent transactions from using the underlying database connection.

Ideally, the database connection should be acquired on demand prior to executing the first SQL statement, like the SELECT query that fetches the Product entity in our example.

Using the LazyConnectionDataSourceProxy with Spring Data JPA

The LazyConnectionDataSourceProxy has been available since the 1.1.4 version of Spring Framework, which was released on January 31, 2005.

To add the LazyConnectionDataSourceProxy to our Spring Data JPA project, we just need to wrap the DataSource we are going to provide to the LocalContainerEntityManagerFactoryBean with the LazyConnectionDataSourceProxy like this:

@Bean
public DataSource dataSource(){
    HikariConfig hikariConfig = new HikariConfig();
    hikariConfig.setAutoCommit(false);
    hikariConfig.setDataSource(dataSourceProvider().dataSource());
    HikariDataSource poolingDataSource = new HikariDataSource(hikariConfig);
⠀
    ChainListener listener = new ChainListener();
    SLF4JQueryLoggingListener loggingListener = new SLF4JQueryLoggingListener();
    loggingListener.setQueryLogEntryCreator(new InlineQueryLogEntryCreator());
    listener.addListener(loggingListener);
    listener.addListener(new DataSourceQueryCountListener());
    DataSource loggingDataSource = ProxyDataSourceBuilder
        .create(poolingDataSource())
        .name(DATA_SOURCE_PROXY_NAME)
        .listener(listener)
        .build();
⠀
    return new LazyConnectionDataSourceProxy(loggingDataSource);
}
⠀
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
    @Autowired DataSource dataSource
) {
    LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = 
        new LocalContainerEntityManagerFactoryBean();
    entityManagerFactoryBean.setPersistenceUnitName(
        getClass().getSimpleName()
    );
    entityManagerFactoryBean.setPackagesToScan(packagesToScan());
    entityManagerFactoryBean.setDataSource(dataSource);
⠀
    JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
    entityManagerFactoryBean.setJpaProperties(properties());
    return entityManagerFactoryBean;
}

With the LazyConnectionDataSourceProxy in place, the connection acquisition will take place as illustrated by the following sequence diagram:

When the JPA EntityTransaction is started, the LazyConnectionDataSourceProxy will create a ConnectionProxy that will not trigger the connection acquisition when calling the setReadOnly(true) call. Instead, the read-only flag is stored in the ConnectionProxy and set on the actual JDBC Connection after the connection is acquired because an SQL statement is about to be executed.

This way, the underlying database connection is acquired lazily, and the FxRate loading will be done without us holding a database connection.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

The Spring LazyConnectionDataSourceProxy is a great utility that allows us to acquire the database connection lazily when using Spring Data JPA.

By acquiring the database connection as late as possible, we can reduce the transaction response time and, therefore, execute more successive transactions in the unit of time on the same physical database connection.

Insert details about how the information is going to be processed

DOWNLOAD NOW