No connections in poll available when performing multiple queries in parallel

[zygisx]

When performing n repository queries in parallel, where n >= maximumPoolSize, all queries seems to be stuck and hikaricp pool do not have free connections. Getting error:

HikariPool-1 - Connection is not available, request timed out after 30002ms.

Preconditions:

1. Selecting aggregate entity with 1-n relationship
2. @Trasactional annotation is not added

I have made small demo project to showcase this problem:
https://github.com/zygisx/demo-spring-data-jdbc-problem

As you can see database aggregate contains 1-n relationship UserEntity -> UserEmailAddress: link

Whenever i add @Transactional annotation to repository problem is gone. Though spring-data-jdbc documentation states that:

CRUD methods on repository instances are transactional by default
https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#jdbc.transactions

Tests in demo project showcases this issue
https://github.com/zygisx/demo-spring-data-jdbc-problem/blob/main/src/test/kotlin/com/example/demo/DemoApplicationTests.kt

When hikaricp maximumPoolSize is 10. Tests which performs 9 parallel calls to Repository passes.
Though test which performs 10 parallel calls to repository fails with HikariPool-1 - Connection is not available, request timed out after 30002ms..

If You see that i can provide more info regarding this issue feel free to contact me.
Thanks for looking into it ;)

[schauder]

You don't have a @Transaction annotation, this means for every query a separate transaction is opened and for it a connection requested from the pool. Since Spring Data JDBC needs multiple queries for the method in question you have multiple threads trying to obtain connections in random order. This leads to deadlocks.

The fix should be simply to add @Transactional to the repository method.
The fix should result in either all method calls properly finishing or some of them getting a proper exception when the pool is exhausted.

Could you please verify, that the method works with proper transaction configuration?

[schauder]

Sorry, I saw you actually wrote, that it works with transactions.

About the documentation saying

CRUD methods on repository instances are transactional by default

Yeah, I guess that is misleading. This refers only to the methods provided by the CrudRepository interface.

[zygisx]

Yes, as I wrote with @Transactional it works.

So, if understood correctly, if no @Transactional annotation added to repository method, for each db query new connection will be obtained. And those connections will be released only after all queries will be finished.
So having big aggregate which performs many queries will use significant portion of database connection pool connections to retrieve aggregate from database, when no @Transactional added.
(please correct me, if my assumptions is wrong)

Is there a reason why Transactional behaviour is not a default one ?

Thanks for quick response ;)

[schauder]

Yes, your understanding is correct. I'll take the question about the default behaviour to the team.