We are using query timeout hints for both native and JPQL based queries using Hibernate as our JPA provider with great success. Timeouts work flawlessly with the Query.getSingleResult() and Query.getResultList() paths using Oracle as our DB implementation (in both JBOSS and WebSphere deployments using CMT)

We have a number of queries that for performance purposes, perform an "insert into XYZ select ....", again using both native and JPQL based queries. These queries, because they do an insert (which returns no ResultSet) must use the executeUpdate() path, rather than the Query.getSingleResult() or Query.getResultList() path.

The issue that we are having is that the executeUpdate() path does not seem to honor query timeout hints. I have debugged using the Hibernate Source code, and clearly, the executeUpdatePath does not invoke query.setQueryTimeout() as the query.getSingleResult() and query.getResultList() paths perform.

I found an interesting JIRA... HHH-346 which seems to have resolved this issue, however, the fix was in a module named UpdateStatementExecuter. UpdateStatementExecuter no longer seems to be part of the code base. As I debug, the executeUpdate() path makes use of a class called: NativeSqlQueryPlan, which seems to have nearly identical code to the UpdateStatementExecuter code and may very well have been the replacement for it. The timeout fix does not seem to have made its way into UpdateStatementExecuter however.

Our code is executed transactionally in a critical section. If a query takes too long, it can potentially lock out other processes. We need to ensure that no queries (including these inserts with selects) can run for too long. As a workaround we have tested obtaining the session, and connection from the entity manager, and executing the sql through a straight JDBC Statement with a query timeout set. This works fine, however, its a hack, and precludes the use of the ORM. We lose exception handling, and the ability to perform JPQL based queries, to name a few.


Does anyone have any suggestions on a better JPA/Hibernate based solution to this issue? Should I reopen or create a new JIRA?

Thank you in advance for your consideration.