I have 2 applications (one is SpringBoot app and another is a C++ app) calling the same Oracle sequence SEQUENCE_A responsible for generating PK on the same table TABLE-A.
The sequence DDL is as:
CREATE SEQUENCE SEQUENCE_A START WITH 1 INCREMENT BY 1 ORDER;
So, SEQUENCE_A should start at 1 and then keep incrementing by 1, no cycling. This means that any client app inserting into TABLE_A will have SEQUENCE_A generate the next available sequence number which is 2, then 3, then 4 and so on. Based on Oracle documentation, this should happen even if the 2 apps try to generate the next sequence at the same time. (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Sequence-Pseudocolumns.html#GUID-D438E28B-3E30-4B12-8D52-8DA5CFE2E0FF)
My SpringBoot application (which uses ORM, so no SQL INSERT, but mapping to table entity like shown below) has entity using SEQUENCE_A for its PK field:
@Entity
@Table(name = "TABLE-A")
public class TableAEntity implements Serializable {
private static final long serialVersionUID = 1234...L;
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "table-a_generator")
@SequenceGenerator(name = "table-a_generator", sequenceName = "SEQUENCE_A", allocationSize = 1)
@Column(name = "ID", nullable = false)
private Long id;
...
}
This application will mostly do inserts into TABLE-A. The C++ app will call it and let it do the insertion. But if for whatever reason, the SpringBoot app is not responsive or down, after 60 seconds, C++ app will do the insert itself into TABLE-A using SEQUENCE-A to generate PK like:
INSERT ALL
INTO TABLE-A(ID, NAME)
VALUES(SEQUENCE_A.NEXTVAL, '%s')
INTO TABLE-A-LOG(ID, TABLE-A_ID, SUCCEEDED)
VALUES(SEQUENCE_B.NEXTVAL, SEQUENCE_A.CURRVAL, 0)
SELECT * FROM DUAL", name
Given that both of these applications are using SEQUENCE_A, they should always get the next available sequence number. So, if we start with 1, it will be like:
APP SEQ
App1 > 1
App1 > 2
App2 > 3
App1 > 4
App2 > 5
...
However, once in a while I would get a unique constraint violation on the TABLE-A PK in production environment. So, it looks like, one of the 2 application is trying to reuse a PK that already exists. But since both of the applications are using same Oracle sequence SEQUENCE-A defined as above, that should not be possible.
In our staging environment, I haven't seen this error but upon observation of the 2 years worth of inserted records, I noticed that:
- Spring Boot application has PKs with higher value. They are currently at 4 digits lengths and and the last 3 are 4122, 4123, and 4124.
- C++ application has PKS with much smaller value and they have currently all 2 digits lengths and the last 3 generated are 21, 41, and 61.
To my understanding, an Oracle sequence generates next available number regardless of from where it is called. So, in example above, the NEXTVAL should be the highest + 1, so 4124+1 = 4125. How is it possible that C++ app is generating 61 as NEXTVAL?
Obviously this will eventually lead to collision and unique constraint violation I get occasionally.
What I am seeing looks like each application "knows" its own generated highest sequence and than adds 1 to that.
Another strange thing I noticed is that when I inspect NEXTVAL and CURRVAL in Oracle by issuing:
SELECT SEQUENCE_A.NEXTVAL FROM DUAL; // returns 62
SELECT SEQUENCE_A.CURRVAL FROM DUAL; // returns 62
This seem to be more aligned with what C++ app is getting. This makes it even more confusing since why would then SpringBoot application get values larger than 81 by so much? It almost looks like SpringBoot is somehow cashing sequences it has ever generated before and than adding 1 on top of that.
I also inspected values for this sequence by issuing:
SELECT MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE, LAST_NUMBER
FROM ALL_SEQUENCES
WHERE SEQUENCE_NAME = 'SEQUENCE_A';
, and result I get is:
MIN_VALUE|MAX_VALUE |INCREMENT_BY|CYCLE_FLAG|CACHE_SIZE|LAST_NUMBER|
---------+----------------------------+------------+----------+----------+-----------+
1|9999999999999999999999999999| 1|N | 20| 101|
Why would 2 applications inserting into same table and using same sequence result in such different sequence numbers generated?
Given above and that SpringBoot application has generated sequence that are much higher than sequences generated from C++ app and returned from Oracle table when querying DUAL and ALL_SEQUENCES, there seem to be that SpringBoot is somehow using its own sequences. But I dont know why? Every documentation I read confirms that my Entity annotations above are correct.
SELECT SEQUENCE_A.NEXTVAL FROM DUALreturns 82. So, I would expect APP1 and APP2 to next generate 83, 84, 85, ... However, SpringBoot app receives 4000+ as next sequence and does not error While C++ app receives 83 but occasionally gets ORA-00001. This is most likely due to collision with older sequences generated by SpringBoot that were in 80s range. Again, both apps use same sequence as per original post – DragonheadallocationSize=1as per original post. But that SO seem to be related to what I have, thank you. However, I still dont know how to fix it – Dragonhead