如何在分布式环境中预取 Oracle 序列 ID-s
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43808/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to prefetch Oracle sequence ID-s in a distributed environment
提问by Zizzencs
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，第二个选择返回可以使用的最后一个。
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.
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?
- 我真的不能玩事务隔离级别。我使用 JPA 并且更改会影响整个应用程序，不仅是预取查询，这对我来说是不可接受的。
在 PostgreSQL 上，我可以执行以下操作：
SELECT setval('seq', NEXTVAL('seq') + n - 1)当您可以使用固定的增量值（在我的情况下这是完全可以接受的）时，Matthew 的解决方案有效。但是，当您不想固定增量的大小，但想动态调整它时，有没有解决方案？
回答by Matthew Watson
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>
回答by Zizzencs
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 个连续的数字。
回答by Matthew Watson
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>
回答by serg10
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...
我想这并不能真正回答您编辑的问题中的最后一点......
