Sequence with multiple increments
In our database we have a sequence that is used by 2 different applications.
Application A requests *1000* ids, that it will use.
Application B requests *100* ids.
One possible solution could be the following:
The sequence is created with increment *1*.
The application requests the nextval.
The application drops the sequence.
The application recreates the sequence with the value it got + 100/1000.
The problem here is that these applications consist of multiple threads. When thread 1 drops the sequence and thread 2 requests the nextval, the application will fail.
Another solution could be the following:
The sequence is created with increment *1*.
The application requests the nextval.
The application increments the sequence with 100/1000.
Here I am a bit worried about thread 2 requesting the nextval after thread 1 requested the nextval but before thread 1 incremented the sequence.
Maybe if executed as 1 transaction, this problem will not occur?
I would really like to hear your thoughts on this.