How to prefetch Oracle sequence identifiers in a distributed environment

Question

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

An application must first select a package of 100 identifiers from a sequence. This is relatively easy to do in a single-threaded unallocated environment, you can simply ask these queries:

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

The first choice selects the first identifier of the sequence that the application can use, the second choice returns the last that can be used.

In a multi-threaded environment, things get more interesting. You cannot be sure that before the second choice, another thread will not increase the sequence by 100 again. This problem can be solved by synchronizing access on the Java side - you allow one thread to start receiving identifiers at a time.

The situation becomes very difficult when you cannot synchronize, because parts of the application do not work on the same JVM, even on the same physical machine. I found several links on forums where others have problems resolving this problem, but none of the answers work, let alone rationality.

Can the community solve the problem?

Additional Information:

• I cannot play with transaction isolation levels. I use JPA, and the change will affect the whole application, not just prefetch requests, and this is unacceptable to me.

• In PostgreSQL, I could do the following:

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

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

Answer 1

Why not just increase the sequence by 100 times? 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> 

Note in your example .. Watch out for DDL .. This will result in an implicit commit

DDL generated commit example

 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

Why do you need to get sequence identifiers first? In most cases, you insert into the table and return the identifier.

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

It looks like you're trying to pre-optimize processing.

If you really need to prefetch the identifiers, just call the sequence 100 times. The whole point of the sequence is that it controls the numbering. You should not assume that you can get 100 consecutive numbers.

Answer 3

Matthew has the right approach. In my opinion, it is very unusual for an application to reset the value of the sequence current after each use. It is much more conditional to set the size of the increment for everything you need.

In addition, this method is much more efficient. Choosing nextval from a sequence is a highly optimized operation in Oracle, while ddl to change the sequence is much more expensive.

I assume that it doesn’t actually answer the last question in your edited question ...

Answer 4

If you don’t need a fixed size increment, the sequences are not really what you need, all they really guarantee is that you get a unique number that will always be bigger than the last. There is always a chance that you will have gaps and you will not be able to really control the number of increments on the fly safely or effectively.

I can’t think of any case when I had to do such things, but most likely the easiest way is to simply save the “current” number somewhere and update it as necessary.

Something like that.

 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>