Spring boot jooq how to use same connection in a same grpc call?

Question

We are using hikari cp, and I have found repeated call getConnection and close(which returns the connection to the connection pool) multiple times in same grpc call.

If the connection is limited and it will impact the performance the getConnection will be too slow.

It will make some calls performance too slow. And make the p99 of calls terrible.

Need I mark the call transactional with readonly to optimize the repeated getConnection call for the queries? Or maybe I can use some kind grpc server interceptors to do such trick?

Thanks.

Answer 1

jOOQ's ConnectionProvider is designed this way:

• Before each query, ConnectionProvider.acquire():Connection is called
• After each query, ConnectionProvider.release(Connection) is called

The default implementation that wraps a JDBC DataSource will effectively call:

• DataSource::getConnection
• Connection::close

This is the most sensible default for a jOOQ query, which knows nothing about your application, transactions, lifecycles, etc.

In Spring, but also in jOOQ, once you start a transaction, the Connection becomes thread-bound, meaning that instead of acquiring a new connection for every query, the same one is provided for each query (this being a prerequisite for your code being transactional).

So, indeed, making your code transactional will prevent re-acquiring new connections for every jOOQ query. It doesn't matter if you're using Spring's transactions, or jOOQ's. They both work this way. As a third option, you can also manually manage the JDBC Connection lifecycle and supply jOOQ with a Connection instead of a DataSource.

This is all documented here as well.