Payara JDBC pool validation x times per request

[Ciment Maltu]

Hi,

I use validation for my JDBC pool. I found that some requests took little bit more. After some investigation - I found  more than 1 validation select per request. Sometimes I found 5-15 `select sysdate from dual` per request. I didn't find any error about JDBC validation in logs.

I use oracle database and validation class `org.glassfish.api.jdbc.validation.OracleConnectionValidation`. Validation is enabled, Validate At Most Once: 30s, pooling yes. min and max connection - 50.

My expectation about validation of JDBC connections was to see most once validation select per request.

I attached screenshot from elastic APM, one example of request, where payara did more validation per request.

Did I understand correctly? What can cause many validation selects per request?

[Will Hartung]

How many transactions are you doing per request, you should be getting a single connection and sticking with it throughout the transaction, but if there are multiple transactions going on, then, yea, you'd see it validating the connection for each new transaction.

Regards,

WIll Hartung

[Ciment Maltu]

Hi Will,

I use only one transaction for this case. I use EJBs and the default transaction behaviour is REQUIRED. I have also setup JTA transaction type in my persistence.xml.

In JDBC connection pool settings I have checked option Associate with Thread:true. In my application I use JPA with QueryDSL. This is snippet how select looks like.

```

public boolean hasPackageForPlatform(Platform platform, List<Long> packageIds) {

long count = getJPAQueryFactory().selectFrom(qPackage)
.where(qPackage.platform.eq(platform))
.where(qPackage.id.in(packageIds))
.fetchCount();

return count > 0L;
}

```

P.S. I use Payara version 5.201

Any help?

Dátum: streda 8. septembra 2021, čas: 22:41:01 UTC+2, odosielateľ: willh...@gmail.com