Hibernate IDENTITY vs SEQUENCE entity identifier generators

Question

This article says:

Unlike identity, the next number for the column value will be retrieved from memory rather than from the disk – this makes Sequence significantly faster than Identity

Does it mean that ID comes from disk in case of identity? If yes, then which disk and how?

Using sequence, I can see in the log, an extra select query to DB while inserting a new record. But I didn't find that extra select query in the log in case of identity. Then how sequence becomes faster than identity?

Solution 1

Strategy used by sequence:

Before inserting a new row, ask the database for the next sequence value, then insert this row with the returned sequence value as ID.

Strategy used by identity:

Insert a row without specifying a value for the ID. After inserting the row, ask the database for the last generated ID.

The number of queries is thus the same in both cases. But, Hibernate uses by default a strategy that is more efficient for the sequence generator. In fact, when it asks for the next sequence value, it keeps th 50 (that's the dafault, IIRC, and it's configurable) next values in memory, and uses these 50 next values for the next 50 inserts. Only after 50 inserts, it goes to the database to get the 50 next values. This tremendously reduces the number of needed SQL queries needed for automatic ID generation.

The identity strategy doesn't allow for such an optimization.

Solution 2

The IDENTITY generator will always require a database hit for fetching the primary key value without waiting for the flush to synchronize the current entity state transitions with the database.

So the IDENTITY generator doesn't play well with Hibernate write-behind first level cache strategy, therefore JDBC batching is disabled for the IDENTITY generator.

The sequence generator can benefit from database value preallocation and you can even employ a hi/lo optimization strategy.

In my opinion, the best generators are the pooled and pooled-lo sequence generators. These generators combine the batch-friendly sequence generator with a client-side value generation optimization that's compatible with other DB clients that may insert rows without knowing anything about our generation strategy.

Anyway, you should never choose the TABLE generator because it performs really bad.

Comments

[cooper]

Thanks. In fact i too have gone through the links.if identity does not need that extra select query, then could you please clarify how sequence becomes faster than identity.

[Angga]

when in debug mode and use entity that have sequence for its id, i save(before commit) an entity and that entity already have id even before the row existed in the database.

[Sebastian]

I would also add that SQL Server "reserves" a batch of sequence numbers in its cache (thus memory). By default it's 50 (that's probably also the reson why NH developers picked this value for their purpose). When those 50 numbers got used, SQL Server needs to update its system tables that store last used number in a given sequence, in order to "reserve" next batch of numbers - this is a disk operation. Identities are also cached but the cache size is 5x smalled (counts 10 numbers) and thus the "sequence generator" touches disk more frequently.

[cooper]

@JB, thanks .I think this is what i was looking for.But what is the meaning of - "ask the database for the last generated id" in case of Identity, is it like auto-increment.Can i say there is a data transfer also in the reverse direction, from db to application(round trip), in case of Identity and it is only from application to db(one way) in case of Sequence(assuming we don't have allocationSize defined for sequence).Probably i am confused, please rectify.I believe, without allocationSize every db hit will trigger a Select to get the sequence.

[JB Nizet]

I don't know the exact low-level mechanism used by every database. But with the ones I've used, an additional roundtrip to the database is needed to get the last auto-generated ID.

[Louis Huh]

What about TABLE generator with allocationSize config? Is it bad too?

[Louis Huh]

No, I haven't yet. I'm going to use aurora and it doesn't support sequence. so I study table sequence... thanks for your articles.