I have been using xa-datasource to have a connection pool for underlying oracle 10g database. I have created a pool of min size : 20 and max size : 40. I have a scenario where 20 threads are active, and each threads takes connection from the pool, completes its DB operation (i.e. saves approx 4000 records to DB). Once it completes the execution, it closes the connection which ideally should go back to the connection pool. The execution thread repeats it execution by taking a connection from pool and closes at the end on each execution cycle.
<xa-datasource> <jndi-name>appstatetxds</jndi-name> <use-java-context>false</use-java-context> <track-connection-by-tx></track-connection-by-tx> <isSameRM-override-value>false</isSameRM-override-value> <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class> <xa-datasource-property name="URL">jdbc:oracle:thin:@smm_db_host:1521/ALSMM</xa-datasource-property> <xa-datasource-property name="User">EPB_APP_CONF</xa-datasource-property> <xa-datasource-property name="Password">abc123</xa-datasource-property> <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name> <no-tx-separate-pools></no-tx-separate-pools> <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool --> <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name> <!--pooling parameters--> <min-pool-size>20</min-pool-size> <max-pool-size>40</max-pool-size> <blocking-timeout-millis>15000</blocking-timeout-millis> <idle-timeout-minutes>50000</idle-timeout-minutes> <metadata> <type-mapping>Oracle10g</type-mapping> </metadata> </xa-datasource>
Now as connection is closed, it should go back to the pool immediately, but what I have been observing from the admin console, that max connection count goes upto 28. It is sure that no other process is running into the system apart from these 20 threads. Each thread retrieves the connection from pool and closes it.
I just want to know, what could be the reason for going a max count upto 28, which should ideally be 20.
Does close method invocation, just returns the handler, and in back-ground it actually takes time to synchronize the data with uderlying DB. I just want to restrict that the haldler should be returned only once the connection actually is returned back to pool.