MariaDB 10.3 supports database sequences

(Last Updated On: June 6, 2018)

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.

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

2 thoughts on “MariaDB 10.3 supports database sequences

  1. Can’t you run (untested, verify syntax):

    WITH RECURSIVE s(i) AS (
    SELECT 1
    UNION ALL
    SELECT i + 1 WHERE i < 3 — Bind value here
    )
    SELECT NEXTVAL(hibernate_sequence)

    Seems better to run this query only once, than running the other query hundreds of times. Needs to be benchmarked, of course. Probably, there’s a threshold for a number of sequence value generations, after which the recursive generation is better than the repeated query approach.

    1. Hibernate offers the pooled and pooled-lo to reduce the number of database sequence calls. So, if you are inserting lots of entities, those optimizers can surely help reduce response time.

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.