Oracle 序列事务性

Oracle Sequence Transactionality

提问by Cris

I need for a particular business scenario to set a field on an entity (not the PK) a number from a sequence (the sequence has to be a number between min and max

我需要一个特定的业务场景来在实体（而不是 PK）上设置一个字段，一个序列中的数字（序列必须是 min 和 max 之间的数字

I defined the sequence like this :

我定义了这样的序列：

CREATE SEQUENCE MySequence
  MINVALUE 65536 
  MAXVALUE 4294967296 
  START WITH 65536
  INCREMENT BY 1
  CYCLE
  NOCACHE
  ORDER;

In Java code I retrieve the number from the sequence like this :

在 Java 代码中，我从序列中检索数字，如下所示：

select mySequence.nextval from dual

My question is :

我的问题是：

If I call this "select mySequence.nextval from dual" in a transaction and in the same time in another transaction same method is called (parallel requests) it is sure that the values returned by the sequence are different ?

如果我select mySequence.nextval from dual在一个事务中调用这个“ ”并且同时在另一个事务中调用相同的方法（并行请求），那么序列返回的值肯定不同吗？

Is not possible to have like read the uncommitted value from the first transaction ?

不可能从第一个事务中读取未提交的值？

Cause let's say I would have not used sequence and a plain table where I would increment myself the sequence, then the transaction 2 would have been able to read same value if the trasactinalitY was the default "READ COMMITTED".

因为假设我不会使用序列和普通表，我会在其中增加序列，如果 trasactinalitY 是默认的“READ COMMITTED”，那么事务 2 将能够读取相同的值。

回答by Florin Ghita

The answer is NO.

答案是不。

Oracle guarantees that numbers generated by sequence are different. Even if parallel requests are issued, RAC environment or rollback and commits are mixed.

Oracle 保证序列生成的数字是不同的。即使发出并行请求，RAC 环境或回滚和提交也是混合的。

Sequences have nothing to do with transactions.

序列与事务无关。

See here the docs:

请参阅此处的文档：

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate uniqueintegers. You can use sequences to automatically generate primary key values.

When a sequence number is generated, the sequence is incremented, independentof the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

使用 CREATE SEQUENCE 语句创建一个序列，这是一个数据库对象，多个用户可以从中生成唯一的整数。您可以使用序列自动生成主键值。

生成序列号时，序列会递增， 与事务提交或回滚无关。如果两个用户同时递增同一个序列，那么每个用户获取的序列号可能会有间隙，因为序列号是由另一个用户生成的。一个用户永远无法获得另一个用户生成的序列号。在一个用户生成序列值后，该用户可以继续访问该值，而不管该序列是否由另一用户递增。

序列号是独立于表生成的，因此相同的序列可以用于一个或多个表。个别序列号可能会被跳过，因为它们是在最终回滚的事务中生成和使用的。此外，单个用户可能没有意识到其他用户正在从相同的序列中进行绘图。

回答by shonky linux user

Oracle guarantees sequence numbers will be different. Even if your transaction is rolled back, the sequence is 'used' and not reissued to another query.

Oracle 保证序列号会有所不同。即使您的事务被回滚，该序列也会被“使用”并且不会重新发布到另一个查询。

Edit: Adding additional information after requirements around "no gaps" were stated in comments by Cris

编辑：在 Cris 的评论中陈述了关于“无差距”的要求后添加附加信息

If your requirements are for a sequence of numbers without gapsthen oracle sequences will probably not be a suitable solution, as there will be gaps when transactions roll back, or when the database restarts or any other number of scenarios.

如果您的要求是一个没有间隙的数字序列，那么 oracle 序列可能不是一个合适的解决方案，因为在事务回滚、数据库重新启动或任何其他数量的情况下都会出现间隙。

Sequences are primarily intended as a high performance generation tool for unique numbers (e.g. primary keys) without regard to gaps and transaction context constraints.

序列主要用作唯一数字（例如主键）的高性能生成工具，而不考虑间隙和事务上下文约束。

If your design / business / audit requirements need to account for every number then you would need instead to design a solution that uses a predetermined number within the transaction context. This can be tricky and prone to performance / locking issues in a multi-threaded environment. It would be better to try to redefine your requirement so that gaps don't matter.

如果您的设计/业务/审计要求需要考虑每个数字，那么您需要设计一个在事务上下文中使用预定数字的解决方案。这在多线程环境中可能很棘手并且容易出现性能/锁定问题。最好尝试重新定义您的需求，这样差距就不重要了。

回答by ntalbs

sequence.nextvalnever returns the same value (before cycled) for the concurrent request. Perhaps you should check the following URL:

sequence.nextval从不为并发请求返回相同的值（在循环之前）。也许您应该检查以下 URL：

http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#sthref883

http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#sthref883

回答by Alexander Tokarev

Unfortunately you have to implement you're 'own wheel' - transactional sequence. It is rather simple - just create the table like sequence_name varchar2, value, min_value number, max_value number, need_cycle char and mess around 'select value into variable from your sequence table for update wait (or nowait - it depends from your scenario)'. After it issue update set value = variable from previous step + 1 where sequence_name = the name of your sequence and issue the commit statement from client side. That's it.

不幸的是，您必须实现“自己的轮子” - 事务序列。这相当简单 - 只需创建像 sequence_name varchar2、value、min_value 数、max_value 数、need_cycle char 这样的表，然后在“从序列表中选择值到变量中以进行更新等待（或 nowait - 这取决于您的场景）”。在它发出 update set value = variable from previous step + 1 where sequence_name = 你的序列的名称并从客户端发出提交语句之后。就是这样。