[SOLVED] Hibernate Sequence Generator is not Consistent

Hibernate Sequence Generator is not Consistent

I have a table with promotion id annotated as

@SequenceGenerator(name="GEN_PROMID", sequenceName="SEQ_PROMOTIONID", allocationSize=1)
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="GEN_PROMID")
@Column(name="PROMOTIONID")
private Long promotionid;

but even though allocationSize is specified as 1, hibernate increases the numbers inconsistently. The following are recent descending values for promotionid in the DB

I read somewhere that Hibernate might imploy hilo seq_hilo algotihm and org.hibernate.id.SequenceHiLoGenerator generator even if @SequenceGenerator is defined. Also read in the following link that we could use @GenericGenerator to resolve this problem in the link

Can someone throw detailed info in this context? The @GenericGenerator syntax doesn't look plain. Shall @SequenceGenerator be used or not. Sometimes @SequenceGenerator works perfectly and generates correct primary keys.

PS. I'm using Hibernate 3.5 and using Oracle 11g DB

EDIT

Sequence code -

CREATE SEQUENCE SEQ_PROMOTIONID
    INCREMENT BY 1
    START WITH 100;

EDIT 2

More analysis shows that at least a value divisible by 20 is "always" inserted. Since Oracle's sequence has default caching of 20, it looks like some collision between normal increment of hibernate and the cache value. Also observed that when the insertions had a time gap between them then it used to go to next value divisible by 20 otherwise increased properly to just 1

1440    12-NOV-14 09.33.09.686000000 AM
1420    07-NOV-14 07.21.41.238000000 AM
1407    03-NOV-14 11.40.41.508000000 AM
1406    03-NOV-14 11.31.37.341000000 AM
1405    03-NOV-14 04.57.53.356000000 AM
1404    03-NOV-14 04.56.39.074000000 AM
1403    03-NOV-14 04.55.17.741000000 AM
1402    03-NOV-14 04.30.59.980000000 AM
1401    03-NOV-14 04.27.14.016000000 AM
1400    03-NOV-14 04.19.23.736000000 AM
1380    27-OCT-14 11.06.33.360000000 AM
1360    17-OCT-14 11.59.15.738000000 AM
1342    15-OCT-14 01.57.50.253000000 PM
1341    15-OCT-14 01.55.39.173000000 PM
1340    14-OCT-14 07.07.14.283000000 AM
1320    10-OCT-14 10.41.04.766000000 AM
1305    07-OCT-14 11.08.10.388000000 AM
1304    07-OCT-14 05.00.50.295000000 AM
1303    07-OCT-14 04.59.01.434000000 AM
1302    06-OCT-14 11.34.43.012000000 AM
1301    06-OCT-14 11.31.18.855000000 AM
1300    06-OCT-14 11.27.16.237000000 AM
1280    04-OCT-14 04.47.40.391000000 AM
1261    01-OCT-14 05.09.06.291000000 PM
1260    01-OCT-14 10.18.41.060000000 AM
1241    22-SEP-14 07.04.45.593000000 AM
1240    22-SEP-14 04.57.25.289000000 AM
1220    19-SEP-14 06.55.31.450000000 AM
1200    16-SEP-14 09.03.04.763000000 AM
1181    10-SEP-14 07.44.04.115000000 AM
1180    08-SEP-14 11.05.30.590000000 AM
1168    04-SEP-14 05.09.46.000000000 AM
1167    02-SEP-14 07.47.52.454000000 AM
1166    02-SEP-14 07.46.52.043000000 AM
1165    02-SEP-14 07.45.38.323000000 AM
1164    02-SEP-14 07.43.27.562000000 AM
1163    02-SEP-14 07.41.11.702000000 AM
1162    02-SEP-14 07.39.27.336000000 AM
1161    02-SEP-14 07.37.35.561000000 AM
1160    02-SEP-14 07.36.12.776000000 AM
1140    28-AUG-14 06.09.08.346000000 AM
1122    25-AUG-14 09.15.51.112000000 AM
1121    25-AUG-14 09.14.30.789000000 AM
1120    25-AUG-14 09.12.54.710000000 AM
1100    20-AUG-14 05.46.08.394000000 AM
1080    14-AUG-14 10.44.54.917000000 AM
1061    09-AUG-14 06.00.43.708000000 AM
1060    07-AUG-14 02.12.24.893000000 PM
1042    04-AUG-14 07.34.57.224000000 AM
1041    04-AUG-14 07.32.16.555000000 AM
1040    04-AUG-14 07.28.34.526000000 AM
1021    01-AUG-14 11.45.22.141000000 AM
1020    31-JUL-14 09.46.17.765000000 AM
1002    23-JUL-14 01.33.45.940000000 AM
1001    22-JUL-14 11.07.54.784000000 AM
1000    21-JUL-14 06.50.43.991000000 AM

Solution

The sequence generator is consistent. It's only task is to generate unique integer values, nothing else. And why would you be bothered by this behaviour?

As mentioned this behaviour is caused by oracle caching, pre-allocating, the sequences numbers (20 by default). The ID column is an surrogate/artificial primary key and only used to uniquely identify the row, no information whatsoever should be derived from it. Even if you don't cache the sequence number you will never get an uninterrupted series of ID's due to rolled back transactions, deletes, application and database server restarts. And not caching sequences has a heavy performance penalty on high volume transaction system.

So ignore it, it's OK, there is nothing wrong. There is no such thing as a gap free sequence generator...