locked
Primary key violation error in sql server 2008 RRS feed

  • Question

  • I have created two threads in C# and I am calling two separate functions in parallel. Both functions read the last ID from XYZ table and insert new record with value ID+1. Here ID column is the primary key. When I execute the both functions I am getting primary key violation error. Both function having the below query:

    insert into XYZ values((SELECT max(ID)+1 from XYZ),'Name') 

    Seems like both functions are reading the value at a time and trying to insert with the same value. How can I solve this problem.. ?

    Monday, March 25, 2013 5:07 AM

Answers

  • you need to use IDENTITY column instead of max(ID). It will maintain data integrity and avoid the issues you mentioned.


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Monday, March 25, 2013 5:19 AM
  • Both the functions reading the same value from at a time.

    YOu can use identity for primary key column and get the inserted value using @@SCOPE_IDENTITY.. This might fix your problem.

    Best Regards, Venkat

    • Proposed as answer by Piotr Palka Monday, March 25, 2013 5:44 AM
    • Marked as answer by Allen Li - MSFT Tuesday, April 2, 2013 1:58 PM
    Monday, March 25, 2013 5:20 AM

All replies

  • you need to use IDENTITY column instead of max(ID). It will maintain data integrity and avoid the issues you mentioned.


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Monday, March 25, 2013 5:19 AM
  • Both the functions reading the same value from at a time.

    YOu can use identity for primary key column and get the inserted value using @@SCOPE_IDENTITY.. This might fix your problem.

    Best Regards, Venkat

    • Proposed as answer by Piotr Palka Monday, March 25, 2013 5:44 AM
    • Marked as answer by Allen Li - MSFT Tuesday, April 2, 2013 1:58 PM
    Monday, March 25, 2013 5:20 AM
  • CREATE ID as AutoIncreament & just insert the 'NAME';


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Monday, March 25, 2013 5:25 AM
  • Instead of reading the value and inserting new rows using an application function, use a stored procedure for that pupose. So the locking and concurrency will be managed by the database engine itself...If you need an incremental column as PRIMARY KEY, then change the ID column having an IDENTITY property. However if the table already has data and part of referencial integrity and other constraints, this is not an easy task...Have a look at the below script. Read it carefully..this will help in your scenario...test it thoroughly..

    -- Your existing table
    CREATE TABLE XYZ
    (
    ID INT PRIMARY KEY,
    Name VARCHAR(10)
    );
    -- Insert some values
    INSERT INTO XYZ (ID, Name) VALUES (1, 'ABC')
    INSERT INTO XYZ (ID, Name) VALUES (2, 'XYZ')
    -- A new table with the same structure as XYZ
    -- but with ID column has IDENTITY property
    CREATE TABLE Temp_XYZ
    (
    ID INT IDENTITY (1, 1) PRIMARY KEY,
    Name VARCHAR(10)
    );
    GO
    SET IDENTITY_INSERT Temp_XYZ ON;
    -- Load data from XYZ table to Temp_XYZ
    INSERT INTO Temp_XYZ (ID, Name)
    	SELECT ID, Name FROM XYZ WITH (TABLOCKX);
    SET IDENTITY_INSERT Temp_XYZ OFF
    DROP TABLE XYZ;
    EXEC sp_rename 'Temp_XYZ', 'XYZ';
    GO
    -- Create a Stored Procedure to INSERT values
    -- to XYZ table
    CREATE PROCEDURE usp_INSERTXYZ
    (
    @Name VARCHAR(10)
    )
    AS
    	BEGIN
    		INSERT INTO XYZ (Name) VALUES (@Name)
    	END;
    GO
    -- Call this SP in your application function
    -- Hope your ISOLATION LEVEL is READ COMMITTED
    EXEC usp_INSERTXYZ 'PQR';
    SELECT * FROM XYZ


    Krishnakumar S

    Monday, March 25, 2013 6:00 AM
  • Others have suggested IDENTITY, and this is good for your throughput. However, if you need consecutive values, that is not a solution for you.

    The alternative is:

    DECLARE @id int
    BEGIN TRANSACTION
    SELECT @id = coalesce(max(ID), 0) + 1 FROM tbl WITH (UPDLOCK)
    INSERT tbl (ID, col1, col2, ...)
       VALUES (@id, @val1, @val2...)
    COMMIT TRANSACTION

    But this creates a serialisation point and will decrease your throughput, as the threads have to wait for each other.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 25, 2013 7:52 AM