Get ORA-00001: unique constraint violated when adding new records

I have an application to add new records to the table using multithreading. It sometime throws ORA-0001 error. If I requeue this request to the threadpool then the problem is solved. It seems to me this is not thread-safe in this case. I use 30 threads, and the size of records is more than 200K. Any hints to solve this issue?

Thanks,

Martin

This sounds like it might be an Oracle issue: from the fact that it succeeds when you retry the SaveChanges, I can only assume that there isn't actually any duplicate data (e.g. we haven't allocated a duplicate ID), because if there were, it would fail again when retried.

Or does the retry create an entirely new entity with a new ID?  In that case it might indicate a bug in ID allocation.

Can you identify which unique constraint is being violated?  Can you verify whether the data being submitted by LightSpeed is truly violating that constraint?  (Use the LightSpeedContext.Logger.)  Does the logger show anything different on the resubmit?

If it is the ID constraint which is being violated, and the logger does show duplicate IDs being allocated, can you let us know which identity method you are using?  If you are using Sequence, do you have the IdentityBlockSize and the sequence INCREMENT BY set to the same value?

A new UOW is created in the retry. A new entity object or a list of new entity objects with the status "new" is passed in the retry. There is only one unique key (PK) specified in the table. The code just inserts the new records into the table so cannot tell anything different from logger show. This issue does not happen everytime.

I use IdentityMethod.MultiSequence and IdentityBlockSize is 1.

Can you provide us with code (ideally a standalone console project plus CREATE TABLE SQL) that reproduces this issue?  It's okay if the repro is intermittent as long as it's "reasonably often."  You can attach a zip file via the options tab, or mail it to ivan @ the obvious domain name if it contains sensitive info.  Thanks!

Also, can you confirm whether you are using 2.2 or 3.0?

 I am using 3.0. I cannot send you the application due to the security concern. Thanks for your offer.

I'm using the KeyTable approach and I am also seeing this issue. The next id should be 140 but it is getting 26 for example. I've attached a log showing the KeyTable id changing. The product is a VisualWebGUI asp.net application.

Looking at these two records:

14:57:44: SELECT
  KeyTable.NextId AS "KeyTable.NextId"
FROM
  KeyTable
FOR UPDATE

--> Time:   1 ms


14:57:44: UPDATE
  KeyTable
SET
  NextId = 142

--> Time:   0 ms


15:07:57: SELECT
  KeyTable.NextId AS "KeyTable.NextId"
FROM
  KeyTable
FOR UPDATE

--> Time:   2 ms


15:07:57: UPDATE
  KeyTable
SET
  NextId = 2

--> Time:   1 ms

This looks like somebody re-ran the "create key table" script between 14:57 and 15:07.  Recreating the key table resets the "next id" value to 1, which would be consistent with the second update statement.

I was using a static variable for the LightSpeedContext and then using CreateUnitOfWork each time my code would want to connect to the database. I've since got rid of the static variable and so far it seems to be working now.

I'm for sure not resetting the KeyTable but it is almost like another context is memory is updating the next value back down.

For now it is appearing to be working by changing it from a static variable.

Thanks for the additional info.  We'll continue to monitor this issue, as it certainly should be safe to use a static LightSpeedContext.  Anyway, glad to hear you have it working now.

I'm sorry but it is not working again. I see that in Microsoft SQL you put a lock on the table. Can the issue be that your not putting a lock and my multiple database connections/contexts are updating the keytable to the wrong value. 

Do you have any kind of logging tool that you can put on the Oracle database itself?  *Something* seems to be resetting the KeyTable -- I assume to 1 again -- but it's not part of your application or the LightSpeed logger would show it.  (What other connections and contexts do you have in your application?  I assume all of them are logging?  I assume you're not hosting migrations or anything else that bypasses the LightSpeed query engine?)  Furthermore, if it were another instance of LightSpeed that somehow erroneously selected a duplicate value, then I'd expect it to be updating to a "recent" value rather than all the way back to 1.  (The KeyTable identity generator performs a SELECT, then an UPDATE to the selected value plus the IdentityBlockSize.  So even if we did manage to SELECT duplicate values of, say, 140, then we should still end up updating KeyTable to, say, 150, not to 1.  And we perform the select and update in its own transaction, with a FOR UPDATE on the SELECT, which *should* prevent duplicate reads anyway -- as I understand it this is the Oracle equivalent of the SQL Server WITH (UPDLOCK).)

So I'm still suspecting that something else -- something external to your application -- is issuing the resets.  I'm not very familiar with the Oracle toolset but if you have some equivalent to SQL Profiler that you could put on the Oracle instance to capture queries from *all* applications, not just your own, then that would be really helpful.  Thanks!

I'm not very familiar with it as well and I know there are tracing tools but I don't know how to use them. It is a dedicated Oracle server to just do this one compatibility test so I don't think there is anything external going on. The only thing I can think of is that it has something to do with VisualWebGUI framework I'm using for the ASP.net application.

I'm going to try using an Oracle sequence and see if that fixes the issue because I'm not too bothered about which identity method I use as long as it works.

I posted this question originally, and now got this issue in another application. It is not multi-threaded application this time. The database is oracle, and it uses MultiSequenceNamingStrategy. The Lightspeed version is 3.1.2501.16319.

I attached the generated SQL below. I noticed that the OAADDRESSES_sequence.NEXTVAL is 1960203 while the PK value in the insert query is 1960202. This causes the issue because 1960202 has been used. Any idea, why (nextval - 1) is used as the PK? 

My current workaround is override the GeneratedId method which call base.GeneratedId() twice. Is there any better way for it?

SELECT CONTACTP.OAADDRESSES_sequence.NEXTVAL FROM dual

--> Time:   1 ms

--> Result: 1960203

BEGIN

INSERT INTO CONTACTP.OAADDRESSES

(

  ADDRESS_ID,

  Addrline1,

  Addrline2,

  Country,

  Dpid,

  Postcode,

  State,

  Status,

  Suburb

)

VALUES

(

  1960202,

  '5/Cecily',

  NULL,

  NULL,

  2343,

  3455,

  'NSW',

  NULL,

  'Pyrmont'

);

This is because there are two ways to interpret NEXTVAL.  LightSpeed defaults to interpreting NEXTVAL as the "next value after the current block."  We've since learned that many other applications interpret NEXTVAL as "the end of the current block."  With a block size of 1, the LightSpeed default means it uses NEXTVAL - 1 whereas other applications may be using NEXTVAL, resulting in clashes if they share the same sequence, because another application can allocate from the sequence and advance it, then LightSpeed allocates from the sequence but because it subtracts 1 gets the same result.

The solution to this is to specify LightSpeedContext.IdentityMethodOptions, passing a SequenceIdentityMethodOptions whose BlockBound is IncludeNextValue:

context.IdentityMethodOptions = new SequenceIdentityMethodOptions {
  BlockBound = SequenceIdentityBlockBound.IncludeNextValue
};

See the docs for the SequenceIdentityBlockBound enum for more info and examples of the effect of this flag.

 Thanks Ivan. It works perfectly!