Jooq consumes all available DB connections from Hikari CP-CodePudding

I'm writing a MySQL based app using Jooq and Hikari:

final HikariConfig config = new HikariConfig();
config.setMinimumIdle(10);
config.setMaximumPoolSize(100);
config.setConnectionTimeout(10000);
config.setMinimumIdle(10);
config.setJdbcUrl(AppConfig2.getMySqlUrl());
config.setUsername(AppConfig2.getMySqlUser());
config.setPassword(AppConfig2.getMySqlPassword());
final HikariDataSource dataSource = new HikariDataSource(config);
dslContext = DSL.using(dataSource, SQLDialect.MYSQL, settings);

Then I use this dslContext in my code to read data from the database. Problem is, after one or two calls, all 100 connections are consumed which is unexpected.

After a few initial DB calls, I get HikariPool-1 - Connection is not available, request timed out after 10007ms. error and with Hikari stats it telle me:

HikariPool-1 - Before cleanup stats (total=100, active=100, idle=0, waiting=0)
HikariPool-1 - After cleanup  stats (total=100, active=100, idle=0, waiting=0)

To me it seems like Jooq is greedily creating and keeping DB connections open and does not return them to the connection pool.

The commands in my code are simple fetchOnes like:

var twitterUser = dslContext.fetchOne(TWITTER_USER, TWITTER_USER.ID.eq(currentUserId));
var inboxUser = dslContext.fetchOne(INBOX_USER, INBOX_USER.TWITTER_USER_ID.eq(currentUserId));

CodePudding user response：

Turns out, I was running a statement without a call to fetch and Jooq does not issue any warnings about that. That statement was executed for lots of data items, which caused a fresh connection acquisition on each item.

return dslContext.selectFrom(TWITTER_DIRECT_MESSAGE)
            .where(
                TWITTER_DIRECT_MESSAGE.SENDER_ID.eq(senderId)
                    .and(TWITTER_DIRECT_MESSAGE.RECIPIENT_ID.eq(recipientId))
            ).orderBy(TWITTER_DIRECT_MESSAGE.CREATED_AT.desc())
            .limit(1)
            .fetch() //this was missing!!!
            .stream()
            .map(TwitterDirectMessageRecord::getCreatedAt)
            .findFirst();

CodePudding user response：

The jOOQ manual shows examples on how to correctly stream results with jOOQ. The ResultQuery::stream method mentions it too. The method returns a resourceful stream, which you have to close yourself:

try (Stream<R> stream = query.stream()) {
    // Do things with stream
}

There is no other way. See also this rejected feature request about returning auto-closing streams: #4932, which was rejected because of various reasons listed here: Register a Stream "completion" hook

If you don't really need resourceful (lazy) streams, then you already found the solution: call fetch() to eagerly fetch all data into memory first, and stream the resulting list.