Avatar billede fredand Forsker
08. marts 2013 - 18:40 Der er 5 kommentarer og
1 løsning

Strange sequences counter in oracle

Hello!

I got an question from a tester why two sequences in oracle have skipped 20 steps.
We have only seen this in a clustred environment with two JBoss and one databas.
In my own envionment with one JBoss and one database I have not been able to reproduce this.

When I look in the database (after a couple inserts) for two tables I see:

Table_A
ID 1
ID 21
ID 41
ID 42
ID 43

Table_B (refers to table A, many to one i think its called)
ID 1  FK_TABLE_B_ID 1
ID 21 FK_TABLE_B_ID 21
ID 41 FK_TABLE_B_ID 41
ID 42 FK_TABLE_B_ID 42
ID 43 FK_TABLE_B_ID 42
ID 44 FK_TABLE_B_ID 42

The 3 first inserts got steps by 20, but as soon I inserted more as you see in table B (all this from the JEE-application) it started to increment in a more nice way.

I first examined the ORM-object that uses Hibernate.
I found out that the two classes that maps these two tables seems to refer to different sequence, BUT WITH SAME NAME "seq":

public class TableA {

@SequenceGenerator(name = "seq", sequenceName = "TABLE_A_SEQ")
@GeneratedValue(generator = "seq")
@Id
@Column(name = "ID")
private String    id;

public class TableB {
@SequenceGenerator(name = "seq", sequenceName = "TABLE_B_SEQ")
@GeneratedValue(generator = "seq")
@Id
@Column(name = "ID")
private String    id;

Could this be the error??


How ever when I look at sequences in oracle I see that there is a cache with 20, and that there says no order, perhaps this could be the reason??
And that this is a expected behavior, that oracle can deliver one of the ones in the cache??

CREATE SEQUENCE TABLE_A_SEQ
  START WITH 61
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;
 
CREATE SEQUENCE TABLE_B_SEQ
  START WITH 61
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

What do you think guys?

Best regards/Mange takk!
Fredrik from Sweden.
Feel free to reply in danish!
Avatar billede arne_v Ekspert
08. marts 2013 - 19:08 #1
Sequences are guaranteed to generate unique numbers not to generate them without gaps.

The jump of 20 relates to CACHE 20. Basically it generates 20 numbers. And if something happens, then they may get lost.

NOORDER does not apply to the problem.

http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_6014.htm


CACHE

Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

Note:
Oracle recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment.


NOCACHE

Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.



ORDER

Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.


Also read:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:369390500346406705
Avatar billede arne_v Ekspert
08. marts 2013 - 19:10 #2
And I don't think it would make sense to use the same sequence for both tables, so that part is fine.
Avatar billede fredand Forsker
11. marts 2013 - 09:12 #3
Hello Arne!

What more can I say, thanks for an excellent answer!

I would not have guessed that the numbers in the cache would be lost. Interesing in my case is that something extraordinary must have happen in oracle since they got lost. I just wonder what that could have been?

Perhaps there still is a bug according to the annotations. Shouldn't it be like this instead:

public class TableA {

@SequenceGenerator(name = "seqA", sequenceName = "TABLE_A_SEQ")
@GeneratedValue(generator = "seqA")
@Id
@Column(name = "ID")
private String    id;

public class TableB {
@SequenceGenerator(name = "seqB", sequenceName = "TABLE_B_SEQ")
@GeneratedValue(generator = "seqB")
@Id
@Column(name = "ID")
private String    id;

...instead of just name = "seq" and generator = "seq" in both classes. 

Best regards and please leave a svar!
Thanks alot!
/Fredrik
Avatar billede arne_v Ekspert
11. marts 2013 - 13:40 #4
If the Oracle server is restarted then I assume that it will lose all cache.

If you allocate a number in a transaction and then rollback you will most likely lose that number.

If <insert something I don't know about Oracle> then ...

:-)
Avatar billede arne_v Ekspert
11. marts 2013 - 19:02 #5
The annotations in #3 looks more correct than those in #0.

I suspect #0 annotations are working due to some implementation specific feature like sequential processing and storing in a map.
Avatar billede fredand Forsker
12. marts 2013 - 10:04 #6
Thanks for that commet Arne!
I will try it out and see if there is any change.
Best regards
Fredrik
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Kurser inden for grundlæggende programmering

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester