I know that a Hibernate application should use only one Hibernate session in a J2EE transaction (one hibernate session per application transaction). Anyway if you have already built a J2EE application that opens and closes hibernate sessions several times in one J2EE/JTA application transaction, then you should read this posting.

WebSphere 5.0/5.1 gives a different physical database connection for the same transaction if you call DataSource.getConnection() in different SLSB EJBs in some cases (at least when there is concurrent transactions going on). The database doesn't recognize that the different physical database connections belongs to the same transaction. This causes a lot of problems in database transaction isolation. For example, it will cause a deadlock if another EJB modifies some rows and later in the same transaction, another EJB tries to fetch these modified rows. WebSphere will also use different physical database connections for each Hibernate session (in a complex application this might be 10 connections for 1 transaction!).

Read this article to understand what it means:

http://www.ibm.com/developerworks/websphere/library/techarticles/0404_tang/0404_tang.html

It also has the solution. WebSphere will only return the same physical database connection if you use the WebSphere specific API to fetch the connection from the DataSource. WebSphere will return the same physical database connection if the contents of JDBCConnectionSpec objects match (It compares the new connection's JDBCConnectionSpec to the one of the existing connection.).

example code for fetching the JDBC Connection:

   JDBCConnectionSpec connSpec = WSRRAFactory.createJDBCConnectionSpec();
   connSpec.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
   if (user != null || pass != null) {
      connSpec.setUserName(user);
      connSpec.setPassword(pass);   
   }
   Connection connection=((WSDataSource)ds).getConnection(connSpec);

The integration to Hibernate is done using the net.sf.hibernate.connection.ConnectionProvider class. The below code is modified from the ConnectionProvider for JNDI (DatasourceConnectionProvider). There is a helper class for setting the transaction isolation level for a certain application Thread.

To use this ConnectionProvider, add this property to hibernate configuration:

<property name="connection.provider_class">net.sf.hibernate.websphere.WSDataSourceConnectionProvider</property>

Source code:

WSDataSourceConnectionProvider class

package net.sf.hibernate.websphere;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.cfg.Environment;
import net.sf.hibernate.connection.ConnectionProvider;
import net.sf.hibernate.util.NamingHelper;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.ibm.websphere.rsadapter.JDBCConnectionSpec;
import com.ibm.websphere.rsadapter.WSCallHelper;
import com.ibm.websphere.rsadapter.WSDataSource;
import com.ibm.websphere.rsadapter.WSRRAFactory;
import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;

/**
 * ConnectionProvider implementation that implements WSDataSource specific
 * Transaction Isolation level settings for the connection.
 * Modified from the original DatasourceConnectionProvider class by Lari Hotari
 *
 */
public class WSDataSourceConnectionProvider implements ConnectionProvider {
   private WSDataSource ds;
   private String user;
   private String pass;

   private static final Log log =
      LogFactory.getLog(WSDataSourceConnectionProvider.class);

   public void configure(Properties props) throws HibernateException {

      String jndiName = props.getProperty(Environment.DATASOURCE);
      if (jndiName == null) {
         String msg =
            "datasource JNDI name was not specified by property "
               + Environment.DATASOURCE;
         log.fatal(msg);
         throw new HibernateException(msg);
      }

      user = props.getProperty(Environment.USER);
      pass = props.getProperty(Environment.PASS);

      try {
         ds =
            (WSDataSource) NamingHelper.getInitialContext(props).lookup(
               jndiName);
      } catch (Exception e) {
         log.fatal("Could not find datasource: " + jndiName, e);
         throw new HibernateException("Could not find datasource", e);
      }
      if (ds == null)
         throw new HibernateException(
            "Could not find datasource: " + jndiName);
      log.info("Using datasource: " + jndiName);
   }

   public Connection getConnection() throws SQLException {
      if (log.isDebugEnabled()) {
         log.debug("getConnection()");
      }
      Connection connection=ds.getConnection(createJDBCConnectionSpec());
      
      if(log.isDebugEnabled()) {
         if(connection != null) {
            log.debug("Returning connection, System.identityHashCode=" + System.identityHashCode(connection));
            log.debug("Connection=" + connection);
            log.debug("Shareable=" + WSCallHelper.isShareable(connection));
            log.debug("Class=" + connection.getClass());
            Connection nativeConnection=(Connection)WSJdbcUtil.getNativeConnection((WSJdbcConnection)connection);
            log.debug("Native connection=" + nativeConnection);
            log.debug("Native connection, System.identityHashCode=" + System.identityHashCode(nativeConnection) + ", class=" + nativeConnection.getClass());
         } else {
            log.debug("Returning null");
         }
      }
      return connection;
   }

   public void closeConnection(Connection conn) throws SQLException {
      conn.close();
   }

   public void close() {
   }

   protected JDBCConnectionSpec createJDBCConnectionSpec() {
      JDBCConnectionSpec connSpec = WSRRAFactory.createJDBCConnectionSpec();
      connSpec.setTransactionIsolation(getIsolationLevel());
      if (user != null || pass != null) {
         connSpec.setUserName(user);
         connSpec.setPassword(pass);   
      }
      return connSpec;
   }

   /**
    * @return
    */
   private int getIsolationLevel() {
      int isolationLevel=WSDataSourceInterceptorThreadLocal.getIsolationLevel();
      if(isolationLevel==-1) {
         isolationLevel=Connection.TRANSACTION_READ_COMMITTED;
      }
      if(log.isDebugEnabled()) {
         log.debug("isolationLevel=" + isolationLevel);
      }
      return isolationLevel;
   }
}

Helper class for setting the transaction isolation level.

package net.sf.hibernate.websphere;


/**
 * This class stores desired transaction isolation level in a thread local variable.
 *
 * @author Lari Hotari
 */
public class WSDataSourceInterceptorThreadLocal extends ThreadLocal {

   public static int getIsolationLevel() {
      Integer intValue=(Integer)threadInstance.get();
      if(intValue != null) {
         return intValue.intValue();
      } else {
         return -1;
      }
   }

   public static void setIsolationLevel(int isolationLevel) {
      threadInstance.set(new Integer(isolationLevel));
   }

   private static ThreadLocal threadInstance = new ThreadLocal() {
      protected Object initialValue() {
         return new Integer(-1);
      }
   };
   
   public static void clear() {
      threadInstance.set(null);
   }

} 

Forum link: http://forum.hibernate.org/viewtopic.php?t=931737