Post reply

insert fails with violation of PK on table with identity column as PK

  • May 5, 2011 at 10:24 am

    #250385

    Hello,

    I have the following scenario:

    A multi threaded application which is writing into a table that has the PK defined as int identity(1,1).

    During the heavy load periods I see a lot of errors in the application log:

    Failed to execute stored procedure sp_insert_record_into_Obj

    [System.Data.SqlClient.SqlException]: Violation of PRIMARY KEY constraint 'PK_abc'. Cannot insert duplicate key in object 'dbo.Obj'.

    The Obj table is quite huge: around 250 million records.

    The logic inside the stored procedure is quite simple. It's just inserting a record into the table.

    I tried to find a logical reason for this but I couldn't.

    Could this be related to the increment process for the identity column?

    Thanks,

    Dan

  • May 5, 2011 at 10:32 am

    #1320540

    Dan,

    we have a very similar process. Our heavy loads average around 1000 inserts/second and have never had an issue with the primary ley violation. Can you post the procedure that is performing the inserts. What isolation level is being used?

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 11, 2011 at 1:39 pm

    #1323347

    Please post the table definition including constraints and indexes as well.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply