In the past (Hibernate 3.6.10) we used this tag to delegate the better strategy to Hibernate and worked (in MySQL used autoincrement, in Oracle sequence, etc):
@GeneratedValue(strategy = GenerationType.AUTO)
But Hibernate 5.3.10 always create a table called “hibernate_sequence” in databases like MySQL (it should use the AUTOINCREMENT). I’ve tried with IDENTITY and seems to work, even in Oracle but since 12c (previous Oracle versions does not support IDENTITY):
How can I choose the best generated value strategy foreach database? Is there any option which decides the best strategy depending on the database dialect, like in Hibernate 3.6.10?
Generally, a table based sequence strategy is the most efficient strategy you can use on MySQL which lacks support for native sequences. The performance advantage of this approach is visible when you use a proper allocation size for the sequence i.e. 50+ because then, Hibernate won’t have to send every insert immediately to obtain the primary key, and instead can cache a certain amount of id values which it can use without DB communication. Now, when you insert many records at once, and Hibernate can transparently re-order inserts if you configure it to do so, Hibernate will use JDBC batching if enabled. JDBC batching is a very efficient way to insert many records at once, but that only works when the primary key is not generated during an insert by the database.
So overall, if you don’t need high throughput insert performance, you can surely use a generated identity, but by default, Hibernate 5 chose to use make use of the generally more efficient and more portable approach which also enables other optimizations. Not sure if you can switch back to the old behavior somehow, but this should really be a conscious decision you make, so I’d recommend you to make this explicit everywhere.
The best approach would be using the better strategy for each database (autoincrement in MySQL and sequences in Oracle) and this was managed by Hibernate 5 pretty well. But in Hibernate 6 the AUTO strategy uses an hibernate_sequence for MySQL which cause performance problems. The IDENTITY strategy uses autoincrement in MySQL and a sort of autoincremnt in Oracle, but only for Oracle 12c and newer.
Keep on mind my application works with several databases, and right now it’s a problem because I can’t decide the best strategy for every database.
The “best” strategy depends on your access patterns like I tried to outline in my previous reply. Try reading that again to understand what the effects of using autoincrement are and how that might negatively affect performance.
Especially when your app works with several databases, it is much better to use a sequence style table based generator, because then you will have the same batching behavior across databases.
If you want to override the default strategy, you can provide a custom org.hibernate.boot.model.IdGeneratorStrategyInterpreter via org.hibernate.boot.MetadataBuilder#applyIdGenerationTypeInterpreter in a custom org.hibernate.boot.spi.MetadataBuilderInitializer.
Forget this article. The scenario, from what I understand, that it seems to compare is simply not realistic.
The article assumes N threads will try to insert 100 elements at the same time. Of course these threads will compete for the lock on the table sequence and this scenario will show that the strategy is “bad”, but this is not the real world.
In the real world, when you have such a scenario, you would increase the sequence allocation size to a higher value than 100, to be able to serve more threads with id values at the same time. But ask yourself, is that your scenario? Because I highly doubt it is.
For what it’s worth, when you do the same experiment with an allocation size of say 2000 (the whole test with 16 threads seems to ask for 1600 ids), the table generator will outperform the identity generator for sure, because then there is only an initial overhead, but all threads can make use of JDBC batching.
Try to think through it or even experiment with it and don’t just take everything you find online as granted
The last sentence is some of the best piece of advice in the world!
Anyway this person seems to have a lot of experience with Hibernate, but it’s true that many of this kind of information depends on your particular case, database and even the Hibernate version. So, I will make some test to choose the best strategy.
