Hibernate prepareConnection/preparedStatement issues

Question

Within our project, we have updated Spring to 4.3.14.RELEASE and Hibernate to 4.3.11.Final and since we have started seeing two warnings:

1. WARN JDBC Connection to reset not identical to originally prepared Connection - please make sure to use connection release mode ON_CLOSE (the default) and to run against Hibernate 4.2+ (or switch HibernateJpaDialect's prepareConnection flag to false

   • The cure is to switch the release mode to ON_CLOSE, which we don't want – when our app is under heavy load, at some point it stops releasing the connections and the application gets stuck. This works correctly with release mode AFTER_TRANSACTION.
   • The message doesn't say what happens if this error is ignored. Googling doesn't show anything definitive
   • What happens if we switch the prepareConnection flag to false? I tried to find such flag in Hibernate configuration but couldn't find it.

2. GooGooStatementCache:441 - Multiply prepared statement!

   • Is this warning related to the previous one? Is it something we should worry?

EDIT: I updated Hibernate to 5.2.13.Final.

Nuzzle

Answer 1

Here is the explanation of multiply prepared statements (from the GooGooStatementCache source):

The same statement has already been prepared by this Connection, and that other instance has not yet been closed, so the statement pool has to prepare a second PreparedStatement object rather than reusing the previously-cached Statement. The new Statement will be cached, in case you frequently need multiple copies of this Statement.

In English, what this means is the same PreparedStatement was checked out from a Connection before the same PreparedStatement from the same Connection was checked in. This is unusual. Only one client and Thread typically interacts with a Connection at a time, and usually one would reuse the same PreparedStatement (resetting its parameters) if it is required multiple times. Probably the most likely reason this happens is because the application does not close() the PreparedStatements that it opens, relying upon c3p0 to clean them up on Connection checkin.

In any case, it's not a correctness problem, c3p0 will handle the case just fine. But it probably reflects code that could be cleaned up a bit (either by close()-ing PreparedStatements promptly after use or reusing them) to be more efficient in memory and resource use. If you are using the current version (0.9.5.2), c3p0 should have shown you the text of the problematic PreparedStatement.

---

I don't know why you are seeing the Spring/Hibernate warning. c3p0 checked out Connections are proxies, but their identities do not change within their client-visible lifecycles. Looking a bit at the source, there's some reflection and indirection in getting the Connection associated with a Session, which then becomes compared to the one that was checked out. Perhaps something causes the Session to refresh its associated Connection even while the Connection and Session are in use? I wish I could be of more help, but I'm not sure what is going on.

Fallible

Thanks for exhaustive response. As I am just using @Transactional annotation, I don't really open, nor close connections, so it is hard to say, how could it lead to these. I can see the text of the PreparedStatement, but as this is an SQL for fetching an entity, this can be anywhere in the project. I guess stack trace would be much more helpful. Anyway, you are jsut answering the second part of question, but the first is the one which is more important. – Kaela

Answer 2

I had same issue by using Tomcat JDBC Pool.

The problem comes in spring because they do a equality check at line 351 HibernateJpaDialect

The comparison is done on a Connection proxied by the Connection Pool (either Tomcat Pool or c3p0 in your case).

If you setup ON_CLOSE (legacy behavior in hibernate) the connection is only closed when the session is reset/closed which doesn't raise any problem but could change the performance especially if the client keeps Hibernate sessions open for a while.

If you setup (default) AFTER_TRANSACTION the connection is released after the transaction complete and before the session close. line 301 JdbcCoordinatorImpl

Normally, this method resetSessionState() checks not to close a connection twice line 349 if (this.preparedCon != null && this.session.isConnected())

The problem comes because the connection (proxied by the connection pool) changed during the first release (after transaction) and the equality is not met what causes the warn message. This warning message is a false positive

Crystalcrystalline

This explaines the behavior we experienced perfectly. On load hikari would keep the connections active for far to long until we run out of available connections. Which throws 'JDBCConnectionException: Unable to acquire JDBC Connection'. To help this we switched to AFTER_TRANSACTION which seems to resolve the first issue but also raises the 'JDBC Connection to reset...' warning. For now we suppress logs from the HibernateJpaDialect class, but is there an official fix for it? – Placement

Answer 3

you could try with the property hibernate.connection.handling_mode

hibernate.connection.handling_mode=DELAYED_ACQUISITION_AND_HOLD

If you are using JTA

hibernate.connection.handling_mode=DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION

or

hibernate.connection.handling_mode=DELAYED_ACQUISITION_AND_RELEASE_AFTER_STATEMENT

see this springframework javadoc section setPrepareConnection

Nitrobacteria

Yes, that was it the difference between JPA and JTA – Bicyclic

Answer 4

Disable org.springframework.orm.jpa.support.OpenEntityManagerInViewInterceptor by following Settings:

spring:
  jpa:
    open-in-view: false
    hibernate:
      connection:
        release_mode: on_close # default mode, you can remove this line

Tracey

I think the spring.jpa.open-in-view configuration option is Spring Boot specific. – Teetotal