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

None

Activity

Show:
Vlad Mihalcea
January 24, 2017, 12:56 PM
Edited

As a workaround, use the native identifier generator as explained in this article.

Robert Oschwald
August 10, 2017, 3:19 PM
Edited

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

Cavan Morris
April 7, 2018, 9:33 PM
Edited

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.

Vlad Mihalcea
April 13, 2018, 4:45 PM
Edited

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

Unassigned

Reporter

Vlad Mihalcea

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

Priority

Major
Configure