4 Replies Latest reply on Feb 8, 2002 12:42 PM by Pawel Jachowicz

    Unexpected operation order in a transaction

    Pawel Jachowicz Newbie

      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