Wrap the ProxyDataSource in LazyConnectionDataSource

[guitarmanmike]

…ce add jdbc event listener to reset connections tart time to the actual getConnection call on dataSource

[vladmihalcea]

I tried it, and it didn't work as expected. With the SpringDataJPAReadOnlyLazyConfiguration in place, the connection is acquired lazily by the auto-commit check. With the SpringDataJPAReadConfiguration, laziness is achieved by these two settings:

properties.setProperty(
    AvailableSettings.CONNECTION_PROVIDER_DISABLES_AUTOCOMMIT,
    Boolean.TRUE.toString()
);
properties.setProperty(
    AvailableSettings.CONNECTION_HANDLING,
    PhysicalConnectionHandlingMode.DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION.name()
);

So, the LazyConnectionDataSourceProxy does not influence the lazy acquisition at all.

[guitarmanmike]

@vladmihalcea

properties.setProperty(
    AvailableSettings.CONNECTION_HANDLING,
    PhysicalConnectionHandlingMode.DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION.name()
);

As you stated in your article (https://vladmihalcea.com/spring-transaction-connection-management/) using this property breaks the SpringTransactional management of IsolationLevel. The LazyConnectionDataSource was an attempt to get around this limitation without having the connection leased from the pool too early when using readOnly=true or IsolationLevel on the @Transactional annotation.

With the LazyTransactionalConnectionDataSource in place you would leave this property off so that Isolation level and read only flags can be set by Spring Transaction management. However when spring tries to eagerly get the connection and set the flags it will be getting the LazyConnection Proxy and setting the readOnly and Isolation onto the proxy settings, which means it didn't actually lease a connection yet.

With the changes in this pull request I was logging the actual connection lease time from Hikari pool (not when spring requested the lazy connection proxy) and you can see that it brings the connection time down from the ~1000ms to ~2ms. I.e the connection is leased from Hikari at the first prepareStatement to the connection.

This allows readOnly and IsolationLevel to function at the Spring Transaction Management level, but the connection doesn't get leased (from the Hikari Pool) eagerly. Yes spring still tries to get a Connection eagerly to set the flags but that is where the LazyConnectionProxy intercepts/is being returned.

With the log time using the jdbc getConnectionEvent I see the following:

Without lazyConnectionDataSource: 2024-10-16 18:00:53,077 INFO [main]: c.v.h.s.t.r.c.s.SpringTransactionStatisticsReport - type=TIMER, name=fxRateTimer, count=1, min=1254.4691, max=1254.4691, mean=1254.4691, stddev=0.0, median=1254.4691, p75=1254.4691, p95=1254.4691, p98=1254.4691, p99=1254.4691, p999=1254.4691, mean_rate=0.7604892957709418, m1=0.0, m5=0.0, m15=0.0, rate_unit=events/second, duration_unit=milliseconds 2024-10-16 18:00:53,077 INFO [main]: c.v.h.s.t.r.c.s.SpringTransactionStatisticsReport - type=TIMER, name=transactionTimer, count=1, min=1314.6912, max=1314.6912, mean=1314.6912, stddev=0.0, median=1314.6912, p75=1314.6912, p95=1314.6912, p98=1314.6912, p99=1314.6912, p999=1314.6912, mean_rate=0.7603331171452837, m1=0.0, m5=0.0, m15=0.0, rate_unit=events/second, duration_unit=milliseconds 2024-10-16 18:00:53,078 INFO [main]: c.v.h.s.t.r.SpringDataJPAReadOnlyLazyTest - The book price is 22.86 EUR

With LazyConnectionDataSource: 2024-10-16 18:03:49,927 INFO [main]: c.v.h.s.t.r.c.s.SpringTransactionStatisticsReport - type=TIMER, name=fxRateTimer, count=1, min=2342.3568, max=2342.3568, mean=2342.3568, stddev=0.0, median=2342.3568, p75=2342.3568, p95=2342.3568, p98=2342.3568, p99=2342.3568, p999=2342.3568, mean_rate=0.41573970570285124, m1=0.0, m5=0.0, m15=0.0, rate_unit=events/second, duration_unit=milliseconds 2024-10-16 18:03:49,927 INFO [main]: c.v.h.s.t.r.c.s.SpringTransactionStatisticsReport - type=TIMER, name=transactionTimer, count=1, min=10.1245, max=10.1245, mean=10.1245, stddev=0.0, median=10.1245, p75=10.1245, p95=10.1245, p98=10.1245, p99=10.1245, p999=10.1245, mean_rate=0.4156837823632687, m1=0.0, m5=0.0, m15=0.0, rate_unit=events/second, duration_unit=milliseconds 2024-10-16 18:03:49,927 INFO [main]: c.v.h.s.t.r.SpringDataJPAReadOnlyLazyTest - The book price is 22.86 EUR

So the connection is leased for 10ms instead of the 1300ms when it was leased eagerly.

[vladmihalcea]

@guitarmanmike You are right. I made this change to my lazy connection test case and added the breakpoint into the HikariDataSource, and indeed, the connection is fetched lazily.

That's a very good tip.