Introduction

Traditionally, both MySQL and MariaDB relied on AUTO_INCREMENT columns to generate an IDENTITY Primary Key. Although IDENTITY columns are very efficient in generating the Primary Key value, when it comes to using JPA and Hibernate, the IDENTITY generator prevents us from using JDBC batch inserts.

To automatically enroll multiple INSERT, UPDATE or DELETE statements, Hibernate requires delaying the SQL statement until the Persistence Context is flushed. This works very well for the SEQUENCE identifier since the entity identifier can be fetched prior to executing the INSERT statement.

However, for IDENTITY columns, the only way to know the entity identifier is if we execute the SQL INSERT statement. And, Hibernate needs the entity identifier when persisting an entity because otherwise, it cannot build the key which is used for locating an entity in the currently running Persistence Context.

IDENTITY and JDBC batch inserts

Considering we have the following Post entity:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

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

    private String title;

    //Getters and setters omitted for brevity
}

If we enable JDBC batching using the following configuration property:

<property
    name="hibernate.jdbc.batch_size"
    value="10"
/>

And persist 3 Post entities:

for (int i = 0; i < 3; i++) {
    Post post = new Post();
    post.setTitle(
        String.format(
            "High-Performance Java Persistence, Part %d", 
            i + 1
        )
    );
    entityManager.persist(post);
}

Hibernate will generate the following SQL statements:

Query:["
    insert into post (title) values (?)
"], 
Params:[
    (High-Performance Java Persistence, Part 1)
]

Query:["
    insert into post (title) values (?)
"], 
Params:[
    (High-Performance Java Persistence, Part 2)
]

Query:["
    insert into post (title) values (?)
"], 
Params:[
    (High-Performance Java Persistence, Part 3)
]

So, the SQL INSERT statements were not batched being executed one after the other.

Using the SEQUENCE generator

Now, let’s see what happens if we change the identifier strategy to SEQUENCE:

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

Prior to MariaDB 10.3

Prior to MariaDB 10.3, using the SEQUENCE or the AUTO generator would have been a really bad idea because Hibernate would use the TABLE generator instead.

As I explained in this article, the TABLE identifier generator should be avoided since it performs badly and can lead to scalability issues.

When rerunning the previous test case while using the MariaDB102Dialect, Hibernate generates the following SQL statements:

select next_val as id_val from hibernate_sequence for update
update hibernate_sequence set next_val= 2 where next_val=1

select next_val as id_val from hibernate_sequence for update
update hibernate_sequence set next_val= 3 where next_val=1

select next_val as id_val from hibernate_sequence for update
update hibernate_sequence set next_val= 4 where next_val=2

Query:["
    insert into post (title, id) values (?, ?)
"], 
Params:[
    (High-Performance Java Persistence, Part 1, 1), 
    (High-Performance Java Persistence, Part 2, 2), 
    (High-Performance Java Persistence, Part 3, 3)
]

As you can see, Hibernate used the TABLE generator and the hibernate_sequence database table is used as an identifier sequence generator.

Even if JDBC batch inserts were used this time, the identifier generation requires row-level locks taken on the hibernate_sequence as well as a separate database connection to ensure that the identifier generation ransaction is committed prior to the one where the entities are inserted.

Since MariaDB 10.3

Luckily, MariaDB 10.3 adds support for database sequences, so if we change the Hibernate Dialect to MariaDB103Dialect and rerun our test case, we get the following SQL statements:

select nextval(hibernate_sequence)

select nextval(hibernate_sequence)

select nextval(hibernate_sequence)

Query:["
    insert into post (title, id) values (?, ?)
"], 
Params:[
    (High-Performance Java Persistence, Part 1, 1), 
    (High-Performance Java Persistence, Part 2, 2), 
    (High-Performance Java Persistence, Part 3, 3)
]

This time, not only that we benefit from JDBC batch inserts, but we can use a database sequence to generate the table identifiers. However, unlike the TABLE generator, database sequences are very fast and are non-transactional too, meaning that there is no need for a separate database connection.

More, the AUTO generator will fallback to SEQUENCE from MariaDB 10.3, so AUTO is no longer a bad choice for MariaDB 10.3 as it is for MySQL.

Cool, right?

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

Conclusion

If you are using MariaDB with JPA and Hibernate, upgrading to 10.3 is going to be really beneficial. Looking forward to MySQL adding support for database sequences in future.

Insert details about how the information is going to be processed

DOWNLOAD NOW