如何在分布式环境中预取Oracle序列ID-s

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.

我有一个在5个应用服务器上运行的分布式Java应用程序这些服务器都使用在第6台机器上运行的相同Oracle 9i数据库。

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:

应用程序需要从序列中预取一批100个ID。在单线程,非分布式环境中相对容易,您可以发出以下查询:

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.

第一个选择获取应用程序可以使用的第一个序列ID,第二个选择返回可以使用的最后一个序列ID。

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.

在多线程环境中,事情变得更有趣。你不能确定在第二次选择之前另一个线程不会再次将序列增加100。这个问题可以通过同步Java端的访问来解决 - 你只让一个线程一次开始获取ID。

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.

当您无法同步时情况变得非常困难,因为应用程序的某些部分不在同一个JVM上运行,即使在同一台物理机器上也不运行。我在论坛上发现了一些其他人在解决这个问题时遇到问题的参考文献,但是没有一个答案真正起作用,更不用说合理了。

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.

我无法真正使用事务隔离级别。我使用JPA并且更改会影响整个应用程序,不仅仅是预取查询,而且这对我来说是不可接受的。

On PostgreSQL I could do the following:

在PostgreSQL上我可以做以下事情:

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

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?

当你可以使用固定的增量值(在我的情况下完全可以接受)时,Matthew的解决方案就可以工作。但是,如果您不想修改增量的大小,但想要动态调整它,是否有解决方案?

4 个解决方案

#1

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

为什么不一直将序列增加100?每个“nextval”为您提供100个序列号

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

关于你的例子的注释..注意DDL ..它将产生一个隐式提交

Example of commit produced by DDL

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>

#2

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.

为什么你需要首先获取序列ID?在大多数情况下,您将插入表并返回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.

如果你真的需要预先获取ID,那么只需调用序列100次。序列的整个点是它管理编号。你不应该假设你可以获得100个连续数字。

#3

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.

而且,这种方式更高效。从序列中选择nextval是Oracle中高度优化的操作,而运行ddl来改变序列则要昂贵得多。

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

我想这并没有真正回答你编辑过的问题中的最后一点......

#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>

#1