0004176: XA Transaction Manager Bug with Two Oracle connections in the same transaction with the same URL.

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0004176

[Resin]

minor

always

08-12-10 10:02

06-08-11 09:40

Reporter

reza

View Status

public

Assigned To

Priority

normal

Resolution

fixed

Status

closed

Product Version

3.1.9

Summary

0004176: XA Transaction Manager Bug with Two Oracle connections in the same transaction with the same URL.

Description

This bug was uncovered by the NTT Data folks. It happens when there are two open connections to Oracle with the same URL but two different users in the same transaction. Here is the error:

================================================================================
Rolling back transaction from failed begin()
[12:41:59.386] {http--8080-3$4766820} oracle.jdbc.xa.OracleXAException
[12:41:59.386] {http--8080-3$4766820} at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1033)
[12:41:59.386] {http--8080-3$4766820} at oracle.jdbc.xa.client.OracleXAResource.end(OracleXAResource.java:436)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.jca.PoolItem.endResource(PoolItem.java:988)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.jca.PoolItem.rollback(PoolItem.java:880)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.transaction.TransactionImpl.rollbackInt(TransactionImpl.java:818)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.transaction.TransactionImpl.rollback(TransactionImpl.java:764)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.transaction.TransactionManagerImpl.rollback(TransactionManagerImpl.java:279)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.jca.UserTransactionImpl.begin(UserTransactionImpl.java:368)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.jca.UserTransactionProxy.begin(UserTransactionProxy.java:139)
[12:41:59.386] {http--8080-3$4766820} at qa.TestServlet.doGet(TestServlet.java:44)
[12:41:59.386] {http--8080-3$4766820} at javax.servlet.http.HttpServlet.service(HttpServlet.java:114)
[12:41:59.386] {http--8080-3$4766820} at javax.servlet.http.HttpServlet.service(HttpServlet.java:91)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.server.dispatch.ServletFilterChain.doFilter(ServletFilterChain.java:103)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.server.webapp.WebAppFilterChain.doFilter(WebAppFilterChain.java:187)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.server.dispatch.ServletInvocation.service(ServletInvocation.java:265)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:273)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.server.port.TcpConnection.run(TcpConnection.java:682)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.util.ThreadPool$Item.runTasks(ThreadPool.java:743)
[12:41:59.386] {http--8080-3$4766820} at com.caucho.util.ThreadPool$Item.run(ThreadPool.java:662)
[12:41:59.386] {http--8080-3$4766820} at java.lang.Thread.run(Thread.java:619)
javax.transaction.SystemException: javax.transaction.SystemException: oracle.jdbc.xa.OracleXAException
    at com.caucho.jca.UserTransactionImpl.begin(UserTransactionImpl.java:329)
    at com.caucho.jca.UserTransactionProxy.begin(UserTransactionProxy.java:139)
    at qa.TestServlet.doGet(TestServlet.java:44)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:114)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:91)
    at com.caucho.server.dispatch.ServletFilterChain.doFilter(ServletFilterChain.java:103)
    at com.caucho.server.webapp.WebAppFilterChain.doFilter(WebAppFilterChain.java:187)
    at com.caucho.server.dispatch.ServletInvocation.service(ServletInvocation.java:265)
    at com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:273)
    at com.caucho.server.port.TcpConnection.run(TcpConnection.java:682)
    at com.caucho.util.ThreadPool$Item.runTasks(ThreadPool.java:743)
    at com.caucho.util.ThreadPool$Item.run(ThreadPool.java:662)
    at java.lang.Thread.run(Thread.java:619)
Caused by: javax.transaction.SystemException: oracle.jdbc.xa.OracleXAException
    at com.caucho.transaction.TransactionImpl.enlistResource(TransactionImpl.java:316)
    at com.caucho.jca.UserTransactionImpl.begin(UserTransactionImpl.java:327)
    ... 12 more
Caused by: oracle.jdbc.xa.OracleXAException
    at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1033)
    at oracle.jdbc.xa.client.OracleXAResource.start(OracleXAResource.java:240)
    at com.caucho.jca.PoolItem.start(PoolItem.java:737)
    at com.caucho.transaction.TransactionImpl.enlistResource(TransactionImpl.java:313)
    ... 13 more
================================================================================

Here is the stripped down Servlet code that causes the problem:

================================================================================
package qa;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import javax.transaction.HeuristicMixedException;
import javax.transaction.HeuristicRollbackException;
import javax.transaction.NotSupportedException;
import javax.transaction.RollbackException;
import javax.transaction.SystemException;
import javax.transaction.UserTransaction;

public class TestServlet extends HttpServlet {
  private static final long serialVersionUID = 1L;

  @Override
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException
  {
    try {
      Context context = new InitialContext();
      UserTransaction userTransaction = (UserTransaction) context
          .lookup("java:comp/UserTransaction");

      DataSource dataSource1 = (DataSource) context
          .lookup("java:comp/env/jdbc/TestDb1");
      DataSource dataSource2 = (DataSource) context
          .lookup("java:comp/env/jdbc/TestDb2");

      Connection connection1 = dataSource1.getConnection();
      Connection connection2 = dataSource2.getConnection();
      String sql = "insert into my_table(col1) values(?)";

      userTransaction.begin();

      PreparedStatement statement1 = connection1.prepareStatement(sql);
      statement1.setInt(1, 7777);
      PreparedStatement statement2 = connection2.prepareStatement(sql);
      statement2.setInt(1, 7777);

      statement1.executeUpdate();
      statement2.executeUpdate();

      statement1.close();
      statement2.close();
      connection1.close();
      connection2.close();

      userTransaction.commit();
    } catch (NotSupportedException e) {
      e.printStackTrace();
    } catch (SystemException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (IllegalStateException e) {
      e.printStackTrace();
    } catch (SecurityException e) {
      e.printStackTrace();
    } catch (RollbackException e) {
      e.printStackTrace();
    } catch (HeuristicMixedException e) {
      e.printStackTrace();
    } catch (HeuristicRollbackException e) {
      e.printStackTrace();
    } catch (NamingException e) {
      e.printStackTrace();
    }
  }
}
================================================================================

Here is the corresponding database configuration:

================================================================================
  <database jndi-name="jdbc/TestDb1">
     <driver type="oracle.jdbc.xa.client.OracleXADataSource">
       <url>jdbc:oracle:thin:@localhost:1521:xe</url>
       <user>hr</user>
       <password>hr</password>
     </driver>
     <prepared-statement-cache-size>8</prepared-statement-cache-size>
     <max-connections>20</max-connections>
     <max-idle-time>30s</max-idle-time>
     <xa>true</xa>
  </database>

  <database jndi-name="jdbc/TestDb2">
     <driver type="oracle.jdbc.xa.client.OracleXADataSource">
       <url>jdbc:oracle:thin:@localhost:1521:xe</url>
       <user>hr2</user>
       <password>hr2</password>
     </driver>
     <prepared-statement-cache-size>8</prepared-statement-cache-size>
     <max-connections>20</max-connections>
     <max-idle-time>30s</max-idle-time>
     <xa>true</xa>
  </database>
================================================================================

Attached is the Oracle driver that causes it. Here is a Servlet that the Oracle folks sent out showing the XA call sequence the driver is expecting:

================================================================================
package test.xa.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import javax.sql.XAConnection;
import javax.transaction.NotSupportedException;
import javax.transaction.SystemException;
import javax.transaction.TransactionManager;
import javax.transaction.UserTransaction;
import javax.transaction.xa.XAException;
import javax.transaction.xa.XAResource;
import javax.transaction.xa.Xid;

import com.caucho.loader.Environment;
import com.caucho.transaction.TransactionManagerImpl;
import com.caucho.transaction.XidImpl;
import com.caucho.util.Crc64;
import com.caucho.util.RandomUtil;

import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.xa.client.OracleXADataSource;

public class XAConnectionTestServlet6 extends HttpServlet {

    /**
     *
     */
    private static final long serialVersionUID = 1L;

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        try {
            doService(req, res);
        } catch (NamingException e) {
            e.printStackTrace();
        } catch (NotSupportedException e) {
            e.printStackTrace();
        } catch (SystemException e) {
            e.printStackTrace();
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        try {
            doService(req, res);
        } catch (NamingException e) {
            e.printStackTrace();
        } catch (NotSupportedException e) {
            e.printStackTrace();
        } catch (SystemException e) {
            e.printStackTrace();
        }
    }

    private void doService(HttpServletRequest req, HttpServletResponse res)
            throws NamingException, NotSupportedException, SystemException {
        try
        {
            String URL1 = "jdbc:oracle:thin:@10.4.177.249:1521:orcl";
            // You can put a database name after the @ sign in the connection URL.
            String URL2 ="jdbc:oracle:thin:@10.4.177.249:1521:orcl";
            // Create first DataSource and get connection
            OracleDataSource ods1 = new OracleDataSource();
            ods1.setURL(URL1);
            ods1.setUser("UT_XA_SCHEMA_A");
            ods1.setPassword("UT_XA_SCHEMA_A");
            Connection conna = ods1.getConnection();

            // Create second DataSource and get connection
            OracleDataSource ods2 = new OracleDataSource();
            ods2.setURL(URL2);
            ods2.setUser("UT_XA_SCHEMA_B");
            ods2.setPassword("UT_XA_SCHEMA_B");
            Connection connb = ods2.getConnection();

            // Prepare a statement to create the table
            Statement stmta = conna.createStatement ();

            // Prepare a statement to create the table
            Statement stmtb = connb.createStatement ();

            try
            {
              // Drop the test table
              stmta.execute ("drop table my_table");
            }
            catch (SQLException e)
            {
              // Ignore an error here
            }

            try
            {
              // Create a test table
              stmta.execute ("create table my_table (col1 int)");
            }
            catch (SQLException e)
            {
              // Ignore an error here too
            }

            try
            {
              // Drop the test table
              stmtb.execute ("drop table my_tab");
            }
            catch (SQLException e)
            {
              // Ignore an error here
            }

            try
            {
              // Create a test table
              stmtb.execute ("create table my_tab (col1 char(30))");
            }
            catch (SQLException e)
            {
              // Ignore an error here too
            }

            // Create XADataSource instances and set properties.
            OracleXADataSource oxds1 = new OracleXADataSource();
            oxds1.setURL("jdbc:oracle:thin:@10.4.177.249:1521:orcl");
            oxds1.setUser("UT_XA_SCHEMA_A");
            oxds1.setPassword("UT_XA_SCHEMA_A");

            OracleXADataSource oxds2 = new OracleXADataSource();

            oxds2.setURL("jdbc:oracle:thin:@10.4.177.249:1521:orcl");
            oxds2.setUser("UT_XA_SCHEMA_B");
            oxds2.setPassword("UT_XA_SCHEMA_B");

            // Get XA connections to the underlying data sources
            XAConnection pc1 = oxds1.getXAConnection();
            XAConnection pc2 = oxds2.getXAConnection();

            // Get the physical connections
            Connection conn1 = pc1.getConnection();
            Connection conn2 = pc2.getConnection();

            // Get the XA resources
            XAResource oxar1 = pc1.getXAResource();
            XAResource oxar2 = pc2.getXAResource();


            // Create the Xids With the Same Global Ids
            Xid xid1 = createXid(1);
            Xid xid2 = createXid(2);

            // Start the Resources
            oxar1.start (xid1, XAResource.TMNOFLAGS);
            oxar2.start (xid2, XAResource.TMNOFLAGS);

            // Execute SQL operations with conn1 and conn2
            doSomeWork1 (conn1);
            doSomeWork2 (conn2);


            // END both the branches -- IMPORTANT
            oxar1.end(xid1, XAResource.TMSUCCESS);
            oxar2.end(xid2, XAResource.TMSUCCESS);

            // Prepare the RMs
            int prp1 = oxar1.prepare (xid1);
            int prp2 = oxar2.prepare (xid2);

            System.out.println("Return value of prepare 1 is " + prp1);
            System.out.println("Return value of prepare 2 is " + prp2);

            boolean do_commit = true;

            if (!((prp1 == XAResource.XA_OK) || (prp1 == XAResource.XA_RDONLY)))
               do_commit = false;

            if (!((prp2 == XAResource.XA_OK) || (prp2 == XAResource.XA_RDONLY)))
               do_commit = false;

           System.out.println("do_commit is " + do_commit);
            System.out.println("Is oxar1 same as oxar2 ? " + oxar1.isSameRM(oxar2));

            if (prp1 == XAResource.XA_OK)
              if (do_commit)
                 oxar1.commit (xid1, false);
              else
                 oxar1.rollback (xid1);

            if (prp2 == XAResource.XA_OK)
              if (do_commit)
                 oxar2.commit (xid2, false);
              else
                 oxar2.rollback (xid2);

             // Close connections
            conn1.close();
            conn1 = null;
            conn2.close();
            conn2 = null;

            pc1.close();
            pc1 = null;
            pc2.close();
            pc2 = null;

            ResultSet rset = stmta.executeQuery ("select col1 from my_table");
            while (rset.next())
              System.out.println("Col1 is " + rset.getInt(1));

            rset.close();
            rset = null;

            rset = stmtb.executeQuery ("select col1 from my_tab");
            while (rset.next())
              System.out.println("Col1 is " + rset.getString(1));

            rset.close();
            rset = null;

            stmta.close();
            stmta = null;
            stmtb.close();
            stmtb = null;

            conna.close();
            conna = null;
            connb.close();
            connb = null;

        } catch (SQLException sqe)
        {
          sqe.printStackTrace();
        } catch (XAException xae)
        {
          if (xae instanceof OracleXAException) {
            System.out.println("XA Error is " +
                          ((OracleXAException)xae).getXAError());
            System.out.println("SQL Error is " +
                          ((OracleXAException)xae).getOracleError());
          }
        }

    }

    private void doSomeWork2(Connection conn2) {
        try{
            Statement stm = conn2.createStatement();
            stm.execute("insert into my_tab(col1) values('test222')");
            stm.execute("insert into my_tab(col1) values('test22')");
            stm.execute("insert into my_tab(col1) values('test2')");
            stm.close();
        }catch(SQLException e){
            e.printStackTrace();
        }

    }

    private void doSomeWork1(Connection conn1) {
        try{
            Statement stm = conn1.createStatement();
            stm.execute("insert into my_table(col1) values('111')");
            stm.execute("insert into my_table(col1) values('11')");
            stm.execute("insert into my_table(col1) values('1')");
            stm.close();
        }catch(SQLException e){
            e.printStackTrace();
        }

    }

    private Xid createXid(int i) {

        return createXid();
    }

    private XidImpl createXid(){
        return new XidImpl(getServerId(),RandomUtil.getRandomLong());
    }

    private int getServerId(){
        // 0 for test
        int _serverId = 0;

        if (_serverId == 0) {
          String server = (String) Environment.getAttribute("caucho.server-id");

          if (server == null){
              _serverId = 1;
          }else{
              _serverId = (int) Crc64.generate(server);
          }
        }

        return _serverId;
      }

}
================================================================================

I tested this on GlassFish and JBoss and the error does not occur. The error also exists in the latest trunk 4.x.

Additional Information

Attached Files

Relationships

Notes
(0004722) ferg 08-16-10 12:14	env/11i9

Issue History
Date Modified	Username	Field	Change
08-12-10 10:02	reza	New Issue
08-16-10 12:14	ferg	Note Added: 0004722
08-16-10 12:14	ferg	Assigned To	=> ferg
08-16-10 12:14	ferg	Status	new => closed
08-16-10 12:14	ferg	Resolution	open => fixed
08-16-10 12:14	ferg	Fixed in Version	=> 4.0.10
06-08-11 09:19	cowan	Assigned To	ferg =>
06-08-11 09:19	cowan	Status	closed => feedback
06-08-11 09:19	cowan	Resolution	fixed => reopened
06-08-11 09:19	cowan	Note Added: 0005300
06-08-11 09:40	cowan	Note Deleted: 0005300
06-08-11 09:40	cowan	Status	feedback => closed
06-08-11 09:40	cowan	Resolution	reopened => fixed

Mantis 1.0.0rc3[^]

32 total queries executed.
27 unique queries executed.