CODEWITHSUNDEEP
hibernatespringdb2sequence
Juha Syrjälä
Juha Syrjälä

Reputation: 34261

Hibernate and strange behavior with DB2 sequences

I am using Hibernate with Spring and DB2. I am using sequences to generate primary key for entities. All entities use the same sequence HIBERNATE_SEQUENCE, which is the hibernate default.

The problem is that values that end up into primary keys are about 10 times higher than those returned by the HIBERNATE_SEQUENCE.

For example this situation just after a new row is inserted to tbl:

select max(id) as primary_key, nextval for hibernate_sequence sequence_value from tbl ;

primary_key sequence_value
501483661   50148373

I have mapped primary key like this, in super class for all entities:

@MappedSuperclass
public class AbstractEntity implements Serializable {
   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE)
   private Integer id;

I'd like that hibernate uses those values it fetches from the sequence, not sequence values multiplied by 10. What is the correct way to do this?

Upvotes: 1

Views: 7454

Answers (3)

user3248616
user3248616

Reputation: 11

I solved this problem setting allocationSize to 1 in @SequenceGenerator annotation.

Example:

@MappedSuperclass
public class AbstractEntity implements Serializable {
   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_GENERATOR")
   private Integer id;

and the entity:

@SequenceGenerator(name = "SEQ_GENERATOR", sequenceName = "MY_SEQUENCE", allocationSize = 1)
public class MyEntity extends AbstractEntity {

Upvotes: 1

Juha Syrjälä
Juha Syrjälä

Reputation: 34261

Hibernate seems to do following:

When hibernate needs a primary key it will fetch the value from sequence. Hibernate will generate several primary key values from single sequence values. For example hibernate will keep a internal counter whose value is appended to sequence value to obtain the primary key value. When the internal counter hits its limit the counter is reset, a new value from sequence is obtained and primary key process starts all over again.

For example:

  1. Value obtained from sequece is 123. The obtained sequence value is stored per session.
  2. For current session the generated primary keys are 1230, 1231, 1232, 1233, ..., 1238, 1239. A counter value is concatenated to sequence value obtained in step 1. A key is generated when needed.
  3. Now primary key generation process start all over. Goto 1.

This causes following effects:

  • database sequence value is effectively multiplied by 10
  • Hibernate does not have to make a database read for every DB insert it makes. The above algorithm cuts the number of sequence reads down to 10% (when doing a lot of inserts in single session).
  • every other non-hibernate application must use similar algorithm to generate primary keys from sequence, otherwise there will be primary key conflicts at some point

To make hibernate use actual values obtained from sequence, this mapping can be used:

@Id
@GeneratedValue(generator="hibernate_sequence")
@GenericGenerator(strategy="sequence", name="hibernate_sequence")
private Integer id;

Upvotes: 2

Brian Deterling
Brian Deterling

Reputation: 13724

There are a few options on the DB2 create sequence command that might affect this. INCREMENT_BY says how much to increase the value with each call to nextval. CACHE with NO_ORDER reserves a certain number of values so if multiple connections are using the same sequence they can get values faster at the cost of the values being out of order. I would check to see how the sequence was created first before digging into Hibernate. From looking at the Hibernate code, it's pretty straightforward - look at DB2Dialect and you can see the sql it uses to get the sequence value.

Upvotes: 0

Related Questions