Oracle序列事务性 [英] Oracle Sequence Transactionality

问题描述

我需要一个特定的业务场景来为一个实体（不是PK）上的字段设置一个序列中的数字（序列必须是最小值和最大值之间的数字）

我定义了如下序列：

  CREATE SEQUENCE MySequence 
 MINVALUE 65536 
 MAXVALUE 4294967296 
从65536开始
由1 
开始增加周期
开始
开始订单; 
  

在Java代码中，我从这个序列中检索数字：

  select mySequence.nextval从双
  

我的问题是：

如果我在事务中调用这个从双选择mySequence.nextval，并且在另一个事务中同时调用同样的方法（并行请求），那么值返回的顺序是不同的？

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

ç ause假设我没有使用序列和普通表格，我会自己增加序列，那么如果trasactinalitY是默认的READ COMMITTED，事务2将能够读取相同的值。

解决方案

答案是否定的。

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

序列与事务无关。

请参阅此处的文档

lockquote>

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

生成序列号时，序列会自动增加，
独立交易提交或回滚。如果两个
用户同时递增相同的序列，则每个用户获取的序列
数字可能有差距，因为序列号是其他用户生成的
。一个用户永远不能获取其他用户生成的
序列号。在一个用户生成的序列值为
之后，该用户可以继续访问该值
，而不管该序列是否由另一个用户增加。

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

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

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 :

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 ?

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".

解决方案

The answer is NO.

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

Sequences has 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 unique integers. You can use sequences to automatically generate primary key values.

When a sequence number is generated, the sequence is incremented, independent of 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.

这篇关于Oracle序列事务性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！