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
