SEQUENCE .NEXTVAL appears to be Non-Unique causing ORA-00001 errors
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
Has anybody ever seen a sequence return on non-unique (seq.NEXTVAL) value when the sequence is created does not cycle?
As simple as possible, consider the following setup:
a) Master Database
b) Snapshot Database (readonly) - using Snapshot replication
c) 3rd Database for Inserting/Updating records based on replication
Using Event Processing from the Replication Manager to call Stored Procedures to insert/update from b) to c)
The table at (c) is
CREATE TABLE INTERFACERECORDS
(
interfaceid NUMBER(10) NOT NULL,
datetime DATE,
priority NUMBER(15) NOT NULL,
record VARCHAR2(4000),
status NUMBER(1),
description VARCHAR2(255),
CONSTRAINT pk_intrec_pri PRIMARY KEY (priority)
)
The Sequence is
CREATE SEQUENCE INTERFACESSEQ
INCREMENT BY 1
MINVALUE 1
CACHE 20
NOCYCLE
NOORDER;
There are multiple procedures, depending on the tables updated and events each of the procedures which INSERT into the same table, all using the same SEQUENCE interfacesseq.
Replication Event --> (b) CALL Procedure located at (c) with a HASHCODE -> Procedure builds a v_record by selectig data from (b) and trying to INSERT into INTERFACERECORDS.
The insert into the INTERFACERECORDS table is constructed as follows:
<snip>
INSERT INTO interfacerecords
(interfaceid, datetime, priority, record, status, description)
VALUES (v_interfaceid, sysdate, interfacesseq.NEXTVAL, v_record, NULL, NULL);
COMMIT;
<snip>
It is at this point that the error is occurring
ORA-00001: unique constraint (TALLYMAN.PK_INTREC_PRI) violated
I have checked all programs that produce this error and they all use the same INSERT statement as per above. As such, they are all using the SEQUENCE interfacesseq.NEXTVAL to generate the value for the PRIORITY column which is a Primary Key
The assumption that I am finding very hard to prove is that the replication is updating thousands of records per second and maybe the SEQUENCE isn't as highly concurrent as it should be, making it possible under exreme load to allow 2 procedures to obtain the same sequence number.
Now the sequence created above had a CACHE of 20, which for now we have increased to 10000 and are monitoring the problem.
It is very sporadic as the table shows:
DATE ERRORS
28/04/2008 150
19/06/2008 48
23/06/2008 20
07/07/2008 13
29/07/2008 11
09/08/2008 9
26/08/2008 13
18/11/2008 8
23/12/2008 2
11/02/2009 1809
The most recent one caused 1809 errors which has raised the levels of concern.
Now I could write an EXCEPTION to capture DUP_VAL_ON_INDEX but am loathed to do this because the seq.NEXTVAL should return a UNIQUE value
Edited by: user6430084 on Feb 18, 2009 3:14 PM