Unexpected operation order in a transaction

[pawel]

I have a session bean that does the following:

1) Updates some foreign-key fields.
2) Deletes a record.

Here is some sample code:

/**
* Deletes the user
* @exception javax.ejb.EJBException to cause the Container to rollback the transaction
* @param widgetName the name of the widget to delete
* @ejb:interface-method view-type="remote"
* @ejb:permission role-name="Anonymous"
* @ejb:transaction type="RequiresNew"
*/
public void deleteUser(String userName) throws EJBException{
try{
// locate all users with the specified name
USER aUser = userSession.findBy_USER_NAME(userName);

// find all phone numbers associated with a user
PHONE_NUMBER[] phones = phoneSession.findBy_USER_OID(aUser.getOID());
for (int i=0; i<phones.length; i++){
phones.setUSER_OID(null);
}

aUser.remove();

}catch(EJBException ee){
throw ee;
}catch(Exception e){
throw new EJBException(e);
}
}

where each PHONE_NUMBER has a foreign key relation to the USER. In order to delete a USER the foreign-key in PHONE_NUMBER must be nullified.

I am using CMP and the error that I am getting is that the updates occur after the delete when I check the DB log.

SQL0532N A parent row cannot be deleted because the relationship "MYDB.PHONE_NUMBER.USER_FK" restricts the deletion. SQLSTATE=23504

Thus the order of operations is not kept when they are sent to the DB, because the DB tries to execute the delete operation prior to having set the foreign keys to null.

How can I keep the order of operations the same as specified in the method?

Regards,
Pawel

[davidjencks]

PLEASE TELL US WHAT JBOSS VERSION YOU ARE USING!!

I believe this is fixed in 2.4.4

[pawel]

We're using JBOSS 2.2.2 Do you know what changes have been made to fix this problem? Whoms should I ask to find out? We cannot switch over to the newer versions right now so knowing the solution implemented would be extremely helpful.

Pawel

[dsundstrom]

In JBoss 2.4 before a finder is exected or a bean is removed all entities in the transaction are synchronized with the database. We added a datastructor to org.jboss.ejb.EntityContainer which tracks all entities associated with each transaction. Then before a query is executed or a remove is called, we call store on each entity.

[pawelj.]

I've tested this with JBoss-2_4_4 and indeed the problem has been fixed. Many thanks.....

Pawel