Hibernate Identity, Sequence and Table (Sequence) generator

Introduction

In my previous post I talked about different database identifier strategies. This post will compare the most common surrogate primary key strategies:

  • IDENTITY
  • SEQUENCE
  • TABLE (SEQUENCE)

IDENTITY

The IDENTITY type (included in the SQL:2003 standard) is supported by:

The IDENTITY generator allows an integer/bigint column to be auto-incremented on demand. The increment process happens outside of the current running transaction, so a roll-back may end-up discarding already assigned values (value gaps may happen).

The increment process is very efficient since it uses a database internal lightweight locking mechanism as opposed to the more heavyweight transactional course-grain locks.

The only drawback is that we can’t know the newly assigned value prior to executing the INSERT statement. This restriction is hindering the “transactional write behind” flushing strategy adopted by Hibernate. For this reason, Hibernates disables the JDBC batch support for entities using the IDENTITY generator.

For the following examples we’ll enable Session Factory JDBC batching:

properties.put("hibernate.order_inserts", "true");
properties.put("hibernate.order_updates", "true");
properties.put("hibernate.jdbc.batch_size", "2");

Let’s define an Entity using the IDENTITY generation strategy:

@Entity(name = "identityIdentifier")
public class IdentityIdentifier {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
}

Persisting 5 entities:

doInTransaction(new TransactionCallable<Void>() {
    @Override
    public Void execute(Session session) {
        for (int i = 0; i < 5; i++) {
            session.persist(new IdentityIdentifier());
        }
        session.flush();
        return null;
    }
});

Will execute one query after the other (there is no JDBC batching involved):

Query:{[insert into identityIdentifier (id) values (default)][]} 
Query:{[insert into identityIdentifier (id) values (default)][]} 
Query:{[insert into identityIdentifier (id) values (default)][]} 
Query:{[insert into identityIdentifier (id) values (default)][]} 
Query:{[insert into identityIdentifier (id) values (default)][]} 

Aside from disabling JDBC batching, the IDENTITY generator strategy doesn’t work with the Table per concrete class inheritance model, because there could be multiple subclass entities having the same identifier and a base class query will end up retrieving entities with the same identifier (even if belonging to different types).

SEQUENCE

The SEQUENCE generator (defined in the SQL:2003 standard) is supported by:

A SEQUENCE is a database object that generates incremental integers on each successive request. SEQUENCES are much more flexible than IDENTIFIER columns because:

  • A SEQUENCE is table free and the same sequence can be assigned to multiple columns or tables
  • A SEQUENCE may preallocate values to improve performance
  • A SEQUENCE may define an incremental step, allowing us to benefit from a “pooled” Hilo algorithm
  • A SEQUENCE doesn’t restrict Hibernate JDBC batching
  • A SEQUENCE doesn’t restrict Hibernate inheritance models

Let’s define a Entity using the SEQUENCE generation strategy:

@Entity(name = "sequenceIdentifier")
public class SequenceIdentifier {
    @Id
    @GenericGenerator(
        name = "sequence", 
        strategy = "sequence", 
        parameters = {
            @org.hibernate.annotations.Parameter(
                name = "sequence", 
                value = "sequence"
            )
        
    })
    @GeneratedValue(generator = "sequence")
    private Long id;
}

I used the “sequence” generator because I wanted to use the SequenceGenerator, rather than SequenceHiLoGenerator or SequenceStyleGenerator which Hibernate might choose on our behalf.

Adding 5 entities:

doInTransaction(new TransactionCallable<Void>() {
    @Override
    public Void execute(Session session) {
        for (int i = 0; i < 5; i++) {
            session.persist(new SequenceIdentifier());
        }
        session.flush();
        return null;
    }
});

Generate the following queries:

Query:{[call next value for hibernate_sequence][]} 
Query:{[call next value for hibernate_sequence][]} 
Query:{[call next value for hibernate_sequence][]} 
Query:{[call next value for hibernate_sequence][]} 
Query:{[call next value for hibernate_sequence][]} 
Query:{[insert into sequenceIdentifier (id) values (?)][1]} {[insert into sequenceIdentifier (id) values (?)][2]} 
Query:{[insert into sequenceIdentifier (id) values (?)][3]} {[insert into sequenceIdentifier (id) values (?)][4]} 
Query:{[insert into sequenceIdentifier (id) values (?)][5]} 

This table the inserts are batched, but we know have 5 sequence calls prior to inserting the entities. This can be optimized by using a HILO algorithm.

TABLE (SEQUENCE)

There is another database independent alternative to generating sequences. One or multiple tables can be used to hold the identifier sequence counter. But it means trading write performance for database portability.

While IDENTITY and SEQUENCES are transaction-less, using a database table mandate ACID, for synchronizing multiple concurrent id generation requests.

This is made possible by using row-level locking which comes at a higher cost than IDENTITY or SEQUENCE generators.

The sequence must be calculated in a separate database transaction and this requires the IsolationDelegate mechanism, which has support for both local (JDBC) and global(JTA) transactions.

  • For local transactions, it must open a new JDBC connection, therefore putting more pressure on the current connection pooling mechanism.
  • For global transactions, it requires suspending the current running transaction. After the sequence value is generated, the actual transaction has to be resumed. This process has its own cost, so the overall application performance might be affected.

Let’s define a Entity using the TABLE generation strategy:

@Entity(name = "tableIdentifier")
public class TableSequenceIdentifier {

    @Id
    @GenericGenerator(
        name = "table", 
        strategy = "enhanced-table", 
        parameters = {
            @org.hibernate.annotations.Parameter(
                name = "table_name", 
                value = "sequence_table"
            )
    })
    @GeneratedValue(generator = "table", strategy=GenerationType.TABLE)
    private Long id;
}	

I used the newer “enhanced-table” generator because the legacy “table” generator has been deprecated.

Adding 5 entities:

doInTransaction(new TransactionCallable<Void>() {
    @Override
    public Void execute(Session session) {
        for (int i = 0; i < 5; i++) {
            session.persist(new TableSequenceIdentifier());
        }
        session.flush();
        return null;
    }
});

Generate the following queries:

Query:{[select tbl.next_val from sequence_table tbl where tbl.sequence_name=? for update][default]} 
Query:{[insert into sequence_table (sequence_name, next_val)  values (?,?)][default,1]} 
Query:{[update sequence_table set next_val=?  where next_val=? and sequence_name=?][2,1,default]} 
Query:{[select tbl.next_val from sequence_table tbl where tbl.sequence_name=? for update][default]} 
Query:{[update sequence_table set next_val=?  where next_val=? and sequence_name=?][3,2,default]} 
Query:{[select tbl.next_val from sequence_table tbl where tbl.sequence_name=? for update][default]} 
Query:{[update sequence_table set next_val=?  where next_val=? and sequence_name=?][4,3,default]} 
Query:{[select tbl.next_val from sequence_table tbl where tbl.sequence_name=? for update][default]} 
Query:{[update sequence_table set next_val=?  where next_val=? and sequence_name=?][5,4,default]} 
Query:{[select tbl.next_val from sequence_table tbl where tbl.sequence_name=? for update][default]} 
Query:{[update sequence_table set next_val=?  where next_val=? and sequence_name=?][6,5,default]} 
Query:{[insert into tableIdentifier (id) values (?)][1]} {[insert into tableIdentifier (id) values (?)][2]} 
Query:{[insert into tableIdentifier (id) values (?)][3]} {[insert into tableIdentifier (id) values (?)][4]} 
Query:{[insert into tableIdentifier (id) values (?)][5]}

The table generator allows JDBC batching but it resorts to SELECT FOR UPDATE queries. The row level locking is definitely less efficient than using a native IDENTITY or SEQUENCE.

So, based on your application requirements you have multiple options to choose from. There isn’t one single winning strategy, each one having both advantages and disadvantages.

If you like this article, I bet you are going to love my book as well.

Code available on GitHub.

If you liked this article, you might want to subscribe to my newsletter too.

28 thoughts on “Hibernate Identity, Sequence and Table (Sequence) generator

      1. I personally think they needed to offer some big customers a migration path from other RDBMS, e.g. DB2… Same with OFFSET .. FETCH pagination.

  1. Great post. I am always looking forward your Hibernate posts because every time I learn something new from them.

    By the way, we have used JPA’s IDENTITY type with PostgreSQL 9.3 which supports auto increment. You can use serial data types (http://www.postgresql.org/docs/9.3/static/datatype-numeric.html) in the auto incremented column and it works like magic.

    I haven’t used Hibernate (through its own API) for ages, so I don’t know if this works with Hibernate’s IDENTITY type though.

      1. Hi Vlad.
        We are using Spring Boot witch by default comes with Hibernate. We also tried the IDENTITY type with PostgreSQL serial column but our conclusion was different from what you explain. Looking at the logs that were generated by Hibernate there is no query to fetch an id prior to insertion and the insert statements do not contain any id.
        Can you please help us understand who could this work and why would Hibernate act differently than the case with MySQL (AUTO-INCREMENT) which I guess has the same benefit (relying on the persistence provider to assign id-s)
        Thanks

      2. You need to use SEQUENCE instead, and not IDENTITY or AUTO. The IDENTITY, even on PostgreSQL, requires inserting the entity to know the identifier, hence the problems.

      3. The only suggestion would be to add PostgreSQL >= 7.2 to the list of databases that support IDENTITY(even if technically the implementation is using a SEQUENCE)

    1. The only suggestion would be to add PostgreSQL >= 7.2 to the list of databases that support IDENTITY(even if technically the implementation is using a SEQUENCE)

  2. Great Post,
    but I have a doubt. I have a Oracle table with an ID column with GENERATED ALWAYS AS IDENTITY NOT NULL. Can I use IDENTITY generator inside the hibernate configuration file?
    Thank you.

    1. For GENERATED ALWAYS AS IDENTITY NOT NULL, Oracle uses a SEQUENCE behind the scenes. It’s always better to use a dedicated SEQUENCE for each table, so you might want to consider using a SEQUENCE instead. The Oracle10gDialect doesn’t support IDENTITY anyway, so you can’t use it. You could use a select identifier generator which “retrieves a primary key, assigned by a database trigger, by selecting the row by some unique key and retrieving the primary key value”.

    1. You’d have to rely to JDBC or jOOQ for that. This way you can simply insert your data without having to call the getGeneratedKeys() method. If you can handle batch inserts without knowing what the identifiers are, then you should be fine with the JDBC approach.

  3. Hi Vlad,

    Is it possible to use IDENTITY/SEQUNCE/TABLE with in a composite primary key for one of the fields. In fact, say, we have a composite primary key class EmployeePK that has two fields “ID” and “NAME” and want to make ID as an one of IDENTITY/SEQUNCE/TABLE types and “NAME” field will be user assigned. I understand it definitely is not a good schema design but my intent is to understand whether we can have IDENTITY/SEQUNCE/TABLE with in a composite primary key for one of the fields and other user assigned?

    When I tried the above scenario with IDENTITY column I got an error message ” Declaring class is not found in the inheritance state hierarchy: com.demo.domain.EmployeePK”. I wanted to make sure that this exception is expected w.r.t. the above scenario.

      1. In fact, I am not using any inheritance here. I have only one class Employee and EmployeePK that contains its composite PK. And when I tried saving employee object I am getting following exception ”Declaring class is not found in the inheritance state hierarchy: com.demo.domain.EmployeePK”. So that is where I am wondering if it possible to use IDENTITY/SEQUNCE/TABLE with in a composite primary key for one of the fields and the other field will be user assigned?

  4. Hi Vlad,

    In the SequenceIdentifier class, I think the parameter name for sequence name should be name = “sequence_name” instead of name = “sequenceName”

  5. Please include database table schema for TABLE strategy. It took me a lot of time to figure out that the table need to have two column one for sequence_name and another for value.
    I am using following code:

    @GenericGenerator(name = "new_seq", strategy = "enhanced-table", parameters = {
            @org.hibernate.annotations.Parameter(name = "table_name", value = "sequence_table"),
            @org.hibernate.annotations.Parameter(name = "value_column_name", value = "sequence_value"),
            @org.hibernate.annotations.Parameter(name = "segment_column_name", value = "sequence_name"),
            @org.hibernate.annotations.Parameter(name = "segment_value", value = "new_seq")
    
    })
    @GeneratedValue(generator = "new_seq", strategy=GenerationType.TABLE)
    
  6. Hi Vlad,I was about to buy your book from leanpub but the note “This book is 90% complete” stopped me in tracks. Are you still working to complete the book ?

    1. Yes, I am. I still have to write the part about jOOQ and then it’s done. If you buy it now, you’ll get that update for free. You should also know that the price grows with the content, so when it’s done it will be more expensive that it is now. So, you can take advantage of the pre-sale deal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s