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.
What does 20Threads mean? Remote invocations, http requests.
The connection is back to pool after the complete transaction is committed/rollead back.
So it can be that it takes more.
Also it can be that you have a use-case which use the @RequiresNew annotation for transaction, in this case you might use more than one connection for one request.
Thanks for the reply!!!!
In my case, I have 20 DataMaps (CopyOnWriteArrayList), each datamap being populated with data parallely. Each datamap has a dedicated thread, which is triggered first time data is written to this datamap and then repeats the process till the datamap is empty.
On each notification, thread takes the data from CopyOnWriteArrayList, on basis of some business logic and then gets the connection by looking up the datasource ( using JNDI namespace). It then looks up the EJBs to perform the rest of the business logic, but here the same connection opened at the DataMap is used by the EJB to perform the DB writes.
The transactional attributes for EJB are
I'm a bit confused
@TransactionManagement==BEAN is the annotation to handle transactions in the code
@TransactionAttribute is the annotation for container managed transactions
So I think you have a mixture of it and a wrong expectation.
I would recommend to have the working SLSB with a new transaction to process the data and remove the entry of the map after it is complete.