Intershop7: How to use NumberSequenceProvider correctly

Question

I am working on the latest and greatest Intershop 7.8.0.3 and am trying to implement a new functionality which generates a unique, human readable SKU. My first idea was to use the NumberSeriesProvider to implement the functionality. However, unfortunately this class is marked as deprecated and obviously replaced with NumberSequenceProvider. There is unfortunately a huge documentation gap for this feature.

So far so good. NumberSequenceProvider also provides a method nextValue(sequenceid) to provide the next sequence entry. My problem is how to define the sequenceid. I have seen that there is a method createSequence(...) which can be used to do so. This method adds the sequence to the database and also stores the sequenceid in the local sequenceNumberGenerators cache. Without calling the createSequence(...) method a sequence is - as far as i can see - never available to the nextValue(sequenceId) method and therefore a call to this method will result in an error.

I do not understand how this is supposed to work in a production scenario with multiple application servers. Actually I would need to call the createSequence(...) method on every server start to make the sequence available in the local sequenceNumberGenerators cache. This approach would always try to create the sequence in the database which, results in a catched Exception if the sequence was already created on a previous start or by a parallel application server.

Is this how it is supposed to work?

Thank you and best regards

Answer 1

AFAIK once created - programmatically or via DBInit - the sequence is persisted in the database. It is represented by an oracle sequence and a row in the BASICSERIESENTRY table. The sequence will be available after server restart again and there won't be a need to recreate it.

A call to next val on a server will reserve "series" of numbers. This will happen within a synchronized method - e.g. the numbers from 50 till 100 will be reserved and kept in memory (sychronized) until the interval is exhausted by the generator. After that the server will attempt to request another series from the database. This is an optimization from rather ancient times which aims to decrease the number of database calls. On each server restart the database will be called again so a new series is reserved.

Here is how the server(s) will behave in some cases, when, for example orders are created with a sequence number generator.

Scenario 1: Single server - 3 orders are created, the server is restarted, 3 new orders are created.

Possible result: Order numbers might be 50,51,52, 100, 101, 102

Scenario 2: Parallel servers create orders.

Possible results in time: Order numbers might be 100, 101, 50, 102, 51, 52

Note that you may have 1 to N appservers but just a single DB which manages the sequence.

UPDATE Sample usage:

Step 1) Create the sequence

@Inject
private NumberSequenceProvider nsp;

public void createSeq()
{
    nsp.createSequence(
                    "SO_1234567890",
                    "0000000",
                    "ABC",
                    1,
                    1,
                    Long.MAX_VALUE,
                    1,
                    false,
                    false,
                    50
                    );
}

Here is what happened in the DB:

SELECT * FROM BASICSERIESENTRY WHERE IDENTIFIER = 'SO_1234567890'

IDENTIFIER      NUMBERPATTERN    SEQUENCENAME                   OCA
--------------- ---------------- ------------------------------ ---------------------- 
SO_1234567890   0000000          ABC                            0


SELECT *
FROM user_sequences 
WHERE sequence_name = 'ABC';

SEQUENCE_NAME                  MIN_VALUE              MAX_VALUE              INCREMENT_BY           CYCLE_FLAG ORDER_FLAG CACHE_SIZE             LAST_NUMBER            
------------------------------ ---------------------- ---------------------- ---------------------- ---------- ---------- ---------------------- ---------------------- 
ABC                            1                      9223372036854775807    1                      N          N          50                     1         

Step 2) Restart the server

Step 3) Try it out

String seq = Stream.
            generate(() -> nsp.nextValue("SO_1234567890")).
            limit(5).
            collect(Collectors.joining(", "));

System.out.println("SEQUENCE: " + seq);

Output:

SEQUENCE: 0000001, 0000002, 0000003, 0000004, 0000005    

Troubleshooting:

1) NumberSequenceProvider is really nasty quite when it comes to reporting any Oracle errors. So please check your error log for errors like: ORA-04006: START WITH cannot be less than MINVALUE

2) Activate the Debug log for the provider implementation and check for related debug messages (see comments).