MariaDB 10.3 supports database sequences
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
