Hibernate inconsistently generating duplicate primary keys

Question

I'm using Spring and Hibernate (hibernate-core 3.3.1.GA), and as a result of a web call, the code does a transaction with several inserts. Sometimes, one of the inserts fails with Hibernate saying 'Duplicate entry ... for key 'PRIMARY'. I have not been able to identify any pattern on when this happens -- it may work for 4 - 5 requests, and then it fails, then works on retrying, and then may fail on the next request.

Below are the relevant parts of the code:

Controller

@RequestMapping(value = "/users", method = RequestMethod.POST)
public @ResponseBody Map<Object, Object> save(<params>) throws IllegalArgumentException {
    ...
    try {
            map = userHelper.save(<parameters>);
    ...
    } catch (Exception e) {
        e.printStackTrace();
    }
}

The exception is thrown in the above part.

UserHelper.save() method

@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
public HashMap<String, Object> save(<parameters>) throws NumberParseException, IllegalArgumentException, HibernateException {
    ....
    userService.save(<parameters>);
    return save;
}

UserService

HBDao dao;

@Autowired
public UserService(org.hibernate.SessionFactory sessionFactory) {
    dao = new HBDao(sessionFactory);
}
...
@Transactional(propagation = Propagation.SUPPORTS, rollbackFor = Exception.class)
public HashMap<String, Object> save(<parameters>) throws NumberParseException {
    ...
    User user;
    // several lines to create User object
    dao.save(user);
    ...
    lookupService.saveUserConfigurations(user, userType, loginById);
    ...
    return response;
}

HBDao

This class wraps hibernate sessions.

public HBDao(SessionFactory sf) {
    this.sessionFactory = sf;
}

private Session getSession() {
    sessionFactory.getCurrentSession();
}

public void save(Object instance) {
    try {
        getSession().saveOrUpdate(instance);
    } catch (RuntimeException re) {
        throw re;
    }
}

lookupService.saveUserConfigurations(user, userType, loginById) call results in the below methods in LookupRepository class to be executed:

LookupRepository

@Transactional(propagation = Propagation.SUPPORTS, rollbackFor = Exception.class)
public LookupMapping save(LookupMapping configuration) {
    dao.save(configuration);
    return configuration;
}

public Collection<LookupMapping> saveAll(Collection<LookupMapping> configurations) {
    configurations.forEach(this::save);
    return configurations;
}

LookupMapping

@Entity
public class LookupMapping {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long configId;
    ...
}

Hibernate Mapping for LookupMapping class

<hibernate-mapping package="com...configuration.domain">
    <class name="LookupMapping" table="lookup_mapping" mutable="false">
        <id column="id" name="configId" type="long">
            <generator class="increment"/>
        </id>
        ...
    </class>
</hibernate-mapping>

Hibernate config

<hibernate-configuration>
    <session-factory name="sosFactory">
        <!-- Database connection settings -->
        ...

        <property name="connection.pool_size">2</property>

        <!-- SQL dialect -->
        <property name="dialect">com. ... .CustomDialect</property>

        <!-- Enable Hibernate's current session context -->
        <property name="current_session_context_class">org.hibernate.context.ManagedSessionContext</property>

        <!-- Disable the second-level cache -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

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

        ...
</session-factory>
</hibernate-configuration>

Below are the lines from the log:

2018-05-04 10:24:51.321 7|13|60f566fa-4f85-11e8-ba9b-93dd5bbf4a00 ERROR [http-nio-8080-exec-1] org.hibernate.util.JDBCExceptionReporter - Duplicate entry '340932' for key 'PRIMARY'
2018-05-04 10:24:51.321 7|13|60f566fa-4f85-11e8-ba9b-93dd5bbf4a00 WARN [http-nio-8080-exec-1] org.hibernate.util.JDBCExceptionReporter - SQL Error: 1062, SQLState: 23000
2018-05-04 10:24:51.322 7|13|60f566fa-4f85-11e8-ba9b-93dd5bbf4a00 ERROR [http-nio-8080-exec-1] org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) [hibernate-core-3.3.1.GA.jar:3.3.1.GA]
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50) [hibernate-core-3.3.1.GA.jar:3.3.1.GA]
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027) [hibernate-core-3.3.1.GA.jar:3.3.1.GA]
    at com.arl.mg.helpers.UserHelper.save(UserHelper.java:329) [classes/:?]
...

I'm working on a legacy codebase (so cannot upgrade Hibernate easily), and the code that I wrote are in LookupRepository class (and LookupService which is called in UserService).

The Duplicate entry error happens while persisting the LookupMapping objects. There are always two of this object being persisted, and when the error occurs, the duplicate ID is created same as the last entry. That is, if for the first request, IDs 999 and 1000 were inserted, and if the error occurs for the next request, the duplicate ID will be 1000 (and not 999).

Another, possibly important thing to note is that Hibernate shows this line:

org.hibernate.jdbc.ConnectionManager [] - transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources!

This is all the info that I have so far, and I hope I've covered the relevant code as well. Any help will be much appreciated. Do let me know if I have to give more info.

Thanks!

Answer 1

The problem was with the ID generation strategy defined in the Hibernate mapping file.

The strategy was set as increment, which seems to work only when there are no other processes inserting to the table. In my case, it seems that sometimes there were previously open sessions, and new requests ended up inserting to the table simultaneously.

The solution was to change the strategy to native, which uses the underlying database's strategy to generate ID.

<hibernate-mapping package="com...configuration.domain">
    <class name="LookupMapping" table="lookup_mapping" mutable="false">
        <id column="id" name="configId" type="long">
            <generator class="native"/>
        </id>
        ...
    </class>
</hibernate-mapping>