Java – how to prefetch Oracle sequence id-s in a distributed environment

I have a distributed Java application running on five application servers, all using the same Oracle 9i database running on machine 6

The application needs to prefetch a batch of 100 IDs from the sequence. It is relatively easy in a single threaded, non distributed environment. You can issue the following query:

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

The first selection obtains the first sequence ID that can be used by the application, and the second selection returns the last sequence ID that can be used

In a multithreaded environment, things become more interesting You can't be sure that another thread won't increase the sequence by 100 again before the second selection This problem can be solved by synchronizing the access of the Java side - you only let one thread start to get the ID once

When you cannot synchronize, the situation becomes very difficult because some parts of the application do not run on the same JVM, even on the same physical machine I found some references to other people's problems in solving this problem on the forum, but none of the answers really worked, let alone reasonable

Can the community provide a solution to this problem?

More information:

>I can't really use the transaction isolation level I use JPA and the changes will affect the entire application, not just prefetch queries, which is unacceptable to me. > On PostgreSQL, I can do the following:

Select SETVAL ('seq ', nextval ('seq') n – 1) > when you can use a fixed increment value (which is completely acceptable in my case), Matthew's solution is OK However, if you don't want to change the size of the increment, but want to adjust it dynamically, is there a solution?

Solution

Why not always increase the sequence by 100? Each "nextval" gives you 100 serial numbers

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>

Notes on your example Pay attention to DDL It will produce an implicit commit

DDL generated submission 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>