[SOLVED] Wildfly Idle/Hung connection timeout

Wildfly Idle/Hung connection timeout

I have a connection defined in standalone.xml to be able to release a connection upon idle timeout. I have noticed sometimes the connection is not releasing from SQL server DB even after the query has completed. The other scenario I would like to address is how to release a connection on a hung query even though its been cancelled from TEIID. Currently I have to manually kill the session to release the connection using the WILDFLY CLI.

<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>true</prefill>
                        <use-strict-min>true</use-strict-min>
                        <flush-strategy>IdleConnections</flush-strategy>
                    </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.novendor.NullExceptionSorter"/>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>30000</blocking-timeout-millis>
                        <idle-timeout-minutes>30</idle-timeout-minutes>
                    </timeout>
                    <statement>
                        <track-statements>false</track-statements>
                        <prepared-statement-cache-size>400</prepared-statement-cache-size>
                        <share-prepared-statements>true</share-prepared-statements>
                    </statement>
</datasource>

Solution

Your Jboss is configured to close idle connection after 30 minutes. The actual maximum time depends upon the idleRemover scan time, which is half of the smallest idle-timeout-minutes of any pool. In your case Idle connection remover runs every 15 minutes. The idle-timeout-minutes property should be configured to a value greater than 0 but less than the timeout period specified on the database server, network firewalls, etc. If you want idle connection being removed more quickly, you got to be configure it accordingly.