hibernate oracle sequence produces large gap

Asked 17/3, 2011 at 22:38 Answered 30/8, 2018 at 12:5

Solved oracle hibernate sequence gaps-in-data

I am using hibernate 3 , oracle 10g. I have a table: subject. The definition is here

CREATE TABLE SUBJECT
    ( 
     SUBJECT_ID NUMBER (10), 
     FNAME VARCHAR2(30)  not null, 
     LNAME VARCHAR2(30)  not null, 
     EMAILADR VARCHAR2 (40),
     BIRTHDT  DATE       not null,
     constraint pk_sub primary key(subject_id) USING INDEX TABLESPACE data_index
    ) 
;

when insert a new subject, sub_seq is used to create an subject id, the definition is here

create sequence sub_seq
       MINVALUE 1 
       MAXVALUE 999999999999999999999999999 
       START WITH 1
       INCREMENT BY 1 
       CACHE 100 
       NOCYCLE ;

the Subject class is like this:

@Entity
@Table(name="ktbs.syn_subject")
public class Subject {

    @Id 
    @Column(name="subject_id")
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SUB_SEQ")
    @SequenceGenerator(name="SUB_SEQ", sequenceName = "SUB_SEQ")
    private long subjectId;
    private String fname;
    private String lname;
    private String emailadr;
    private Date birthdt;
}

in the subject table , there have been 4555 subjects in the database loaded by plsql scripts from excel and the sub_sequence worked fine. subject ids range from 1--4555.

however, when i added a subject from my application using hibernate, the sequence number jumped to 255050. After several days running, the subject ids generated by hibernate look like this

There are several large gaps: 4555 to 255051, 255067 to 260051, 265057 to 270051

this is a waste and not a desired behavior.

does anyone know why this happens and hot to fix it

Thanks

Coleridge answered 17/3, 2011 at 22:38 Comment(0)

I think that the problem comes from the fact that the sequence generator is not really a sequence generator, but a sequence hilo generator, with a default allocation size of 50. as indicated by the documentation : http://docs.jboss.org/hibernate/stable/annotations/reference/en/html_single/#entity-mapping-identifier

This means that if the sequence value is 5000, the next generated value will be 5000 * 50 = 250000. Add the cache value of the sequence to the equation, and it might explain your huge initial gap.

Check the value of the sequence. It should be less than the last generated identifier. Be careful not to reinitialize the sequence to this last generated value + 1, because the generated valus would grow exponentially (we've had this problem, and had negative integer ids due to overflow)

Veranda answered 17/3, 2011 at 23:22 Comment(3)

thanks. yes, that may be the problem. so hibernate here in my case dose not use oracle defined sequence object? how to fix this problem? – Coleridge 17/3, 2011 at 23:31

you also get gaps from restarts if I remember when setup properly as the sequence in the db is 1, 2, 3, 4, and if allocation size is 500, then when it gets next seq, 500*1=500 and when it finally gets to 999, it calls get next sequence retrieving 2(or 3 if another process got 2 already), and then 500*2=1000 all the way to 1499. Server restarts after only using 1 or 2 values in the 500 would cause a gap. – Ecstasy 12/12, 2013 at 2:57

oh, and when you call persist and don't flush(if in manual mode) it uses up ids from the sequence that are never saved to the database as well(There are plenty of reasons as I just notied you are not using allocationSize which we use ALOT to prevent the constant db interaction on insert operations as it is more roundtrip work then) – Ecstasy 12/12, 2013 at 2:59

Agree with JB. But still thanks to PaulJ.

To be more specific to my annotation code below:

@Entity
@Table(name="ktbs.syn_subject")
public class Subject {

  @Id 
  @Column(name="subject_id")
  @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SUB_SEQ")
  @javax.persistence.SequenceGenerator(name="SUB_SEQ", sequenceName = "SUB_SEQ")
  private long subjectId;
  private String fname;
  private String lname;
  private String emailadr;
  private Date birthdt;
}

If you use javax.persistence.SequenceGenerator, hibernate use hilo and will possibly create large gaps in the sequence. There is a post addressing this problem: https://forum.hibernate.org/viewtopic.php?t=973682

There are two ways to fix this problem

In the SequenceGenerator annotation, add allocationSize = 1, initialValue= 1

instead of using javax.persistence.SequenceGenerator, use org.hibernate.annotations, like this:

@javax.persistence.SequenceGenerator(
    name = "Question_id_sequence", 
    sequenceName = "S_QUESTION"
)

@org.hibernate.annotations.GenericGenerator(
    name="Question_id_sequence", 
    strategy = "sequence", 
    parameters = { 
        @Parameter(name="sequence", value="S_QUESTION") 
    }
)

I have tested both ways, which works just fine.

Coleridge answered 17/3, 2011 at 23:58 Comment(0)

Actually having allocationSize=1 is fine if your sequence INCREMENT VALUE is 1 and you do not have the need of persisting a lot of entities. However if you want to persist thousands or millions of records, the above setting could become a performance bottleneck since every save need to fetch a id hence need a db read.

To Solve this problem, we need to set the allocationSize to something like 500 and sequence INCREMENT VALUE in DB also to 500, then most important add a hibernate setting hibernate.id.new_generator_mappings to ask it to use the new sequence generator implementation, here i assume you set your hibernate properties in a java Config class:

properties.setProperty("hibernate.id.new_generator_mappings", Boolean.toString(true));

This way, Hibernate will use SequenceStyleGenerator rather than the old SequenceHiLoGenerator to generate the ids. The SequenceStyleGenerator is more jpa and oracle friendly. It generates identifier values based on an sequence-style database structure. Variations range from actually using a sequence to using a table to mimic a sequence.

Look at my post for more detail if you are in the same boat:

vcfvct.wordpress.com/2016/04/23/jpa-sequencegenerator-with-allocationsize-1-performance-tuning/

Broadminded answered 23/4, 2016 at 21:42 Comment(0)

If you read the following link, you will see that the problem is caused by the CACHE setting on your sequence creation command. Removing the cache setting will solve the problem to a degree - but does not take into account the possiblity of rollbacks, etc.

Link is: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:369390500346406705

The only way to resync your sequences now is to re-create the sequence, rename the current table and create the table again and then re-insert the records from the old table into the new table.

NOTE: The cache value for sequences is useful for large loads where 'x' sequence values are allocated at once. If you are using a transaction system where you do one insert at a time - then caching is not useful (or I should say - I've never found it useful).

NOTE: This is my understanding of the cache option for sequences. You can look up the Oracle Documentation on CREATE SEQUENCE commands for more info. But the link above should provide a reasonable answer to your question.

Thanks. Paul

Gracia answered 17/3, 2011 at 22:45 Comment(4)

thanks. but the gap is 10000 and the defined gap is 100. Is it related to hibernate settings? – Coleridge 17/3, 2011 at 22:59

Each time you are doing an insert it is allocating 100 sequence values. So a gap of 10,000 is only 1,000 or 100 records being inserted. You need to read up on sequence caches for more details - but the main point is that you can't rely upon a sequence at all for maintaining a sequential set of values as gaps will ALWAYS be present regardless of the cache value. – Gracia 17/3, 2011 at 23:1

I just issue the query from sql developer select sub_seq.nextval from dual; it shows 5408. which means hibernate dose not use the oracle sequence object sub_seq. i suspect there is something wrong with the hibernate settings – Coleridge 17/3, 2011 at 23:13

See my answer. It's dut to the fact that sequence generator uses hilo. – Veranda 17/3, 2011 at 23:23

Another solution is:

Use 'GenerationType.AUTO' instead of 'GenerationType.SEQUENCE' as strategy for @GeneratedValue, as below;

@Id
@SequenceGenerator(name = "studentId", sequenceName = "student_Id")
@GeneratedValue(strategy = GenerationType.AUTO, generator="studentId")  
private int studentId;

Jsandye answered 22/11, 2012 at 18:15 Comment(1)

Does not work for me with Hibernate 4 & JPA. Setting allocationSize= 1 does. – Foley 4/6, 2020 at 12:12

One solution to this we can configure sequence generator with allocationSize as:

@SequenceGenerator(name = "gen_name", sequenceName = "seq_name", allocationSize= 1)

Berar answered 30/8, 2018 at 12:5 Comment(1)

This is not sufficient, possibly by quite a lot, if the table is slow-growing. If Oracle's sequence cache is set to e.g. 20, and only ~1 is added each week, and the DB is brought down for maintenance each week, you will still have a gap of 19 IDs between adjacent records. To fix this, set the sequence cache size < the number of rows added between maintenance, or set cache size to NOCACHE. – Algol 7/5, 2019 at 0:8

The most successfull answer would be:

@Id
@SequenceGenerator (name = "id_sequence", sequenceName = "sq50")
@GeneratedValue(strategy = GenerationType.AUTO, generator = "id_sequence")
public int getId() {
return id;
}

Pence answered 23/5, 2013 at 11:46 Comment(0)

As said here, try to adjust your SequenceGenerator.allocationSize with your database sequence INCREMENT BY number.

Calvados answered 5/2, 2015 at 9:4 Comment(0)

I had similar issues. sequence generator and sequence hilo generator are quite similar but have differences. In hibernate 3, hilo generator multiplies with default value 50. Therefore no need to increment DB sequence. On the other hand, later versions of hibernate uses sequence generator by default. Therefore DB increment by 50 is required.

https://access.redhat.com/documentation/en-US/JBoss_Enterprise_Application_Platform/6.3/html/Migration_Guide/Preserve_the_Existing_Behavior_of_the_Hibernate_Identity_Auto_Generated_Value1.html

I had this issue which have multiple hibernate versions (3 and 5). Same configuration worked fine (incremented by 1 in DB). But failed in hibernate 5. Therefore I update my persistence.xml as below. This ensures hilo generation

        <property name="hibernate.id.new_generator_mappings" value="false" />

Classicize answered 5/5, 2016 at 10:51 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags