Atomikos Forum

Hi,

I am encountering some strange behaviour around connection release in a Atomikos and Oracle database environment.

I have 2 datasources as AtomikosDatasourceBean enlisted in an Atomikos transaction but one via hibernate current session management and the other via JPA.

Once the transation completes, it seems to correctly release the connection from hibernate-managed session but not with JPA's. It ends in having connection pool exhausted.

Another thing that may be linked is that when the datasources are closing, I am having the following message :
WARN |c.a.d.p.ConnectionPool| atomikos connection pool 'java:comp/env/DataDS1': connection is still in use on pool destroy: an AtomikosXAPooledConnection with a SessionHandleState with 1 context(s) - please check your shutdown sequence to avoid heuristic termination of ongoing transactions!
WARN |c.a.d.p.ConnectionPool| atomikos connection pool 'java:comp/env/DataDS2': connection is still in use on pool destroy: an AtomikosXAPooledConnection with a SessionHandleState with 1 context(s) - please check your shutdown sequence to avoid heuristic termination of ongoing transactions!

but it only happens with Oracle drivers & database.

I tried with H2, Postgre, Mysql & SQLServer and everything is fine: no pool exhausted and no warning.

the topic default82df.html?community.6.3162.9 seems to be about the same issue but does not point to anything in the end...

Can you help me somewhere ?

I've created a maven project to produce the connection leak simply. However, I don't where to post the zipped file.

here is the main class :

package org.bonitasoft.jpa;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.transaction.TransactionManager;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * @author Julien Reboul
 *
 */
public class SimpleTest {

    public static void main(final String[] args) throws Exception {
        new SimpleTest().test();
    }

    public void test() throws Exception {
        System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.bonitasoft.engine.local.SimpleMemoryContextFactory");
        System.setProperty(Context.URL_PKG_PREFIXES, "org.bonitasoft.engine.local");
        System.setProperty("sysprop.bonita.db.vendor", "oracle");
        final String dialect = "org.hibernate.dialect.Oracle10gDialect";

        //init context
        final ConfigurableApplicationContext springContext = new ClassPathXmlApplicationContext("datasource.xml", "jndi-setup.xml");
        final Properties props = (Properties) springContext.getBean("driverProperties");

        initDBTable(props);
        // init hibernate factory
        final SessionFactory factory = initSessionFactory();
        // init JPA factory
        final EntityManagerFactory entityManagerFactory = initEntityManagerFactory(dialect);

        // get TransactionManager
        final TransactionManager utm = lookupTM();

        utm.begin();
        Session session = factory.getCurrentSession();

        /* Add few employee records in database */
        addEmployee(session, 1, "Zara", "Ali", 1000);
        addEmployee(session, 2, "Daisy", "Das", 5000);

        final EntityManager entityManager = entityManagerFactory.createEntityManager();
        entityManager.joinTransaction();

        entityManager.persist(new Employee(4, "John", "Paul", 10000));
        utm.commit();

        utm.begin();
        session = factory.getCurrentSession();
        listEmployees(session);
        listEmployeeJPA(entityManagerFactory, 1);
        utm.commit();

        entityManagerFactory.close();
        springContext.close();
    }

    protected void initDBTable(final Properties props) throws SQLException {
        final Connection connection = DriverManager.getConnection(props.getProperty("URL"), props.getProperty("user"), props.getProperty("password"));
        Statement createStatement = connection.createStatement();
        try {
            createStatement
            .execute("drop table EMPLOYEE ");
        } catch (final Exception e) {
            e.printStackTrace();
        } finally {
            createStatement.close();
        }
        createStatement = connection.createStatement();
        createStatement
        .execute("create table EMPLOYEE (id NUMBER(19,0) NOT NULL ,first_name VARCHAR2(20) default NULL,"
                + "last_name  VARCHAR2(20) default NULL, salary INT default 0, PRIMARY KEY (id) )");
        createStatement.close();
        connection.close();
    }

    protected TransactionManager lookupTM() throws Exception {
        final InitialContext initialContext = new InitialContext();
        return (TransactionManager) initialContext.lookup("java:comp/UserTransaction");

    }

    protected EntityManagerFactory initEntityManagerFactory(final String dialect) {
        final Properties conf = new Properties();
        conf.put("hibernate.dialect", dialect);
        conf.put("hibernate.transaction.jta.platform", "org.bonitasoft.jpa.JNDIAtomikosJtaPlatform");
        conf.put("hibernate.connection.release_mode", "auto");
        conf.put("hibernate.current_session_context_class", "jta");
        conf.put("hibernate.transaction.factory_class", "org.hibernate.engine.transaction.internal.jta.CMTTransactionFactory");
        final EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("SimpleTest", conf);
        return entityManagerFactory;
    }

    protected SessionFactory initSessionFactory() {
        final Configuration configuration = new Configuration();
        configuration.addClass(Employee.class);
        return configuration.configure().buildSessionFactory();
    }

    /* Method to CREATE an employee in the database */
    public Integer addEmployee(final Session session, final int id, final String fname, final String lname, final int salary) {
        Integer employeeID = null;
        try {
            final Employee employee = new Employee(id, fname, lname, salary);
            employeeID = (Integer) session.save(employee);
        } finally {
        }
        return employeeID;
    }

    /* Method to READ all the employees */
    public void listEmployees(final Session session) {
        try {
            final List employees = session.createQuery("FROM Employee").list();
            for (final Iterator iterator =
                    employees.iterator(); iterator.hasNext();) {
                final Employee employee = (Employee) iterator.next();
                System.out.print("First Name: " + employee.getFirstName());
                System.out.print("  Last Name: " + employee.getLastName());
                System.out.println("  Salary: " + employee.getSalary());
            }
        } finally {
        }
    }

    public void listEmployeeJPA(final EntityManagerFactory entityManagerFactory, final int employeeId) {
        final EntityManager entityManager = entityManagerFactory.createEntityManager();
        entityManager.joinTransaction();

        final Employee employee = entityManager.find(Employee.class, employeeId);
        System.out.print("First Name: " + employee.getFirstName());
        System.out.print("  Last Name: " + employee.getLastName());
        System.out.println("  Salary: " + employee.getSalary());
        entityManager.flush();
        entityManager.clear();
        entityManager.close();
    }
}

can someone give us a hint, please?

after a bit of investigation, it seems the problem is exactly the same than in default82df.html?community.6.3162.9

does a patch or a fix version exists for this case ?

I'm wondering there is no new version available. This seems like a major bug.

Is there something like a roadmap? When can we expect a new version of Atomikos?

Meanwhile you can try this workaround: if you have a readonly service operation using more than one datasource, separate the calls to two different transactions (with spring you can use propagation REQUIRES_NEW)

Hi,

There is no fix yet because we can't reproduce this after adding some tests. So I strongly suspect the commercial release is right.

If you can make a budget available for a commercial subscription then that would be great...

Thanks

Thks Nicolaus but we can't afford to open a new transaction in this case...

We have found why we encounter a connection leak in read only transaction and why only with some Oracle databases
.
As far as we have understood the connection release operation, it is processed via some synchronization in the afterCompletion phase. It changes the state of the contexts of the SessionHandleState (which is directly linked to a Connection) to Tx.TERMINATED.

When all contexts of a SessionHandleState are at TERMINATED, the connection returns effectively available to the connection pool.
However, in the case where there is multiple datasources during a READ_ONLY transaction the synchronizations are never called...

It is pretty clear in the CoordinatorImp class, when calling prepare() returns READ_ONLY, then the commit() is not called :

970:     int prepareResult = prepare ();
971:     // make sure to only do commit if NOT read only
972:     if ( prepareResult != Participant.READ_ONLY )
973:         commit ( false );

It seems that when the commit is not called, the synchronizations are not executed either which seems to be the reason why we think it is a bug..

This issue only happens with some Oracle databases because, as far as we have tested, Oracle seems to be the only one to return XAResource.XA_READONLY when the Participant#prepare (implemented by XAResourceTransaction) calls XAResource#prepare. Every other database vendor we have tested (H2, Postgreql, MySQL, SQLServer) returns XAResource.XA_OK.

we have created a simple maven project with only JDBC connections where the problem occurs if I cant post it somewhere...