49

The Hibernate documentation says:

Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.

But all my entities have this configuration:

@Id
@GeneratedValue(strategy = javax.persistence.GenerationType.IDENTITY)
private Integer id;

When I'm using this identity above So

  1. what's the problem with IDENTITY ?
  2. is the batch insert disabled?
  3. How can I solve this?
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
Hayi
  • 6,972
  • 26
  • 80
  • 139

1 Answers1

72

Transactional write-behind

Hibernate tries to defer the Persistence Context flushing up until the last possible moment. This strategy has been traditionally known as transactional write-behind.

The write-behind is more related to Hibernate flushing rather than any logical or physical transaction. During a transaction, the flush may occur multiple times.

The flushed changes are visible only for the current database transaction. Until the current transaction is committed, no change is visible by other concurrent transactions.

IDENTITY

The IDENTITY generator allows an int or 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.

TABLE

The only solution would be to use a TABLE identifier generator, backed by a pooled-lo optimizer. This generator works with MySQL too, so it overcomes the lack of database SEQUENCE support.

However, the TABLE generator performs worse than IDENTITY, so in the end, this is not a viable alternative.

Conclusion

Therefore, using IDENTITY is still the best choice on MySQL, and if you need batching for insert, you can use JDBC for that.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • thanks Vlad for the help i understand now how all that work ;) – Hayi Jan 02 '15 at 18:21
  • @Vlad, with `IDENTITY` generator it seems clear that `INSERTS` can't be batched, but what about `UPDATES`, can they be batched with using `IDENTITY` generator and `mysql`? After setting correct config params, of course. (`hibernate.jdbc.batch_size`, `hibernate.order_updates`, `hibernate.jdbc.batch_versioned_data`). I tried it, but its not working. P.S. I am not using `JOOQ` – zodi91 Jun 13 '17 at 21:23
  • As I explained in [my book](https://leanpub.com/high-performance-java-persistence?utm_source=stackoverflow&utm_medium=banner&utm_campaign=profile), UPDATE and DELETE statements are supported so it's either a configuration issue or you don't log statements properly. – Vlad Mihalcea Jun 14 '17 at 04:46
  • Probably it's ok. You will have to check how MySQL logging works when you send a batch of parameter values to a single PreparedStatement – Vlad Mihalcea Jun 15 '17 at 01:21
  • In your blog post(https://vladmihalcea.com/2015/03/18/how-to-batch-insert-and-update-statements-with-hibernate/), from where are you getting those logs? – zodi91 Jun 15 '17 at 09:35
  • If you are using mysql, you can use Mysql general query log with JDBC https://stackoverflow.com/questions/10903206/enabling-mysql-general-query-log-with-jdbc – Reva Jul 07 '17 at 05:07
  • I am using the GenerationType.AUTO and seeing the same issues. Do I need to use (SELECT MAX(PK) FROM TABLE) instead? – wheeleruniverse Mar 27 '18 at 17:10
  • My answer tells that there's no fix. IDENTITY disables JDBC batching for INSERT statements. Unless this is fixed in Hibernate, you will have to issue the INSERTs using plain JDBC or a different framework. – Vlad Mihalcea Mar 27 '18 at 18:00
  • @VladMihalcea, but in your [blogpost](https://vladmihalcea.com/hibernate-identity-sequence-and-table-sequence-generator/) you explain how to do batch inserts on Identity-generated entities or am I missing something? – gdrt Jan 21 '19 at 11:32
  • @gdrt The article explains that Hibernate disabled JDBC batching for INSERT statements when using IDENTITY. Search for `This restriction is hindering the transactional write-behind strategy adopted by Hibernate. For this reason, Hibernates cannot use JDBC batching when persisting entities that are using the IDENTITY generator`, and you'll see what I mean. – Vlad Mihalcea Jan 21 '19 at 11:44
  • @VladMihalcea, now I understand. Essentially, you wanted to show how it doesn't work. Thanks. Besides, if you are interested I just posted here somewhat distantly related [question](https://stackoverflow.com/questions/54291475/hibernate-selects-identity-column-values-before-every-insert) about Hibernate. – gdrt Jan 21 '19 at 13:56
  • @VladMihalcea : The newly assigned value is unknown prior to executing the INSERT statement. How does this affect the “transactional write behind” and what is the reason as to why the batching wont work without the assigned value. Please suggest. – Farhan stands with Palestine Jan 21 '19 at 20:33
  • @VladMihalcea : Few days back I could see that there was a huge discount. Is it applicable now? – Farhan stands with Palestine Jan 22 '19 at 08:54
  • @VladMihalcea : Please check your mailbox. – Farhan stands with Palestine Jan 22 '19 at 09:09
  • Hello, supposed we want to insert in this kind of table, and we don't care about the id, because we don't reuse it anywhere (we just insert), do we have an option to simply ignore the identity returned and to not break the batch process? – Thibaut Jul 15 '19 at 12:07
  • It does not matter if you need the id or not as Hibernate needs it to associate the provided entity with the current Persistence Context. If you want to use batch inserts with IDENTITY, you have to use JDBC or a framework like jOOQ. – Vlad Mihalcea Jul 15 '19 at 15:06
  • I personally don't see why would recommend JOOQ. Why not use the normal JDBC Template insert. – Makky Dec 30 '19 at 13:56
  • I used jOOQ, and I liked it. I wrote about it in my High-Performance Java Persistence book. While JDBC Template is fine for simple operations, jOOQ can do way much more, like dynamically building SQL queries in a type-safe manner. – Vlad Mihalcea Dec 30 '19 at 14:05
  • @VladMihalcea What about GUID generator so we can avoid jOOQ (ie. less tech in our tech stack == easier learning curve and faster team)? I wonder if batching with MySQL works then? – Dean Hiller Aug 15 '20 at 15:29
  • @VladMihalcea, does IDENTITY disables batch updates too or just batch inserts. For example, I have a tables in which I can only do select and update, not insert. Will batch update work with IDENTITY generation strategy? – Heril Muratovic Apr 29 '21 at 15:55
  • 2
    Only batching INSERT is affected. UPDATE and DELETE statements can be batched just fine with IDENTITY. – Vlad Mihalcea Apr 29 '21 at 16:55