How to prefetch Oracle sequence ID-s in a distributed environment

Question

I have a distributed Java application running on 5 application servers. The servers all use the same Oracle 9i database running on a 6th machine.

The application need to prefetch a batch of 100 IDs from a sequence. It's relatively easy to do in a single-threaded, non-distributed environment, you can just issue these queries:

select seq.nextval from dual;
alter sequence seq increment by 100;
select seq.nextval from dual;

The first select fetches the first sequence ID that the application can use, the second select returns the last one that can be used.

Things get way more interesting in a multithreaded environment. You can't be sure that before the second select another thread doesn't increase the sequence by 100 again. This issue can be solved by synchronizing the access on the Java side - you only let one thread begin fetching the IDs at one time.

The situation becomes really hard when you can't synchronize because parts of the application doesn't run on the same JVM, not even on the same physical machine. I found some references on forums that others have problems with solving this problem too, but none of the answers are really working not to mention being reasonable.

Can the community provide a solution for this problem?

Some more information:

• I can't really play with the transaction isolation levels. I use JPA and the change would affect the entire application, not only the prefetching queries and that's not acceptable for me.

• On PostgreSQL I could do the following:

  select setval('seq', nextval('seq') + n - 1 )

• The solution by Matthew works when you can use a fixed increment value (which is perfectly acceptable in my case). However is there a solution when you don't want to fix the size of the increment, but want to adjust it dynamically?

Answer 1

Why not just have the sequence as increment by 100 all the time? each "nextval" gives you 100 sequence numbers to work with

SQL> create sequence so_test start with 100 increment by 100 nocache;

Sequence created.

SQL> select so_test.nextval - 99 as first_seq, so_test.currval as last_seq from dual;

 FIRST_SEQ   LAST_SEQ
---------- ----------
         1        100

SQL> /

 FIRST_SEQ   LAST_SEQ
---------- ----------
       101        200

SQL> /

 FIRST_SEQ   LAST_SEQ
---------- ----------
       201        300

SQL>

A note on your example.. Watch out for DDL.. It will produce an implicit commit

Example of commit produced by DDL

SQL> select * from xx;

no rows selected

SQL> insert into xx values ('x');

1 row created.

SQL> alter sequence so_test increment by 100;

Sequence altered.

SQL> rollback;

Rollback complete.

SQL> select * from xx;

Y
-----
x

SQL>

Answer 2

Matthew has the correct approach here. In my opinion, it is very unusual for an application to reset a sequence's current value after every use. Much more conventional to set the increment size to whatever you need upfront.

Also, this way is much more performant. Selecting nextval from a sequence is a highly optimised operation in Oracle, whereas running ddl to alter the sequence is much more expensive.

I guess that doesn't really answer the last point in your edited question...

Answer 3

Why do you need to fetch the sequence IDs in the first place? In most cases you would insert into a table and return the ID.

insert into t (my_pk, my_data) values (mysequence.nextval, :the_data)
returning my_pk into :the_pk;

It sounds like you are trying to pre-optimize the processing.

If you REALLY need to pre-fetch the IDs then just call the sequence 100 times. The entire point of a sequence is that it manages the numbering. You're not supposed to assume that you can get 100 consecutive numbers.

Answer 4

For when you don't want a fixed size increment, sequences aren't really what you are after, all they really guarantee is that you will be getting a unique number always bigger than the last one you got. There is always the possibility that you'll end up with gaps, and you can't really adjust the increment amount on the fly safely or effectively.

I can't really think of any case where I've had to do this kind of thing, but likely the easiest way is just to store the "current" number somewhere and update it as you need it.

Something like this.

drop table t_so_test;

create table t_so_test (curr_num number(10));

insert into t_so_test values (1);
create or replace procedure p_get_next_seq (inc IN NUMBER, v_next_seq OUT NUMBER) As
BEGIN
  update t_so_test set curr_num = curr_num + inc RETURNING curr_num into v_next_seq;
END;
/


SQL> var p number;
SQL> execute p_get_next_seq(100,:p);

PL/SQL procedure successfully completed.

SQL> print p;

         P
----------
       101

SQL> execute p_get_next_seq(10,:p);     

PL/SQL procedure successfully completed.

SQL> print p;

         P
----------
       111

SQL> execute p_get_next_seq(1000,:p);

PL/SQL procedure successfully completed.

SQL> print p;

         P
----------
      1111

SQL>