0 Replies Latest reply on Jun 16, 2004 11:04 AM by pollentierc

    ejbRemove with commit A causes foreign key violation

    pollentierc Newbie

      Hi all,

      I have the following problem with CMP2.0 beans:

      We are currently using the 'Instance per Transaction' container configuration, but we are trying to move to a container with caching and commit option A.

      I have the impression that JBoss does not always handle the referential inegrity on the database correctly: We have table Parcel with a foreign key to table Dsp.
      On EJB level, we have bean Parcel with a one-to-many relation to bean Dsp. There is a setDsp() and a getDsp() method in the Parcel bean to set or get the CMR relation.

      I execute the following method in the Parcel bean (transaction attribute = 'Required'). It sets a new value for the Dsp relation, and then removes the old Dsp entity

      public void setNewDsp( DspLocal newDsp) throws ... {
      DspLocal oldDsp = getDsp();
      setDsp(newDsp) ;
      oldDsp.remove() ;

      When I look at the SQL statements received by mysql , I get the following sequence:

      1. DELETE FROM dsp WHERE dsp_pk='testRemoveAndCache''
      2. UPDATE parcel SET dsp_fk='testRemoveAndCache_2' WHERE parcel_pk='...'
      3. UPDATE dsp SET country_fk=null, business_partner_fk=null, dsp_type_fk=null, pin_process_fk=null WHERE dsp_pk='testRemoveAndCache'

      I see two problems here:
      - The first statement is sent to mysql when executing the third line of code. The second and third statement should be sent to mysql first because the related code is executed first.
      This update fails because of fk-violation, because the second query should be executed first.
      - The third statement tries to update the Dsp entity that was removed in the first statement.

      I have the impression that the remove() method is not included in the transaction.

      When I go through the code with debugger I see that the update of Parcel is cached (there is no sql sent to mysql). Then, when removing the Dsp, the query is sent immediately to mysql.

      Does anyone have any idea. Is this a JBoss bug ? Am I missing some config ?

      Some info:

      I am using jboss-3.2.3 with mysql 4.0.18-standard, mysqlConnector 3.0.11 running on Redhad Linux 9

      My datasource is configured with transaction isolation TRANSACTION_READ_COMMITTED

      Container config:

      <container-configuration extends="Standard CMP 2.x EntityBean">
      <container-name>KPServer CMP 2.x EntityBean</container-name>

      The 'Standard CMP 2.x EntityBean' config:
      <container-name>Standard CMP 2.x EntityBean</container-name>