Hibernate throws PK violation error

Question

I've been using hibernate with jboss 4.2.3 and everything was working, now i migrated the code to Jboss 7.1.1 and suddenly i start getting :

Caused by: org.hibernate.exception.ConstraintViolationException: ORA-00001: unique constraint (OBLICORE.PK_ACE_WORKERS_QUEUE_STATS_ID) violated

Also the generated ID's are negative.

The entity that fails is defined as such:

@Id
@SequenceGenerator(name = "SEQ_ACE_WORKERS_QUEUE_STATS_ID", sequenceName = "SEQ_ACE_WORKERS_QUEUE_STATS_ID", allocationSize = 500)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_ACE_WORKERS_QUEUE_STATS_ID")
@Column(name = "ID")
private long Id;

I checked the sequence in Oracle and it seems O.K (Like i said, it worked before with jboss 4.2 and nothing changed on the DB side since the migration).

I tried writing Hibernate query logs but couldn't locate that query and I've also logged the specific call that persist this class and saw that it only get called once.

Answer 1

Check this question: hibernate oracle sequence produces large gap

It must be Hibernate's sequence generator which is defaulting to the Hi/Lo algorithm, and the returned values overflowing. You can try using a hibernate-specific annotation to default to the older behaviour GenericGenerator(name="blah", strategy="sequence"), or set allocationSize=1.

If you are relying on your sequence incrementing by some value larger than 1, you'll have to use a different generator. Or maybe it's enough to set hibernate.id.new_generator_mappings to false, but that is in the scope of a new question.

Answer 2

When we changed Hibernate to use the new generator, I used the following script to fix the sequences:

  DECLARE
    v NUMBER;
  BEGIN
    FOR r IN (select sequence_name from user_sequences) LOOP
      EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| r.sequence_name ||' INCREMENT BY 50';
      EXECUTE IMMEDIATE 'SELECT '|| r.sequence_name ||' .NEXTVAL FROM DUAL' INTO v;
    END LOOP;
  END;
  /

If your allocationSize is 500, you should change the "INCREMENT BY 50" to "INCREMENT BY 500".