3 Replies Latest reply: Oct 5, 2010 8:10 AM by Cdelahun-Oracle RSS

    Optimstic Lock Exception with Foreign Keys

    801936
      We are using JEE5 with GlassFishV2.1 and MySQL 5.1 and this is related to OptimisticLockException that we get.

      We have two database tables. First table is USER: having UserId and EmailId columns. Second table is SME_ACCOUNT with AdminId, SharedId, SpaceLeft and Version field which is managed by javax.persistence.Version (@version annotation).

      SME_ACCOUNT has 2 foreign keys on AdminID and SharedID which refer to the UserId of the User table.

      Now,we want to delete entry for a particular EmailId from SME_ACCOUNT. For this we have to first delete the users with Id = AdminID and Id=SharedId from the USER table.

      Following is the code running in our Stateless Container Managed Bean:

      void deleteUser() {

      Query findByEmailID = em.createNamedQuery("Users.findByEmailID");
      findByEmailID.setParameter("emailID", emailID);
      // Find User from USER Table for a EmailID
      User user = (User) findByEmailID.getSingleResult();

      // Remove from SME Account
      SmeAccount smeAccount = em.find(SmeAccount.class, user.getId());
      em.remove(smeAccount);

      // Find/Remove Shared Entry
      Query findById = em.createNamedQuery("Parenttable.findById");
      findById.setParameter("id", smeAccount.getSharedId().getId());

      User sharedUser = (User) findById.getSingleResult();
      em.remove(sharedUser);
      // Remove Admin Entry
      em.remove(user);
      }

      This raises an OptimisticLOckException even when one transaction is running. I am unable to understand how do I make this work ...

      Please refer to http://stackoverflow.com/questions/3812968/optimistic-lock-exception-with-foreign-keys

      Please find the files ( Log of the operations. Entities that were created) at

      https://docs.google.com/leaf?id=0ByQlMqMW4RCoMTFkY2M4NmYtMjY3Ni00Y2M3LWFkMTQtNGU1NWI1NDc2NDhi&hl=en

      I have posted the same issue on Stack Overflow but with no response yet.

      Thanks
        • 1. Re: Optimstic Lock Exception with Foreign Keys
          Cdelahun-Oracle
          Hello,

          James did respond to the stackoverflow post requesting the logs with logging enabled. Looking at the classes and the log snippets provided, it appears the log was edited slightly. My guess is that the logs are reflecting that the findById.getSingleResult(); is causing a flush (which should show in the logs if set to finest), and is why a delete is followed by a select. Because the code then calls em.remove(sharedUser) and em.remove(user) both of which reference SmeAccount with a cascade all relationship, this is causing a second delete of the referenced SmeAccount object resulting in the optimisticLock exception because it was already deleted.

          Because of the relationship mappings, I do not believe that you should mark the User->SmeAccount relationships as cascade all, and instead mark the SmeAccount->User relationships as cascade delete. That way, when you call em.remove(smeAccount); there is no need to find and delete the referenced User objects, as they will already have delete called on them.

          Best Regards,
          Chris
          • 2. Re: Optimstic Lock Exception with Foreign Keys
            801936
            Hello cdelahun,

            Thanks, You are right that was the culprit. In our 'User' Entity we had the following
            ------------
            @OneToOne(cascade = CascadeType.ALL, mappedBy = "sharedAutoUID")
            private SmeAccount smeAccount;
            @OneToOne(cascade = CascadeType.ALL, mappedBy = "users")
            private SmeAccount sharedAutoUID;

            -------------

            But these were auto generated Entities and we overlooked this relation. Though in our DB there is nothing which should create this relation (See the SQL statements for the 'Users' and 'SMEAccount' tables below. There is no relation from 'Users' to 'SMEAccount'. Though there is one from 'SMEAccount' to 'Users').
            Is there a specific reason why TopLinks would have generated this relation? If I know this, I can revisit all my Entities for a sanity check.

            CREATE TABLE `users` (
            `Auto_UID` int(11) unsigned NOT NULL AUTO_INCREMENT,
            `UID` varchar(32) NOT NULL,
            `Email_Id` varchar(50) NOT NULL,
            `Firstname` varchar(25) NOT NULL,
            `Lastname` varchar(25) NOT NULL DEFAULT '',
            `Pwd` binary(20) NOT NULL,
            `Pwd_Attempts` tinyint(3) unsigned NOT NULL,
            `Security_Ques_Id` tinyint(3) unsigned NOT NULL,
            `Security_Ans` varchar(30) NOT NULL,
            `Account_Status_Id` tinyint(3) unsigned NOT NULL,
            `Subs_Id` int(10) unsigned NOT NULL,
            `Lang_Id` char(2) NOT NULL DEFAULT '',
            `Gender` char(1) NOT NULL,
            `Partner_Id` tinyint(3) unsigned NOT NULL DEFAULT '1',
            `User_Type_Id` tinyint(3) unsigned NOT NULL DEFAULT '0',
            PRIMARY KEY (`Auto_UID`),
            UNIQUE KEY `UserSubsIdx` (`Subs_Id`),
            UNIQUE KEY `UID_Idx` (`UID`),
            KEY `FK_user2` (`Security_Ques_Id`),
            KEY `FK_user3` (`Account_Status_Id`),
            KEY `FK_user5` (`Lang_Id`),
            KEY `FK_Partners` (`Partner_Id`),
            KEY `FK_utm` (`User_Type_Id`),
            CONSTRAINT `FK_utm` FOREIGN KEY (`User_Type_Id`) REFERENCES `user_type_mapping` (`User_Type_Id`),
            CONSTRAINT `FK_Partners` FOREIGN KEY (`Partner_Id`) REFERENCES `partner_details` (`Partner_Id`),
            CONSTRAINT `FK_user2` FOREIGN KEY (`Security_Ques_Id`) REFERENCES `security_ques_mapping` (`Security_Ques_Id`),
            CONSTRAINT `FK_user3` FOREIGN KEY (`Account_Status_Id`) REFERENCES `account_status_mapping` (`Account_Status_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
            CONSTRAINT `FK_user5` FOREIGN KEY (`Lang_Id`) REFERENCES `language_mapping` (`Lang_Id`),
            CONSTRAINT `FK_users2` FOREIGN KEY (`Subs_Id`) REFERENCES `subscription_details` (`Subs_Id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

            CREATE TABLE `sme_account` (
            `Admin_Auto_UID` int(11) unsigned NOT NULL,
            `Subs_Type_Id` tinyint(3) unsigned NOT NULL,
            `Shared_Auto_UID` int(11) unsigned NOT NULL,
            `Subs_Start_Date` date NOT NULL DEFAULT '0000-00-00',
            `Registration_Date` date NOT NULL DEFAULT '0000-00-00',
            `Space_Available_For_Allocation` bigint(20) NOT NULL,
            `Version` int(11) NOT NULL DEFAULT '1',
            PRIMARY KEY (`Admin_Auto_UID`),
            UNIQUE KEY `Shared_Auto_UID` (`Shared_Auto_UID`),
            KEY `FK_sme_admin` (`Admin_Auto_UID`),
            KEY `FK_sme_shared` (`Shared_Auto_UID`),
            KEY `FK_sme_subscription_type` (`Subs_Type_Id`),
            CONSTRAINT `FK_sme_account_shared` FOREIGN KEY (`Shared_Auto_UID`) REFERENCES `users` (`Auto_UID`),
            CONSTRAINT `FK_sme_account_subscription` FOREIGN KEY (`Subs_Type_Id`) REFERENCES `subscription_type` (`Subs_Type_Id`),
            CONSTRAINT `FK_sme_account_users` FOREIGN KEY (`Admin_Auto_UID`) REFERENCES `users` (`Auto_UID`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;


            Regards,
            • 3. Re: Optimstic Lock Exception with Foreign Keys
              Cdelahun-Oracle
              Hello,

              TopLink does not do entity generation, are you using an IDE that is doing it for you? The OneToOne with a mappedby value indicates that the foreign key is controlled by the target relationship, and is on the target table. It is not wrong to have, nor is it necessary; it is a convenience relationship to prevent having to query for SmeAccount objects with user=this when the other side is required. Depending on the tool used, there could be a way to not generate these backpointers, making the owning OneToOne mappings unidirectional.

              Best Regards,
              Chris