Two separate transactions with the same database connection

[skymic]

Hi

I use a JBoss4.0.5 with clustering and EJB3.0 profile.

I have defined the following Oracle datasource:

<datasources>
 <local-tx-datasource>
 <jndi-name>idmDS</jndi-name>
 <connection-url>jdbc:oracle:oci:@myDatabase</connection-url>
 <!--

 Here are a couple of the possible OCI configurations.
 For more information, see http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/java.920/a96654/toc.h
tm

 <connection-url>jdbc:oracle:oci:@youroracle-tns-name</connection-url>
 or
 <connection-url>jdbc:oracle:oci:@(description=(address=(host=youroraclehost)(protocol=tcp)(port=1521))(connect_data=(SERV
ICE_NAME=yourservicename)))</connection-url>

 Clearly, its better to have TNS set up properly.
 -->
 <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
 <user-name>user</user-name>
 <password>password</password>
 <min-pool-size>1</min-pool-size>
 <max-pool-size>20</max-pool-size>
 <idle-timeout-minutes>40</idle-timeout-minutes>
 <!-- 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-->
 <!-- Checks the Oracle error codes and messages for fatal errors -->
 <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
 <!-- sql to call when connection is created
 <new-connection-sql>some arbitrary sql</new-connection-sql>
 -->

 <!-- sql to call on an existing pooled connection when it is obtained from pool - the OracleValidConnectionChecker is pre
fered
 <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
 -->

 <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
 <metadata>
 <type-mapping>Oracle10i</type-mapping>
 </metadata>
 </local-tx-datasource>

</datasources>

The datasource is injected in a stateful session bean.

@Stateful
@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
@org.jboss.annotation.ejb.cache.tree.CacheConfig(idleTimeoutSeconds = 3000)
@org.jboss.annotation.ejb.cache.simple.CacheConfig(idleTimeoutSeconds = 3000)
public class SDOCIBusinessServiceImpl implements SDOCIBusinessService {

 @Resource(mappedName = "java:idmDS")
 private DataSource idmDataSource;

The datasource is used in the following method:

public RolesDO login(String username, String password) throws SDOCIBusinessException {
 _logger.info("login for user:" + username + " started... ");
 OracleCallableStatement cstmt = null;
 OracleConnection connection=null;

 try {
 Connection conn = idmDataSource.getConnection();
 connection = (OracleConnection) ((WrappedConnection) conn).getUnderlyingConnection();

Everytime this method is executed JBoss reuses a connection which is already in use by another instance of the staeful session bean.

How can I make sure that JBoss pulls a new connection out of the ConnectionPool for every new instance of the stateful session bean?

Any help?

Thanks