Full-stack dev quiz question #2

Full-stack dev quiz question #2

The second question in the quiz for a full-stack developer is about sequences in Oracle. Do you think you really understand them? Check it out.

Correct answer for the first question is published.

What numbers are generated (ACCOUNTS_SEQ.nextval) by a sequence in Oracle that is created by the following script?

create sequence ACCOUNTS_SEQ
    start with 10
    increment by 1;

Choose only those statements that are guaranteed (always true):

1. values are numeric
2. values are not lower than 10
3. a value generated now is lower than a value generated 10 minutes later
4. if the previously generated value was 50, the next value will be 51
5. values are unique
6. if a value was generated in a transaction, rolling back the transaction and generating a value again produces the same value again

   begin transaction;
   select ACCOUNTS_SEQ.nextval from dual;
   rollback;
   select ACCOUNTS_SEQ.nextval from dual;

For answers scroll down

.

.

.

.

.

.

1. values are numeric - TRUE
2. values are not lower than 10 - TRUE
   start with 10 takes care of that.
3. a value generated now is lower than a value generated 10 minutes later - FALSE
   The create statement provided in the question does not contain the CACHE not the NOCACHE clause so a default is used - CACHE 20. It means that each time the cache is empty, 20 numbers are reserved from the sequence. The whole case gets complicated when this sequence is used in a clustered environment with node A and node B. Imagine the following scenario:
   1. ACCOUNTS_SEQ.nextval is executed on node A. Numbers 1-20 are cached on node A. 1 is returned.
   2. ACCOUNTS_SEQ.nextval is executed on node B. Numbers 21-40 are cached on node B. 21 is returned.
   3. ACCOUNTS_SEQ.nextval is executed on node A. There are still 19 values cached so the next cached value is returned which is 2.
   From the user perspective values are not ordered: 1, 21, 2.
4. if the previously generated value was 50, the next value will be 51 - FALSE
   The above example with two nodes shows clearly that there is no guarantee that the next value will be 51 as the statement can be processed by the other node.
   Even if you have only one node, 51 is not guaranteed. As I already wrote, values are cached. It can be the case that when 50 is returned, there are still 9 numbers in the cache - 51-60. Then the node is restarted. The cache is lost with those 9 numbers. 51 is impossible to get, ever. The next value will probably be 61.
5. values are unique - TRUE
   That is the whole point of sequences. If there were not unique, they could not be used as values in primary key columns which is probably the most popular seuquences usage.
6. if a value was generated in a transaction, rolling back the transaction and generating a value again produces the same value again - FALSE
   Sequences are not transactional.