Jboss EAP7+Hibernate5+Azure DB连接池失效问题求助

First off, I’ve seen this exact issue with Azure SQL Database and JBoss connection pooling before. Azure SQL automatically drops idle connections after 30 minutes by default, and your current setup isn’t handling those stale connections properly. Let’s walk through the fixes step by step:

1. Fix Hibernate’s Connection Release Mode #

Your Hibernate config uses hibernate.connection.release_mode = on_close, which is a major culprit here. This setting keeps the database connection tied to the Hibernate Session until the Session is explicitly closed. If your app has long-lived Sessions (super common with lazy loading enabled via hibernate.enable_lazy_load_no_trans), connections sit idle way past Azure’s timeout window.

Update your persistence.xml to use a more aggressive release mode:

<property name="hibernate.connection.release_mode" value="after_transaction"/>

Or even better, use auto (the default for Hibernate 5+) which handles release based on context:

<property name="hibernate.connection.release_mode" value="auto"/>

This ensures connections are returned to the pool immediately after a transaction completes, instead of hanging around with an idle Session.

2. Tune JBoss Datasource Pool for Azure’s Idle Timeout #

Your current pool settings are way too large and aren’t proactively cleaning up stale connections. Azure drops idle connections at 30 minutes, so we need to configure JBoss to recycle connections before that happens.

Modify your datasource configuration as follows:

<datasource jta="false" jndi-name="java:jboss/datasources/mydb" pool-name="MyDataBase" enabled="true" use-ccm="false">
  <connection-url>jdbc:sqlserver://myserverdatabaseonazure:1433;databaseName=mydbname;loginTimeout=30</connection-url>
  <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
  <driver>sql</driver>
  <pool>
    <min-pool-size>10</min-pool-size> <!-- Reduce from 100 to avoid excessive idle connections -->
    <initial-pool-size>20</initial-pool-size> <!-- Match actual initial app load -->
    <max-pool-size>100</max-pool-size> <!-- Scale down to a reasonable max based on your app's needs -->
    <flush-strategy>IdleConnections</flush-strategy> <!-- Replace FailingConnectionOnly to actively clean idle connections -->
    <idle-timeout-minutes>25</idle-timeout-minutes> <!-- Recycle connections 5 mins before Azure's 30min timeout -->
  </pool>
  <security>
    <user-name>myuser@mydomain</user-name>
    <password>mypassword</password>
  </security>
  <validation>
    <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
    <check-valid-connection-sql>select current_timestamp</check-valid-connection-sql>
    <background-validation>true</background-validation>
    <background-validation-millis>900000</background-validation-millis> <!-- 15min, check more frequently than idle timeout -->
    <validate-on-match>true</validate-on-match> <!-- Verify connection health every time it's pulled from the pool -->
    <use-fast-fail>true</use-fast-fail> <!-- Fail fast on bad connections instead of retrying -->
    <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/>
  </validation>
  <timeout>
    <query-timeout>60</query-timeout> <!-- Set query timeout to avoid hanging queries -->
    <set-tx-query-timeout>true</set-tx-query-timeout>
  </timeout>
</datasource>

Key changes here:

• Reduced pool sizes to avoid having hundreds of idle connections that get dropped by Azure
• Added idle-timeout-minutes to recycle connections before Azure cuts them off
• Switched flush-strategy to actively clean up unused connections
• Enabled validate-on-match to check connection health every time it’s used
• Increased background validation frequency to catch stale connections earlier

3. Clean Up Hibernate Session Management #

Since you’re using RESOURCE_LOCAL transactions, make sure your code is properly managing Sessions and transactions. Always:

• Wrap database operations in explicit transactions (begin/commit/rollback)
• Close Sessions in a finally block or use try-with-resources (if using Hibernate 5.2+)
• Avoid holding onto Sessions longer than necessary (especially with lazy loading enabled)

Example of clean session handling:

try (Session session = sessionFactory.openSession()) {
  Transaction tx = session.beginTransaction();
  // Perform database operations
  tx.commit();
} catch (Exception e) {
  // Handle exception and rollback if needed
}

4. Verify JDBC Driver Settings #

You already upgraded to sqljdbc42.jar, which is great. Double-check that your JDBC URL includes Azure-specific parameters for secure, reliable connections:

jdbc:sqlserver://myserverdatabaseonazure:1433;databaseName=mydbname;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30

Final Testing #

After making these changes, redeploy your app and let it idle for 2+ hours to test. The IJ031040 and Connection reset errors should disappear because:

• Connections are returned to the pool quickly instead of sitting idle
• JBoss proactively recycles connections before Azure drops them
• Every connection pulled from the pool is validated for health

内容的提问来源于stack exchange，提问作者Daniel Max