Switch to native identifier strategy even when using the new identifier generators
Description
Currently, if the hibernate.id.new_generator_mappings is set to false, @GeneratedValue(strategy = GenerationType.AUTO) is mapped to native. If this property is true (which is the deafult value in 5.x), the @GeneratedValue(strategy = GenerationType.AUTO) is always mapped to SequenceStyleGenerator.
For this reason, on any database that does not support sequences natively (e.g. MySQL) we are going to use the TABLE generator instead of IDENTITY.
However, TABLE generator, although more portable, uses a separate transaction every time a value is being fetched from the database. In fact, even if the IDENTITY disables JDBC batch updates and the TABLE generator uses the pooled optimizer, the IDENTITY still scales better.
Environment
Activity
As a workaround, use the native identifier generator as explained in this article.
Worse than the performance penalty is the fact that the sequence table is not holding a PK, so sequence table generation fails on Galera Cluster (which needs a PK on every table) with:
java.sql.SQLSyntaxErrorException: This table type requires a primary key.
Therefore, I would see this ticket as a bug on normal columns. If the Entity is defined as TABLE_PER_CLASS, a sequence is needed to guarantee the primary key uniqueness in the hierarchy. For the hibernate_sequence table missing a PK on MySQL / Galera, see
Hello @mih_vlad,
I see this issue is assigned to you. Are you currently working on a fix for this? If so I have some code that you might find useful to provide a better sequence emulation than the current TABLE generator is using.
It uses separate rows in the sequence table for each entity so different entities are not competing for a lock. It also uses a buffer and a variable fetch size configurable for each entity so that in high throughput systems a block of IDs can be requested with a single DB query and then dolled out as needed.
I have been using this code for over 10 years in various high throughput applications with great success. With minor modification if could be generalized for other Hibernate MySQL users.
Let me know if you are interested and I can provide you with the code.
The issue was fixed and sent via this PR.
The problem is that the change might be too unexpected for devs who wanted to have AUTO as TABLE. So, I can add a property which, when enabled, switched to IDENTITY.
As for your improvement, please create a new Jira issue and send us a Pull Request. Thanks.
Assignee
Reporter
Fix versions
Labels
backPortable
Suitable for new contributors
Requires Release Note
Pull Request
backportDecision
Affects versions
Priority
Major