Duplicate key issue with Spring and Hibernate - assistance needed

Question

--Summary (shortened)--

I have a controller that loads a profile object from the corresponding DAO. It updates some properties, many of them sets, and then calls saveOrUpdate (via save in the DAO) to reattach and update the profile object. At seemingly random intervals, we get an org.hibernate.exception.ConstraintViolationException, with a root cause: Caused by: java.sql.BatchUpdateException: Duplicate entry '3-56' for key 1. The stack trace points to the saveOrUpdate method called from the profile update controller. I can't replicate in my test environment, we only see this in production, so I'm wondering if I'm missing something thread-safety related (which is why I'm posting so much code/configuration info). Any ideas?

-- Code --

I've tried to provide as much relevant configuration/code as possible - let me know if more is needed:

Here is an excerpt from the offending controller:

public class EditProfileController extends SimpleFormController {

protected ModelAndView onSubmit(HttpServletRequest request, HttpServletResponse response, Object command, BindException errors) throws Exception
{
    if(!checkLoggedIn(request))
    {
        return new ModelAndView("redirect:" + invalidRedirect);
    }

    HttpSession session = request.getSession();
    Resource resource = (Resource)session.getAttribute("resource"); //The resource object is stored in session upon login and upon account creation.
    Profile profile = profiles.getProfileByResource(resource);

    if(profile == null)
    {
        profile = new Profile();
        profile.setResource(resource);
    }

    //I use custom editors to populate the sets in the command object with objects based on the selection

    if(profile.getPrimaryRoleSkills() != null && editProfileCommand.getPrimaryRoleSkills() != null)
    {
        profile.getPrimaryRoleSkills().addAll(editProfileCommand.getPrimaryRoleSkills());
        profile.getPrimaryRoleSkills().retainAll(editProfileCommand.getPrimaryRoleSkills());
    }
    else
        profile.setPrimaryRoleSkills(editProfileCommand.getPrimaryRoleSkills());

    profiles.save(profile);  //This is the line that appears in the stack trace
    return new ModelAndView(getSuccessView());
}
//Other methods omitted
}

Abbreviated Profile Class:

public class Profile  implements java.io.Serializable {

private long id;
private Resource resource;
private Set<PrimaryRoleSkill> primaryRoleSkills = new HashSet<PrimaryRoleSkill>(0);

public Profile() {
}
//Other properties trivial or similar to above.  Getters and setters omitted
//toString, equals, and hashCode are all generated by hbm2java
}

NameValuePairs base class (PrimaryRoleSkill extends this without adding anything):

public class NameValuePairs  implements java.io.Serializable {
private long id;
private String name;
private boolean active = true;

public NameValuePairs() {
}
//equals and hashCode generated by hbm2java, getters & setters omitted
} 

Here is my DAO base class:

public class DAO {

protected DAO() {
}   

public static Session getSession() {
      Session session = (Session) DAO.session.get();
      if (session == null) {
         session = sessionFactory.openSession();
         DAO.session.set(session);
      }
      return session;
}

protected void begin() {
  getSession().beginTransaction();
}

protected void commit() {
  getSession().getTransaction().commit();
}

protected void rollback() {
  try {
  getSession().getTransaction().rollback();
  } catch( HibernateException e ) {
     log.log(Level.WARNING,"Cannot rollback",e);
  }

  try {
     getSession().close();
  } catch( HibernateException e ) {
     log.log(Level.WARNING,"Cannot close",e);
  }
  DAO.session.set(null);
 }

public boolean save(Object object)
{
   try {
        begin();
        getSession().saveOrUpdate(object);
        commit();
        return true;
    }
    catch (HibernateException e) {
        log.log(Level.WARNING,"Cannot save",e);
        rollback();
        return false;
    }
}

private static final ThreadLocal<Session> session = new ThreadLocal<Session>();

private static final SessionFactory sessionFactory = new Configuration()
     .configure().buildSessionFactory();
private static final Logger log = Logger.getAnonymousLogger();

//Non-related methods omitted.
}

Below is the important part of the Profiles DAO:

public class Profiles extends DAO {
public Profile getProfileByResource(Resource resource)
{
    try
    {
        begin();
        Query q = getSession().createQuery("from Profile where resource = :resource");
        q.setLong("resource", resource.getId());
        commit();
        if(q.uniqueResult() == null)
            return null;

        return (Profile) q.uniqueResult();
    }
    catch(HibernateException e)
    {
        rollback();
    }
    return null;
}
//Non-related methods omitted.
}

Relevant Spring configuration:

<bean id="profiles" class="com.xxxx.dao.Profiles" />

<bean id="editProfileController" class="com.xxxx.controllers.EditProfileController">
    <property name="sessionForm" value="false" />
    <property name="commandName" value="editProfileCommand" />
    <property name="commandClass" value="com.xxxx.commands.EditProfileCommand" />

    <property name="profiles" ref="profiles" />     

    <property name="formView" value="EditProfile" />
    <property name="successView" value="redirect:/profile" />
    <property name="validator" ref="profileValidator" />
</bean>

hibernate.cfg.xml

<session-factory>
    <property name="connection.driver_class">@driver@</property>
    <property name="connection.url">@connectionurl@</property>
    <property name="connection.username">@dbuser@</property>
    <property name="connection.password">@dbpw@</property>
    <property name="dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
    <property name="dbcp.maxActive">15</property>
    <property name="dbcp.maxIdle">5</property>
    <property name="dbcp.maxWait">120000</property>
    <property name="dbcp.whenExhaustedAction">2</property>
    <property name="dbcp.testOnBorrow">true</property>
    <property name="dbcp.testOnReturn">true</property>
    <property name="dbcp.validationQuery">
        select 1
    </property>
    <property name="dbcp.ps.maxActive">0</property>
    <property name="dbcp.ps.maxIdle">0</property>
    <property name="dbcp.ps.maxWait">-1</property>
    <property name="dbcp.ps.whenExhaustedAction">2</property>

    <!-- Echo all executed SQL to stdout
    <property name="show_sql">true</property>
    -->

    <mapping resource="com/xxxx/entity/Resource.hbm.xml"/>
    <mapping resource="com/xxxx/entity/Authentication.hbm.xml"/>
    <mapping resource="com/xxxx/entity/NameValuePairs.hbm.xml"/>
    <mapping resource="com/xxxx/entity/Profile.hbm.xml"/>
    <mapping resource="com/xxxx/entity/FileData.hbm.xml"/>
 </session-factory>

Excerpt from Profile.hbm.xml:

<hibernate-mapping>
<class name="com.xxxx.entity.Profile" select-before-update="true">
<id name="id" type="long">
        <generator class="foreign">
            <param name="property">resource</param>
        </generator>
</id>

<set name="primaryRoleSkills" cascade="none">
    <key column="profile"/>
    <many-to-many column="primary_role_skill" class="com.xxxx.entity.PrimaryRoleSkill"/>
</set>
</class>
</hibernate-mapping>

Excerpt from NameValuePairs.hbm.xml:

<hibernate-mapping>
<class name="com.xxxx.entity.NameValuePairs" abstract="true">
    <id name="id" type="long">
    <generator class="native" />
</id>
<discriminator column="type" type="string" />
    <property type="string" name="name" length="256">
        <meta attribute="use-in-equals">true</meta>
    </property>
    <property type="boolean" name="active">
        <meta attribute="default-value">true</meta>
    </property>
    <subclass name="com.xxxx.entity.PrimaryRoleSkill" discriminator-value="PrimaryRoleSkill" />
    </class>
</hibernate-mapping>

The application runs on Tomcat 6.0.14, and connects to MySQL version 5.0.89-community, running on Linux. We are using Hibernate 3.3.2 and Spring Framework 2.5.6.

Answer 1

I don't have an immediate answer - it could be a multi-threading condition, or it could be that you're not using the right input data in your test environment to trigger the problem.

If this were my code, I'd start with comparing the equals(), compareTo() and hashCode() implementations with the database definitions - if these methods compare more fields than the database requires for uniqueness, hibernate might consider two objects to be different even if they end up using the same key in the database.

Another approach I'd consider would be to add logging to all places where these objects are saved and retrieved, including stacktraces (obviously with an on/off switch). Or alternatively, when you get the duplicate key error, query the database and log what is already in there. Either way you want to find out where the 'first' record is coming from.

Answer 2

After 10 days of being exception-free, I've come to conclude that the solution I discovered has worked.

Short answer: I switched my DAO to use HibernateTemplate, and use Spring AOP to handle transactions. This involved a lot of rewriting, but it was worth it, as the solution works as intended now. Also, I was unable to get lazy-loading to work in my JSP views, but this isn't a big deal, as my objects are fairly small (I disabled lazy-loading of properties in my Hibernate configuration)

Explanation: The problem was in the way I was obtaining a Hibernate Session. With the original implementation, one Hibernate session is created on application startup for each DAO that extended my DAO base class. This caused two problems. 1) Hibernate sessions are not thread-safe on their own. This is why everything tested fine with one user on a test instance, but had unusual behavior in production. 2) MySQL likes to shut down a connection after a certain period of time. Since the sessions were held open continuously, this was causing broken pipes (not reported in OP, I thought this was a separate issue). With this fix, Spring now manages session creation/closing, Spring AOP handles transaction demarkation, and SpringTemplate even handles much of the Hibernate access.