We’re getting a very intermittent (like every few weeks someone complains about this) primary key constraint violation in our web app. I’ve searched the code base, and the only code that even creates any rows in this table is as follows:
decimal nextDocId = (from d in context.TPM_PROJECTVERSIONDOCS
orderby d.DOCUMENTID descending
select d.DOCUMENTID).Max() + 1;
foreach (TPM_PROJECTVERSIONDOCS doc in documents)
TPM_PROJECTVERSIONDOCS newDoc = new TPM_PROJECTVERSIONDOCS();
newDoc.DOCUMENTID = nextDocId;
newDoc.DOCBLOB = doc.DOCBLOB;
newDoc.DOCUMENTNAME = doc.DOCUMENTNAME;
newDoc.FILECONTENTTYPE = doc.FILECONTENTTYPE;
The error we get is:
ORA-00001: unique constraint (TPMDBO.TPM_PROJECTVERSIONDOCS_PK) violated
This means that
DOCUMENTID is already in use. I have a few theories as to what causes this. First, if multiple people were adding documents at the same time, somewhere between the time when
nextDocId was set and the time the context was saved, new documents may have been added to the database. However, this time would only be a few milliseconds so I think this is unlikely with the small amount of traffic our site gets.
My second theory is perhaps EF does some sort of caching, and
nextDocId is returning a cached value that is no longer valid.
Since this error only happens every so often, and of course only on our production servers, I have no good ways to debug into it or repro.
My question: What’s the most likely cause of this, and is there a better way to re-write this code to prevent primary key violations? I’d love to use just an auto-incrementing field for the primary key, but unfortunately Oracle doesn’t support them. Switching to a UUID would also be a solution, but would result in a lot of DB changes. Thanks!
<PropertyRef Name="DOCUMENTID" />
<Property Name="DOCUMENTID" Type="decimal" Nullable="false" />
<Property Name="PROJECTID" Type="decimal" Nullable="false" />
<Property Name="VERSIONID" Type="decimal" Nullable="false" />
<Property Name="DOCUMENTNAME" Type="VARCHAR2" Nullable="false" MaxLength="500" />
<Property Name="DOCBLOB" Type="BLOB" Nullable="false" />
<Property Name="FILECONTENTTYPE" Type="VARCHAR2" Nullable="false" MaxLength="80" />
I’m unaware of any way to either make
DOCUMENTID default to a sequence, or to query a sequence using the EF.