db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From JF <jfran...@his.co.uk>
Subject Re: Distributed Txns across 2 processes/machines
Date Fri, 01 Apr 2011 12:48:06 GMT

I have a simple JEE6 EAR which is deployed on glassfish on two machines 
lil and liepa.

The beans use a DataSource which is setup as follows;


<jdbc-resource jndi-name="ExperimentDB" 
pool-name="derby_netExperimentPool"/>
<jdbc-connection-pool allow-non-component-callers="true" 
datasource-classname="org.apache.derby.jdbc.ClientXADataSource" 
is-isolation-level-guaranteed="true" name="derby_netExperimentPool" 
res-type="javax.sql.XADataSource" 
transaction-isolation-level="read-committed">
<property name="serverName" value="lil"/>
<property name="portNumber" value="1527"/>
<property name="databaseName" value="ExperimentDB"/>
<property name="User" value="app"/>
<property name="Password" value="app"/>
<property name="URL" value="jdbc:derby://lil:1527/ExperimentDB"/>
<property name="driverClass" 
value="org.apache.derby.jdbc.ClientXADataSource"/>
</jdbc-connection-pool>

So the Beans on BOTH liepa and lil point at a Derby instance running on lil.

A simple web-app (servlet) in the EAR begins a UserTransaction and the 
calls BeanC function6. BeanC aquires its DataSource through resource 
injection and since the bean is "transaction managed" the DataSource is 
enlisted in the User Transaction.
This inserts a row  into TEST_TABLE on the database and then calls BeanD 
on the other box liepa calling its function7.
BeanD aquires its DataSource through resource injection and since the 
bean is "transaction managed" the DataSource is enlisted in the User 
Transaction.
BeanD function7 tries to read the contents of TEST_TABLE to see whether 
it can see what BeanC has inserted.
BeanD then inserts a row into TEST_TABLE to see if BeanC can read it 
when control returns to it.
BeanC function6 then completes by reading what is in the table.



When I run this against the same Derby instance BeanD.function7 on liepa 
fails - presumably with a timeout on a lock;

SEVERE: java.sql.SQLTransactionRollbackException: DERBY SQL error: 
SQLCODE: -1, SQLSTATE: 40XL1, SQLERRMC: 40XL1

which the manual states is "40XL1    A lock could not be obtained within 
the time requested"

Since this query is being executed within the same Global Transaction 
but with a different branch ( I have tried running the eg with my own 
wrapping XADataSource sitting between the TransactionManager and the 
Derby XADataSource to check out the fact that I am running within the 
same Global Transaction on different branches - by inspecting the Xid), 
am I right in concluding that Derby does not support tightly coupled 
transaction branches?
Or is there something else I need to do in setting up my Derby connection?
If I repeat the experiment with Oracle 11gR2 Enterprise then I can see 
the row created by C when I call D, and row inserted by D when control 
returns to C, I do not have any locking problems.


Here is the code;


the code in the servlet;

private void test20() throws NamingException, NotSupportedException, 
SystemException, SecurityException, IllegalStateException, 
RollbackException, HeuristicMixedException, HeuristicRollbackException {
         System.out.println("ControllerServlet test19 start");
         try
         {
             InitialContext context = new InitialContext();
             javax.transaction.UserTransaction tx 
=(javax.transaction.UserTransaction)context.lookup("java:comp/UserTransaction");
             tx.begin();
             beanCr.function6();
             System.out.println("ControllerServlet BeanC function6 called");
             tx.rollback(); //Rollback to ensure that we do have one 
global txn controlling two branches in the Oracle case
         }
         finally
         {
             System.out.println("ControllerServlet test19 end");
         }
     }


BeanC



package uk.co.his.experiment;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;

import javax.annotation.Resource;
import javax.ejb.EJB;
import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.sql.DataSource;

/**
  * Session Bean implementation class BeanC
  */
@Stateless(mappedName = "BeanC")
@LocalBean
public class BeanC implements BeanCRemote {

     //@Resource(name="ExperimentOra")
     @Resource(name="ExperimentDB")
     private DataSource defaultDataSource;


     @EJB(mappedName="corbaname:iiop:liepa:3700#BeanD")
     private BeanDRemote beanDr;

     /**
      * Default constructor.
      */
     public BeanC() {
         // TODO Auto-generated constructor stub
     }

     @Override
     public void function6() {
         System.out.println("function 6 start");
         try {
             Connection conn = defaultDataSource.getConnection();
             System.out.println("Got a " + conn);

             System.out.println("function 6 insertData");
             insertData(conn, "Created in C function 6 on " + 
InetAddress.getLocalHost().getCanonicalHostName());

             System.out.println("function 6 call BeanD.function7 on liepa");
             beanDr.function7();
             System.out.println("function 6 called BeanD.function7 on 
liepa");
             System.out.println("function 6 retrieveData");
             retrieveData(conn);
         } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         } catch (UnknownHostException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
         System.out.println("function 6 end");


     }


     private void insertData(Connection conn, String tag) throws 
SQLException
     {
         PreparedStatement stmt = conn.prepareStatement("INSERT INTO 
TEST_TABLE (ID, NAME) VALUES (?, ?)");
         stmt.setString(1, UUID.randomUUID().toString());
         stmt.setString(2, tag);
         stmt.execute();
         ResultSet rs = stmt.getResultSet();
         if(rs != null && rs.next())
         {
             System.out.println("There were results");
         }
     }

     private void retrieveData(Connection conn) throws SQLException
     {
         PreparedStatement stmt = conn.prepareStatement("SELECT ID, NAME 
FROM TEST_TABLE");

         ResultSet rs = stmt.executeQuery();
         if(rs != null && rs.next())
         {
             do
             {
                 String ID = rs.getString(1);
                 String name = rs.getString(2);
                 System.out.println("Found " + ID + ", " + name);
             }
             while(rs.next());
         }
         else
         {
             System.out.println("Nothing found");
         }
     }
}


BeanD


/**
  * Session Bean implementation class BeanD
  */
@Stateless(mappedName = "BeanD")
@LocalBean
public class BeanD implements BeanDRemote {

     //@Resource(name="ExperimentOra")
     @Resource(name="ExperimentDB")
     private DataSource defaultDataSource;
     /**
      * Default constructor.
      */
     public BeanD() {
         // TODO Auto-generated constructor stub
     }

     @Override
     public void function7() {
         System.out.println("function 7 start");
         try {
             Connection conn = defaultDataSource.getConnection();
             System.out.println("function 7 retrieveData");
             retrieveData(conn);
             System.out.println("function 7 insertData");
             insertData(conn, "Created in D function 7 on " + 
InetAddress.getLocalHost().getCanonicalHostName());
         } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         } catch (UnknownHostException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
         System.out.println("function 7 end");

     }

     private void insertData(Connection conn, String tag) throws 
SQLException
     {
         PreparedStatement stmt = conn.prepareStatement("INSERT INTO 
TEST_TABLE (ID, NAME) VALUES (?, ?)");
         stmt.setString(1, UUID.randomUUID().toString());
         stmt.setString(2, tag);
         stmt.execute();
         ResultSet rs = stmt.getResultSet();
         if(rs != null && rs.next())
         {
             System.out.println("There were results");
         }
     }

     private void retrieveData(Connection conn) throws SQLException
     {
         PreparedStatement stmt = conn.prepareStatement("SELECT ID, NAME 
FROM TEST_TABLE");

         ResultSet rs = stmt.executeQuery();
         if(rs != null && rs.next())
         {
             do
             {
                 String ID = rs.getString(1);
                 String name = rs.getString(2);
                 System.out.println("Found " + ID + ", " + name);
             }
             while(rs.next());
         }
         else
         {
             System.out.println("Nothing found");
         }
     }
}

Here is the trace for the Oracle DataSource on Lil

INFO: ControllerServlet test19 start

INFO: function 6 start

INFO: Got a com.sun.gjc.spi.jdbc40.ConnectionWrapper40@1e0fa2e
INFO: function 6 insertData
INFO: function 6 call BeanD.function7 on liepa

INFO: function 6 called BeanD.function7 on liepa
INFO: function 6 retrieveData
INFO: Found 4f53b3df-6c4d-47bb-b5aa-5a47976c0b56, Created in C function 
6 on Lil
INFO: Found a6d4d431-5631-41ac-a8f3-7e37b37ce1a7, Created in D function 
7 on Liepa
INFO: function 6 end
INFO: ControllerServlet BeanC function6 called
INFO: ControllerServlet test19 end


Here is the trace for the Oracle DataSource on liepa

INFO: function 7 start

INFO: function 7 retrieveData
INFO: Found 4f53b3df-6c4d-47bb-b5aa-5a47976c0b56, Created in C function 
6 on Lil
INFO: function 7 insertData
INFO: function 7 end

Mime
View raw message