Atomikos Forum

Hello,

We recently migrated our application to use atomikos transaction manger. After atomikos adoption. we get database locks in some of the transactions. After some investigation we found out that
atomikos gives 2 different connections in the same JTA transaction.  We manage our transactions using Spring annotation.

This is the pattern we have in our code.

1. Start spring transaction.
2. Get conn1 from datasource.
3. Get conn2 from datasource.
4. Use conn2 to run a insert query.
5. Close conn2.
6. Use conn1 and run a select query.

When [6] is run, it fails to acquire lock to the table and fails.

I have reproduced this issue in a small way and the source code is available here https://github.com/praveen12bnitt/atomkios-tx-issue/
You can clone the repo, import it to eclipse and run JdbcHangTest junit test to see it fail.

The method that has the problem is https://github.com/praveen12bnitt/atomkios-tx-issue/blob/master/src/main/java/com/manh/test/tx/MultipleConnection.java#L20

public void useMultipleConnectionsFail() throws Exception {

        Connection conn1 = null;
        Connection conn2 = null;

        // Get connection from the datasource and not do anything with it. Atomikos give conn1
        conn1 = ds.getConnection();

        // Get second connection from the datasource and run a query. Atomikos gives conn2. Not sure why. As per spec, within a transaction
        // any number of times you get connection, it should give you the same phyiscal connection
        conn2 = ds.getConnection();
        PreparedStatement ps2 = conn2.prepareStatement("insert into employee values('PALANIVEL1', '404-509-7085');");
        ps2.execute();        
        
        // Since are in a JTA transaction, conn2.close() does not close the connection. It will close it only when a commit is issued
        ps2.close();        
        conn2.close();

        // Now use conn1 to read the data. Here atomikos uses a diff db connection and the select query below waits for conn2 to commit.
        PreparedStatement ps1 = conn1.prepareStatement("select * from employee");

        ResultSet rs1 = ps1.executeQuery();
        while (rs1.next()) {
            System.out.println("Fist element");
        }
        
        rs1.close();
        ps1.close();
        conn1.close();

    }

What we believe is, if we get a conn and not do anything with it, atomkios does not bind it to the current tx. So next call to get connection does not return the same connection object.

If use get the conn and run a query on it, it binds it to the tx and after that any number of call to getConnection() give the same connection. So to confirm this, we wrong another method
https://github.com/praveen12bnitt/atomkios-tx-issue/blob/master/src/main/java/com/manh/test/tx/MultipleConnection.java#L52
The modified method doing exactly the same work passes. No dead locks.

Any help is greatly appreciated.

I am also working with my organization to get paid support.

The same code work on JBoss and Websphere. So is getting the connection and not using it the root cause of the problem?