Hibernate pooled and pooled-lo identifier generators

(Last Updated On: January 29, 2018)

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:

PooledOptimizer

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:

PooledLoOptimizer

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.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

4 thoughts on “Hibernate pooled and pooled-lo identifier generators

  1. Please Vlad can you confirm that issue HHH-9287 exists in pooled-lo optimizer also before hibernate version 4.3.11?

    1. The Jira issue mentions the version where an issue got fixed. Therefore, you should upgrade if you want to get the fix.

  2. Hello,

    And what do you think about the “uuid2” generator, which doesn’t need any database access ?

    Tkx

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.