Auto Generating Keys
Identifiers (keys) uniquely identify each persistent entity and the Jakarta Persistence API (JPA) allows them to be generated automatically when an entity is persisted. One function of the NuoDB Hibernate Dialect is to generate the appropriate NuoDB SQL to do this.
Auto-generated keys typically rely on using Sequences in a specific way, a feature Hibernate calls pooled sequences.
A client application uses the sequence to get a batch (pool) of values (keys) that it can call its own.
For example, the client might get 100 values to use.
Each time an entity is persisted the next value from the pool is used as the unique id.
When the pool is exhausted, another batch of keys is fetched from the sequence.
Concurrent clients get different, non-overlapping sets of key values from the shared sequence allowing them to persist entities in parallel knowing the keys they allocate are unique.
Using pooled sequences avoids each client having to get a new key value every time it persists an entity and avoids a popular sequence becoming a hot-spot in the database.
NuoDB uses a similar mechanism to support multiple TEs running in parallel on shared tables.
Each TE gets a batch (pool) of values (keys) to use from a shared sequence.
Each TE can allocate keys from its pool without accessing the sequence or communicating with any other TE.
This mechanism avoids multiple TEs contending for the shared sequence.
Unfortunately managing the pool in the TE rather than the client gives rise to different semantics and so NuoDB’s Hibernate Dialect does not support pooled sequences.
This section discusses NuoDB’s auto-generation SQL syntax, our recommendations for key generation and, for those interested, how each of Hibernate’s auto-generation strategies works with NuoDB and why we recommend the
NuoDB SQL supports the following syntax variations for automatic identifier generation:
To always generate the identifier automatically, use
GENERATED ALWAYS AS IDENTITY:
CREATE TABLE t1 (id BIGINT GENERATED ALWAYS AS IDENTITY ...
To generate identifies automatically, but only when the value is not provided in the
CREATE TABLE t1 (id BIGINT GENERATED BY DEFAULT AS IDENTITY ...
In both cases NuoDB automatically generates a sequence, called
t1$IDENTITY_SEQUENCE, that is used to generate the keys.
To define the sequence manually, see below.
NuoDB SQL supports auto-generation of keys of any primitive type
but in practice id columns are usually of type
In all cases the key is a 64-bit integer stored appropriately for the type.
JPA defines four auto-generation strategies but we only recommend one of them.
For further details and background, each is discussed below.
In particular, unlike with most other RDBMS, we do not recommend using the
@GeneratedValue(strategy = GenerationType.IDENTITY)
Automatically implemented using a dedicated sequence for each table.
You can override this by defining the table manually if you prefer - see recommendation 2.
Key generation occurs in the TE and you get full advantage of NuoDB’s distributed sequence implementation (see discussion on SEQUENCE strategy).
IDcolumn is declared as
GENERATED BY DEFAULTso you can always provide a key explicitly if you wish.
This option may preclude batched inserts (Hibernate 5.3 or earlier) - see below.
CREATE SEQUENCE seq1 START WITH 1000, QUANTUM SIZE 200; CREATE TABLE tab1 ( id BIGINT GENERATED BY DEFAULT AS IDENTITY(seq1), ...);
If you prefer, use
id BIGINT GENERATED ALWAYS AS IDENTITY(seq1).
Another reason to control the underlying sequence might be to ensure unique identifier values across multiple tables (for example when implementing a one-one relationship). Manually declare 2 or more tables sharing the same sequence:
CREATE SEQUENCE seq1 ...; CREATE TABLE tab1 ( id BIGINT GENERATED ALWAYS AS IDENTITY(seq1), ...); CREATE TABLE tab2 ( id BIGINT GENERATED ALWAYS AS IDENTITY(seq1), ...); ...
Unless you have a particular reason for not doing so, the
IDENTITY strategy (possibly using manual table definitions) is the best overall implementation.
Here is how each of the four JPA strategies is implemented when using NuoDB, with points to consider for each one.
The example assumes you have a
Long id that is stored as a
Suppose you have the following identifier declaration in your Java entity class:
private Long id; // Long not long, so it can be null for non-persistent (transient) entities
If you use Hibernate’s ability to run DDL and create tables for you, NuoDB’s Hibernate dialect creates tables with the ID column declared as
ID BIGINT NOT NULL.
In addition Hibernate creates a sequence called
hibernate_sequence to generate the identifier values automatically upon insert.
|This is our recommended strategy.|
If you specify
strategy=GenerationType.IDENTITY and request Hibernate to create tables for you, NuoDB’s Hibernate dialect creates tables with the
ID column defined as
ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL.
The identifier is (typically) specified in Java as an
BigDecimal or a
IDshould always be an object, not a primitive type, so it can be null if the entity has no
The identifier is a positive 64-bit integer, cast as necessary for the
ID column type.
Even when the identifier is a string it still holds an auto-generated numeric value.
Sequences in NuoDB are optimized for use in a distributed system.
Instead of an
INCREMENT BY option, it supports a
This allows each TE to get batches of values to use, reducing contention for the shared sequence.
See CREATE SEQUENCE for full details.
As a result, the sequence values returned by
SELECT NEXT VALUE depend on which TE you are connected to and how many other clients that TE is also supplying values to.
Two TEs share a sequence with default quantum size of 100.
Both get 100 values to use.
TE1 gets 1-100 and TE2 gets 101-200.
TE1 is serving two clients fetching values from the sequence.
So the values returned to client-1 might be
1, 2, 5, 7… because the other client is getting intermediate values.
If client-1 is multi-threaded and getting values from both TE1 and TE2, the values returned might be
Hibernate uses sequences with increment greater than 1 in a similar way to reduce round trips to the database.
If a sequence has increment 100, and
SELECT NEXT VALUE returns 200, then Hibernate knows that any other client getting a value from that sequence will get a value > 299.
Therefore it has a "pool" of contiguous values 200-299 that it can use to supply unique keys.
Hibernate calls these Pooled Sequences and they are (from V5) the default implementation for the
When a new entity is persisted, the next value from the pool is set as the id in the entity and in the new row inserted into the corresponding table.
As you can see from the example, this assumption of a contiguous batch of values does not hold with NuoDB so our dialect tells Hibernate that it does not support Pooled Sequences.
As a result, Hibernate chooses an alternative implementation using an underlying table to simulate a pooled sequence (equivalent to
strategy=GenerationType.TABLE) requiring two database accesses to persist one entity (just like the
AUTO strategy) plus an extra write per pool (to increment the
next-value held in the table for the next pool).
This is the explicit JPA strategy to use a table to simulate a sequence. It allows multiple entities to use the same table to implement their "sequence". Each pseudo-sequence is defined by a different row in the same table.