Introduction

In this post, we’ll uncover a sequence identifier generator combining identifier assignment efficiency and interoperability with other external systems (concurrently accessing the underlying database system).

Traditionally there have been two sequence identifier strategies to choose from.

• The sequence identifier, always hitting the database for every new value assignment. Even with database sequence preallocation we have a significant database round-trip cost.
• The seqhilo identifier, using the hi/lo algorithm. This generator calculates some identifier values in-memory, therefore reducing the database round-trip calls. The problem with this optimization technique is that the current database sequence value no longer reflects the current highest in-memory generated value. The database sequence is used as a bucket number, making it difficult for other systems to interoperate with the database table in question. Other applications must know the inner-workings of the hi/lo identifier strategy to properly generate non-clashing identifiers.

The enhanced identifiers

Hibernate offers a new class of identifier generators, addressing many shortcomings of the original ones. The enhanced identifier generators don’t come with a fixed identifier allocation strategy. The optimization strategy is configurable and we can even supply our own optimization implementation. By default Hibernate comes with the following built-in optimizers:

• none: every identifier is fetched from the database, so it’s equivalent to the original sequence generator.
• hi/lo: it uses the hi/lo algorithm and it’s equivalent to the original seqhilo generator.
• pooled: This optimizer uses a hi/lo optimization strategy, but the current in-memory identifiers highest boundary is extracted from an actual database sequence value.
• pooled-lo: It’s similar to the pooled optimizer but the database sequence value is used as the current in-memory lowest boundary

In the official release announcement, the pooled optimizers are advertised as being interoperable with other external systems:

Even if other applications are also inserting values, we’ll be perfectly safe because the SEQUENCE itself will handle applying this increment_size.

This is actually what we are looking for; an identifier generator that’s both efficient and doesn’t clash when other external systems are concurrently inserting rows into the same database tables.

Testing time

The following test is going to check how the new optimizers get along with other external database table inserts. In our case, the external system will be some native JDBC insert statements in the same database table/sequence.

doInTransaction(new TransactionCallable<Void>() {
    @Override
    public Void execute(Session session) {
        for (int i = 0; i < 8; i++) {
            session.persist(newEntityInstance());
        }
        session.flush();
        assertEquals(8, ((Number) session.createSQLQuery("SELECT COUNT(*) FROM sequenceIdentifier").uniqueResult()).intValue());
        insertNewRow(session);
        insertNewRow(session);
        insertNewRow(session);
        assertEquals(11, ((Number) session.createSQLQuery("SELECT COUNT(*) FROM sequenceIdentifier").uniqueResult()).intValue());
        List<Number> ids = session.createSQLQuery("SELECT id FROM sequenceIdentifier").list();
        for (Number id : ids) {
            LOGGER.debug("Found id: {}", id);
        }
        for (int i = 0; i < 3; i++) {
            session.persist(newEntityInstance());
        }
        session.flush();
        return null;
    }
});

The pooled optimizer

We’ll first use the pooled optimizer strategy:

@Entity(name = "sequenceIdentifier")
public class PooledSequenceIdentifier {

    @Id
    @GenericGenerator(
        name = "sequenceGenerator", 
        strategy = "enhanced-sequence",
        parameters = {
            @org.hibernate.annotations.Parameter(
                name = "optimizer", 
                value = "pooled"
            ),
            @org.hibernate.annotations.Parameter(
                name = "initial_value", 
                value = "1"
            ),
            @org.hibernate.annotations.Parameter(
                name = "increment_size", 
                value = "5"
            )
        }
    )
    @GeneratedValue(
        strategy = GenerationType.SEQUENCE, 
        generator = "sequenceGenerator"
    )
    private Long id;
}

Running the test ends-up throwing the following exception:

DEBUG [main]: n.t.d.l.SLF4JQueryLoggingListener - Name: Time:0 Num:1 Query:{[insert into sequenceIdentifier (id) values (?)][9]} 
DEBUG [main]: n.t.d.l.SLF4JQueryLoggingListener - Name: Time:0 Num:1 Query:{[insert into sequenceIdentifier (id) values (?)][10]} 
DEBUG [main]: n.t.d.l.SLF4JQueryLoggingListener - Name: Time:0 Num:1 Query:{[insert into sequenceIdentifier (id) values (?)][26]} 
WARN  [main]: o.h.e.j.s.SqlExceptionHelper - SQL Error: -104, SQLState: 23505
ERROR [main]: o.h.e.j.s.SqlExceptionHelper - integrity constraint violation: unique constraint or index violation; SYS_PK_10104 table: SEQUENCEIDENTIFIER
ERROR [main]: c.v.h.m.l.i.PooledSequenceIdentifierTest - Pooled optimizer threw
org.hibernate.exception.ConstraintViolationException: could not execute statement
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:72) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]	
Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; SYS_PK_10104 table: SEQUENCEIDENTIFIER
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2]	

To visualize what happens I summarized the sequence calls in the following diagram:

When the pooled optimizer retrieves the current sequence value, it uses it to calculate the lowest in-memory boundary. The lowest value is the actual previous sequence value and this value might have been already used by some other external INSERT statement.

The HHH-9287 issue was fixed in in Hibernate 5.x and 4.3.11, and now the pooled optimizer works without throwing an exception.

The pooled-lo optimizer

Fortunately, there is one more optimizer(not mentioned in the reference documentation) to be tested. The pooled-lo optimizer uses the current database sequence value as the lowest in-memory boundary, so other systems may freely use the next sequence values without risking identifier clashing:

@Entity(name = "sequenceIdentifier")
public class PooledLoSequenceIdentifier {

    @Id
    @GenericGenerator(
        name = "sequenceGenerator", 
        strategy = "enhanced-sequence",
        parameters = {
            @org.hibernate.annotations.Parameter(
                name = "optimizer",
                value = "pooled-lo"
            ),
            @org.hibernate.annotations.Parameter(
                name = "initial_value", 
                value = "1"
            ),
            @org.hibernate.annotations.Parameter(
                name = "increment_size", 
                value = "5"
            )
        }
    )
    @GeneratedValue(
        strategy = GenerationType.SEQUENCE, 
        generator = "sequenceGenerator"
    )
    private Long id;
}

To better understand the inner-workings of this optimizer, the following diagram summarizes the identifier assignment process:

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

A hidden gem is one of those great features that most don’t even know of its existence. The pooled-lo optimizer is extremely useful, yet most people don’t even know of its existence.

Code available on GitHub.