javasqloracle-databasehibernatesequence-sql

Oracle Sequence Transactionality

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

Solution

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

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