Optimistic Lock Exception with Foreign Keys

Question

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 ...

Answer 1

hummm... i dont realy know, but I may suggest a few things:

User/SmeAccount has Cascade Operations (if they do, then they may alter each other in remove() )? Have you tried to remove user and sharedUser before SmeAccount?

Answer 2

Yes, I have tried, but that results in ForeignKey Constraints Violations.