CODEWITHSUNDEEP
javaspringsql-server-2008jpajboss
djb
djb

Reputation: 1674

JBoss reconnecting on connection time out

I want to reconnect when my connection dies.
The problem I'm getting, I explained here https://stackoverflow.com/questions/25504566/jta-transaction-in-other-thread-rolling-back
I haven't worked out why it happens, but I've made it impossible now for deadlocks to occur, so I will wait for a week to see if it's fixed.

But I thought my setup should reconnect on failing connection. But instead, the JTA issue kills my connection, and it doesn't come back. Presumably because it's CMT, there is one connection only, which is passed around via @PersistenceContext, so the same EntityManager is used for everything, so to reconnect, it would have to get a new EntityManager from the EntityManagerFactory, but it doesn't know to do that. I don't know the innards of JBoss and JPA.

This is my setup:

<datasources>
            <datasource jndi-name="java:/DS" pool-name="DS" enabled="true" use-java-context="true">
                <connection-url>jdbc:sqlserver://srv.local:1433;databaseName=AS_DEV</connection-url>
                <driver>sqlserver2008</driver>
                <pool>
                    <min-pool-size>5</min-pool-size>
                    <max-pool-size>50</max-pool-size>

                    <prefill>false</prefill>
                    <use-strict-min>false</use-strict-min>
                    <flush-strategy>FailingConnectionOnly</flush-strategy>
                </pool>
                <security>
                    <user-name>user</user-name>
                    <password>12345</password>
                </security>
                <validation>
                    <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
                </validation>
            </datasource>
            <drivers>
                <driver name="sqlserver2008" module="com.microsoft">
                    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
                </driver>
            </drivers>
        </datasources>

What else can I do, to ensure a revived connection on failure?

Upvotes: 1

Views: 8295

Answers (4)

Pinaki Mukherjee
Pinaki Mukherjee

Reputation: 1656

Use background validation with background-validation-millis. Please set the back ground validation based on your requirement. 

<validation>
   <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker" />
   <background-validation>true</background-validation>
   <validate-on-match>false</validate-on-match>
   <background-validation-millis>60000</background-validation-millis>
</validation>

• <background-validation>true</background-validation> When this is set to "true" set validate-on-match to "false." Use <background-validation-millis> to tune how often validation runs. The default value for the <background-validation-millis> parameter is 0 milliseconds. This also implies background validation is disabled. This value should not be the same as your <idle-timeout-minutes> value.

• Note that it is not valid to use both validate-on-match and background-validation for the same pool. Select one.

Upvotes: 4

rdiegoc
rdiegoc

Reputation: 31

To auto reconnect, you must configure validation tag:

<validation>
    <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
    <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
    <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
    <background-validation>true</background-validation>
    <background-validation-millis>60000</background-validation-millis>
</validation>

This example works fine with mysql

Upvotes: 0

Adam Parod
Adam Parod

Reputation: 178

The accepted answer enables both validate-on-match and background-validation, which the docs recommended against.

If performance is less critical to you, validate-on-match will check each connection prior to use, preventing any dead connections from being used. Otherwise, you can use background-validation, which will be less of a hit on your database, but you run the risk of using a connection that died since the last validation check. A quick summary of the two options, copied from the docs:

validate-on-match

When the option is set to true, the database connection is validated every time it is checked out from the connection pool. This setting results in the quickest recovery but creates the highest load on the database.

background-validation

This is used in combination with the background-validation-millis value to determine how often background validation runs. The lower the value, the more frequently the pool is validated and the sooner invalid connections are removed from the pool. However, lower values take more database resources. Higher values result in less frequent connection validation checks and use less database resources, but dead connections are undetected for longer periods of time.

Upvotes: 3

djb
djb

Reputation: 1674

This fixed it: 

   <validation>
       <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
       <validate-on-match>true</validate-on-match>
       <background-validation>true</background-validation>                      
   </validation>

Upvotes: 0

Related Questions