Wildfly数据源空闲连接未释放及挂起查询连接无法自动释放问题咨询

Let’s tackle your two connection management headaches one by one, using your existing Wildfly datasource config as a starting point.

1. Fixing Idle Connections Not Automatically Releasing #

Your current setup sets idle-timeout-minutes=30 and flush-strategy=IdleConnections, but several gaps might be preventing connections from being cleaned up properly. Here’s how to address them:

Key Configuration Adjustments #

• Replace the generic Exception Sorter: Your NullExceptionSorter won’t correctly identify SQL Server-specific connection failures (like broken or dropped connections). Switch to the MSSQL implementation to ensure invalid connections are removed from the pool:

  <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/>
  

• Enable Statement Tracking: Setting track-statements=false prevents Wildfly from detecting unclosed Statement or ResultSet objects, which can hold connections open indefinitely. Update this to:

  <track-statements>true</track-statements>
  

• Add Abandoned Connection Recovery: Even with idle timeouts, connections might get "abandoned" (e.g., if your app fails to close them properly). Add these settings to the <pool> section to auto-reclaim them:

  <remove-abandoned>true</remove-abandoned>
  <remove-abandoned-timeout-minutes>15</remove-abandoned-timeout-minutes>
  <abandoned-tracking>true</abandoned-tracking>
  

• Tweak Prefill Setting: Since your min-pool-size=0, prefill=true does nothing (there are no connections to prefill). Set it to false to avoid unnecessary overhead:

  <prefill>false</prefill>
  

Why This Works #

• The MSSQL Exception Sorter ensures the pool doesn’t hold onto dead connections that SQL Server has already dropped.
• Statement tracking catches leaks where your code forgets to close database objects properly.
• Abandoned connection recovery acts as a safety net for cases where connections are held open longer than expected.

2. Auto-Releasing Connections After TEIID Query Cancellation #

When you cancel a hung TEIID query, the connection should return to the pool automatically—but if it’s not, we need to ensure TEIID and Wildfly propagate the cancellation properly:

Key Fixes #

• Add Statement Timeouts: Force hung statements to close after a set period by adding a <statement-timeout> to the <statement> section of your datasource (adjust the value to match your query SLA):

  <statement-timeout>300</statement-timeout> <!-- 5 minutes in seconds -->
  

• Configure TEIID Execution Timeouts: In your TEIID VDB configuration, set an execution-timeout to auto-cancel long-running queries. This ensures TEIID actively closes the underlying JDBC connection when a query is canceled:

  <vdb name="YourVDB" version="1">
    <!-- ... other config ... -->
    <property name="execution-timeout" value="300000"/> <!-- 5 minutes in milliseconds -->
  </vdb>
  

• Leverage Wildfly Transaction Timeouts: Add a transaction timeout to your datasource to ensure stuck transactions don’t hold connections open. Add this inside the <datasource> block:

  <transaction>
    <timeout>300</timeout> <!-- 5 minutes in seconds -->
  </transaction>
  

Verify the Fix #

After making these changes:

1. Cancel a hung TEIID query through the TEIID admin console or API.
2. Check the connection pool status via Wildfly CLI to confirm active connections drop:

   /subsystem=datasources/data-source=SourceModel/statistics=pool:read-resource(include-runtime=true)
   

   Look for active-count decreasing after cancellation.

Full Modified Datasource Config #

Here’s your updated config with all recommended changes marked by comments:

<datasource jndi-name="java:/SourceModel" pool-name="SourceModel" enabled="true">
  <connection-url>jdbc:sqlserver://server:1433;integratedSecurity=true;authenticationScheme=NTLM;domain=DOMAIN;databaseName=DB</connection-url>
  <driver>sqlserver</driver>
  <pool>
    <min-pool-size>0</min-pool-size>
    <max-pool-size>60</max-pool-size>
    <prefill>false</prefill> <!-- Updated -->
    <use-strict-min>true</use-strict-min>
    <flush-strategy>IdleConnections</flush-strategy>
    <remove-abandoned>true</remove-abandoned> <!-- Added -->
    <remove-abandoned-timeout-minutes>15</remove-abandoned-timeout-minutes> <!-- Added -->
    <abandoned-tracking>true</abandoned-tracking> <!-- Added -->
  </pool>
  <security>
    <security-domain>SourceModelSecurityDomain</security-domain>
  </security>
  <validation>
    <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
    <check-valid-connection-sql>select 1</check-valid-connection-sql>
    <validate-on-match>true</validate-on-match>
    <use-fast-fail>false</use-fast-fail>
    <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/> <!-- Updated -->
  </validation>
  <timeout>
    <blocking-timeout-millis>30000</blocking-timeout-millis>
    <idle-timeout-minutes>30</idle-timeout-minutes>
  </timeout>
  <statement>
    <track-statements>true</track-statements> <!-- Updated -->
    <prepared-statement-cache-size>400</prepared-statement-cache-size>
    <share-prepared-statements>true</share-prepared-statements>
    <statement-timeout>300</statement-timeout> <!-- Added -->
  </statement>
  <transaction> <!-- Added -->
    <timeout>300</timeout> <!-- Added -->
  </transaction> <!-- Added -->
</datasource>

Final Notes #

• Always restart Wildfly after modifying datasource configs to ensure changes take effect.
• Monitor your connection pool metrics over time to confirm connections are being released as expected.
• If you still see stuck connections, check TEIID’s server logs for errors related to query cancellation—this might indicate a driver compatibility issue (ensure you’re using the latest SQL Server JDBC driver compatible with your Wildfly version).

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