Warning JDBC resources leaked when executing StoredProcedureQuery

[paschm]

Describe the bug
For legacy reasons we are using stored procedures in a Oracle DB. We are calling this procedures over an JPA EntityManager ( EntityManager#createStoredProcedureQuery(String) and StoredProcedureQuery#execute() ). This leads to follow warnings

2020-11-13 10:30:39,440 WARN  [io.agr.pool] (executor-thread-1) Datasource '<default>': Closing open connection prior to commit
2020-11-13 10:30:39,441 WARN  [io.agr.pool] (executor-thread-1) Datasource '<default>': JDBC resources leaked: 0 ResultSet(s) and 1 Statement(s)

As it seems upon execution a CallableStatement gets prepared and tracked in io.agroal.pool.wrapper.ConnectionWrapper.prepareCall(String) . However upon commit of the transaction this CallableStatment isn't closed and therefore doesn't get untracked. When the connection is closed, this results in an open tracked statement in ConnectionWrapper#closeTrackedStatements(), which results in the appropriate JdbcResourcesLeakReport and the warnings. A real resource leak imho doesn't exist, as this CallableStatment is closed in this method.

Expected behavior
When executing a StoredProcedureQuery the prepared CallableStatement gets closed upon commit of the transaction.

Actual behavior
The CallableStatement doesn't get closed upon commit of the transaction, which leads to an open tracked statement when closing the connection.

To Reproduce

Maven project to reproduce:
code-with-quarkus.zip

Script for stored procedure:
procedure.zip

Steps to reproduce the behavior:

1. Create procedure in Oracle DB
2. Modify user, pass and url in application.yml
3. mvn test

Environment (please complete the following information):

• Quarkus version: 1.9.2.Final
• Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

[gsmet]

/cc @barreiro @Sanne

[Sanne]

Interesting. In my experience, Agroal is typically right - we're probably leaking a callable in ORM.

[Sanne]

https://hibernate.atlassian.net/browse/HHH-14326

[paschm]

This even seems to be an issue in the spec

jakartaee/persistence#162

The workaround

Unwrapping and calling getOutputs().release() work as a workaround.

works. The tracked statement is released, before the connection is closed and the leakage warning disappears.

WARN [io.agr.pool] (executor-thread-1) Datasource '<default>': Closing open connection prior to commit

still persists.

[yrodiere]

I believe this would be solved by #7242 if we were to set PhysicalConnectionHandlingMode to DELAYED_ACQUISITION_AND_RELEASE_BEFORE_TRANSACTION_COMPLETION by default. See https://hibernate.zulipchat.com/#narrow/stream/132094-hibernate-orm-dev/topic/HHH-14326 .

There are, however, other aspects to this change, so I'll let the discussion continue on #7242.

[yrodiere]

Fixed in #14305