Database version : Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production
There are thousands of schemas in our database. Those schemas are identical, and each of them have over 400 tables and 300 sequences. Most sequences have cache enabled, and cache size is usually 20.
As the number of schemas grow in the database, we frequently run into primary key constraint violation problems. The values of those primary key columns are from different sequences. If we check database when the problem occurs, sometimes we find the nextval of those sequences are smaller the max values of the corresponding columns already, but sometimes the sequences numbers are still greater than the max values of the columns.
The problem usually occurs when multiple processes are running schema change or data migration scripts concurrently. If we run one process at a time, the problem doesn't seem to happen.
Have anyone experienced similar issue or is there an Oracle bug related to it?
The problem usually occurs when multiple processes are running schema change or data migration scripts concurrently.
What does that mean? 'schema change' or 'data migration'?
I've never heard of having 1000's of identical schemas.
If the primary key values are set using a sequence then two ways you can get data with a larger key value than the sequence is to load data from somewhere else that already has key values or modify the sequence.
And one way that sequences get modified is if the maxvalue is a small (relative) value and you use CYCLE to start the sequence over when it reaches the maximum value.
By 'schema change' or 'data migration' I mean to add tables or columns to all schemas or insert records to tables in all schemas.
None of the two cases, export/import or sequence cycle apply in this scenario.
Edited by: user1016739 on May 1, 2012 5:34 PM
add tables or columns to all schemas or insert records to tables in all schemas.
Adding tables or columns can't cause the problem with the sequences.
Where do these records come from? How are the PKs that use sequence numbers created?
The only way a data value can have a value greater than a sequence is to insert a record that has the value already and doesn't get it from the sequence or modify the sequence to a lower value at some point in the cycle.
There isn't any other way. So you need to nail down which of those is doing the damage.
Those records are created with some sql scripts, and the PKs uses <sequence>.nextval to get their values.
What's weird is sometimes those sequences are good, their nextval are higher than PKs max values. If we run multiple processes simultaneously, the PK violation error occurs. But if we wait for some time w/o changing anything in DB, and then run one process at a time, the problem is gone.
Could it be cached sequences in shared pool out of state for some reason?
Edited by: user1016739 on May 1, 2012 6:09 PM
Its not a new problem anyway, if multiple sessions use your same sequences for different columns, 1 might take the sequence ahead while the other might hold it.. 1 might rollback the insertions while sequence is still ahead and so on.... Be careful with cycle .. and can you show the output of select cycle from user_sequences where sequence_name='SequencE_name' ;
Also see the doc link http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm and read the following in this document
ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.
NOORDER Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
might take the sequence ahead while the other might hold it.. 1 might rollback the insertions while sequence is still ahead and so on
None of those can get the data ahead of the sequence.
Sequences ONLY increase unless CYCLE comes into play or the sequence is reset manually or due to a restore/import. Whether commits or rollbacks are performed doesn't affect this and neither does CACHE.
We don't use one sequence for different columns. Each session accesses different schema, and each schema has its own sequence objects, so multiple session don't compete for sequence numbers.
What could be the cause when sequences are good but we still see PK violation error if we have multiple sessions running and each session is inserting records in different schema? But if we run one process at time, it runs successfully?
Edited by: user1016739 on May 1, 2012 6:37 PM
Another possibility is that a sequence number is getting reused for two different INSERTs.
Post the code that is generating the INSERT statements and that shows the generation and use of the sequence numbers.
I already know it... I was saying that if 1 sequence is shared by multiple sessions how can you assume that if 1 session has taken the sequence to 200 value and other session can still expect it to be on 70 value lets say... thats not possible..
As Rp has mentioned already, there can't be a possibility that the sequence numbers would be ahead of the column values, at least its not what most of us might have seen. So do paste here the code that's doing the inserts and let's see.
if we have multiple sessions running and each session is inserting records in different schema?
Suppose i am running your application by scott user :
As you said each session is having their separate sequences and suppose scott.seq1.nextval is 123
I (scott) am going to insert into hr.tab1.col1
At the same time hr user is also running the application and suppose hr.seq1.nextval is 123
He (HR) is also going to insert into hr.tab1.col1
Now its PK violation, why ? Because I am using current user's sequence object i.e. in scott's session, I am using scott.seq1, while HR user is using hr.seq1; whose both sequence's nextval is 123. So, check the application code that by using whose sequence object you are going to insert into PK column; in the above example, you should use hr.seq1, because you are going to insert into hr.tab1.
Here is an example of sequence definition, insert statement and what two sessions execute concurrently:
CREATE SEQUENCE <schema_name>.tab1_id_sq START WITH 1 increment by 1 nomaxvalue order cache 20;
INSERT INTO <schema_name>.tab1 (id) VALUES (<schema_name>.tab1_id_sq.nextval);
Let's say there are two sessions, S1 and S2. If S1 is running "INSERT INTO schema1.tab1 (id) VALUES (schema1.tab1_id_sq.nextval);", then S2 is running "INSERT INTO schema2.tab1 (id) VALUES (schema2.tab1_id_sq.nextval);", so they don't access the same table or the same sequence, but two tables and two sequences in different schemas.