com.smartgwt.client.docs

Interface SqlConnectionPooling

public interface SqlConnectionPooling

SQL Connection Pooling

Many databases will automatically close inactive connections, which can interfere with connection pooling: if an application is not constantly using all of the connections in its pool, it may retrieve a closed connection from the pool.

In some cases you can disable the behavior of closing inactive connections. For MySQL it's controlled by the wait_timeout setting in your my.cnf file). However, this could potentially cause leaked connections if applications terminate without cleaning up their database connections.

Intelligent connection pools compensate for unexpectedly closed connections automatically:

• J2EE containers generally implement internal keepalives or staleness checks - this is the preferred solution if available. If using SQLDataSource, use JNDI-based configuration as described here.
• SQLDataSource uses DBCP (Apache Commons) pooling, which also compensates for connection closure automatically. This is enabled by default with appropriate settings, but can be disabled system wide via setting sql.pool.enabled to false in server.properties, or disabled for a specific database configuration via sql.dbName.pool.enabled. The following properties can also be set on sql.pool / sql.dbName.pool and control same-named DBCP properties, however, it is not recommended to set these properties unless you have experience with DBCP and are troubleshooting a specific pool-related performance problem: testOnBorrow, testOnReturn, testWhileIdle, timeBetweenEvictionRunsMillis, minEvictableIdleTimeMillis, numTestsPerEvictionRun.

  When the pool is configured for connection validation, as it is by default, a SQL statement is run to verify the condition of its connection. To control the timeout value on this statement, set the sql.validationQueryTimeout / sql.dbName.validationQueryTimeout property (in seconds, default value is 10).

  If you are trying to diagnose an issue related to SQL connection pooling, you can enable DEBUG logging for the following classes in log4j.isc.config.xml (see installation instructions for details about this file). All of these classes are in package com.isomorphic.sql:

  • PoolableSQLConnectionFactory: logs connection creation, and whether or not the connections are pooled
  • SQLConnectionManager: logs when connections are borrowed
  • SQLDriver: logs the hashCode of the connection when SQL statements are executed
  • SQLTransaction: logs transactional open, commit, rollback and close.
• JPA/Hibernate: Hibernate's built-in connection pool is not intended for production use according to Hibernate's own documentation. This includes using JPA with Hibernate as the provider. If you get dead connections during development you can disable Hibernate's built-in connection pool by setting "hibernate.connection.pool_size" to 0. For production use you must use production-ready connection pool libraries for example C3P0. Here are recommended settings for C3P0 properties:
  • c3p0.acquireRetryDelay=1000
  • c3p0.acquireRetryAttempts=60
  • c3p0.breakAfterAcquireFailure=false