Transaction Rollback Difficulty

[mikeydavison]

Hi All,

I'm having the a problem with transaction rollbacks in JBoss 3.2.1. I run a method on a stateless session bean that runs two insert statements against a db table. The second statement is designed to fail so as to trigger an SQLException, which is caught and triggers a RemoteException to be thrown back to the container so a rollback may occur. To my surprise, the rollback doesn't occur and the first successful insert is reflected in the databse. FYI, the db is Oracle 9i. Here are the relevant code and config file snippets -

public class TaskServiceBean implements SessionBean

.
.
.

public void test() throws RemoteException {
Connection con = null;
try {
con = GetConnection.getConnection();

Statement s1 = con.createStatement();
s1.executeUpdate("insert into test_table (id, text) values (1, 'text')");
s1.close();

Statement s2 = con.createStatement();
s2.executeUpdate("insert into test_table (id, text) values (2, 'text'))");
s2.close();

}
catch (Exception ex) {
ex.printStackTrace();
throw new RemoteException("This should cause rollback");
}
finally {
try {con.close();} catch (Exception e) {}
}
}

from ejb-jar.xml

<container-transaction>

<ejb-name>TaskServiceBean</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>

The oracle datasource is configured according to the example defined in oracle-ds.xml that comes with JBoss.

I'd appreciate any insight as to why this apparently simple example fails to work properly. Thanks in advance.

Mike

[tslusser]

I think you should be throwing EJBException not RemoteException.

Also, make sure that you use XA DataSources if you want to have transactions that include two different datasources, jms, JCA connections, etc. Although here it looks like you don't need them.

Also make sure that <transaction-type> is set to Container when you declare the ejb-jar.xml

Good Luck!

[mikeydavison]

Ted, thanks for your reply. I've solved this problem, if anyone else is interested. A while back, I'd configured the my dev. environment to create connections without using JBoss's managed connection pool for tesing outside of the JBoss environment (e.g. DriverManager.createConnection). It's been a while since I made that change, so I forgot all about. Reconfiguring my env. to use the managed pool solved the problem. Hanging my head in shame.....

[yayokz]

I have the same problem as you were, I made connection to 2 different databases using UserTransaction in a statefull SessionBean. When the tables in both database exist the data was inserted properly, but when I delet one of the tables I was hoping that the data thta has aready been inserted in the other table willbe rolled back, but the rollback in the database was failed, even though in the bean the statement transaction.rollback() was executed. below is the code:


public Connection getConnectionOne() throws Exception{
BackOfficeDataSource boDS = new BackOfficeDataSource();
try{
Context ctxClient = new InitialContext(boDS.env);
ctxClient.addToEnvironment
(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.fscontext.RefFSContextFactory");
backOfficeDS = (DataSource)ctxClient.lookup("BackOfficeDS");
}
catch(Exception e){
}
return(backOfficeDS.getConnection());
}

public Connection getConnectionTwo() throws Exception{
BackOfficeDataSource boDS = new BackOfficeDataSource();
try{
Context ctxClient = new InitialContext(boDS.env);
ctxClient.addToEnvironment(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.fscontext.RefFSContextFactory");
backOfficeDS = (DataSource)ctxClient.lookup("BackOfficeDS_backup");
}
catch(Exception e){
}
return(backOfficeDS.getConnection());
}

public void createCustomer(Vector customerData) throws EJBException{
System.out.println("ejbBOCustomerTransaction createCustomer called...");
UserTransaction transaction = sessionContext.getUserTransaction( );
CreateID createID = new CreateID();
custID = createID.getCustomerID();
custName = String.valueOf(customerData.get(0));
try{
transaction.begin();
}
catch(Exception e){
throw new EJBException(e);
}
try{
System.out.println("" + transaction.getStatus());

backOfficeOneConnection = getConnectionOne();
statementOne = backOfficeOneConnection.prepareStatement("INSERT INTO T_CUSTOMER (CUST_ID, CUST_NAME) VALUES (?, ?)");
statementOne.setString(1, custID);
statementOne.setString(2, custName);
statementOne.executeUpdate();
System.out.println("1");

backOfficeTwoConnection = getConnectionTwo();
statementTwo = backOfficeTwoConnection.prepareStatement("INSERT INTO T_CUSTOMER (CUST_ID, CUST_NAME) VALUES (?, ?)");
statementTwo.setString(1, custID);
statementTwo.setString(2, custName);
statementTwo.executeUpdate();
System.out.println("2");

transaction.commit();
System.out.println("Transaction committed...");
}
catch(Exception e){
try{
transaction.rollback();
System.out.println("Transaction rolled back...");
}
catch(Exception re){
throw new EJBException(re);
}
}
}



Can anyone help me on this?
Thanks a lot.