Oracle Sequence Transactionality
Solution 1
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.
Solution 2
Oracle guarantees sequence numbers will be different. Even if your transaction is rolled back, the sequence is 'used' and not reissued to another query.
Edit: Adding additional information after requirements around "no gaps" were stated in comments by Cris
If your requirements are for a sequence of numbers without gaps then 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.
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.
Solution 3
sequence.nextval
never returns the same value (before cycled) for the concurrent request. Perhaps you should check the following URL:
http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#sthref883
Solution 4
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.
Related videos on Youtube
Cris
Updated on June 23, 2022Comments
-
Cris almost 2 years
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".
-
ik_zelf over 11 yearswhy nocache and order? That is not benefical for performance.
-
Cris over 11 yearsdue to a business contraint, cause each number from the sequence needs to be used. if a cache is used and the DB server goes, down for even 1 minute I understood the sequence numbers that are cached are lost.
-
ik_zelf over 11 yearsEven with nocache, you will loose numbers. Think about get sequence and rollback. In that case you do have a gap.
-
Cris over 11 yearsWell there is this scenario , for recovering the lost numbers and there will be a few since rollback is not very often. Using cache it might make the gap bigger. It is a matter of what if ? ... if we use cache performance is improved if cache is set to be a little bit bigger, if we don;t use we might have bigger gaps and longer sequences to recover...will think about it .Thanks for bringing this up...
-
-
Alexander Tokarev over 11 yearsFor person who marked the answer as negative: which way do you suggest in order to create transactional sequence? The my one is only one possible and, moreover, implemented by me and used in my various projects.
-
Cris over 11 yearsI do not know who downvoted, however i did not want a transactional sequence , i asked about the fact if an oracle sequence is affected by a transaction.I voted now up and thanks for the effort of responding.Cheers
-
Morg. over 11 yearsWhich actually means : Oracle's sequences can generate unique values but are not part of the ACID compliant transactional model of the DBMS, but rather a free facility/tool to use with it.