CODEWITHSUNDEEP
c#sql-serversql-server-2008
Irannabi
Irannabi

Reputation: 139

Primary key violation error in sql server 2008

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.. ?

Upvotes: 0

Views: 639

Answers (4)

Gabe
Gabe

Reputation: 86698

It sounds like you want a higher transaction isolation level or more restrictive locking.

I don't use these features too often, so hopefully somebody will suggest an edit if I'm wrong, but you want one of these:

-- specify the strictest isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
insert into XYZ values((SELECT max(ID)+1 from XYZ),'Name')

or

-- make locks exclusive so other transactions cannot access the same rows
insert into XYZ values((SELECT max(ID)+1 from XYZ WITH (XLOCK)),'Name')

Upvotes: 0

BU.
BU.

Reputation: 113

A solution for such problems is to have generate the ID using some kind of a sequence.

For example, in SQL Server you can create a sequence using the command below:

CREATE SEQUENCE Test.CountBy1
    START WITH 1
    INCREMENT BY 1 ;
GO

Then in C#, you can retrieve the next value out of Test and assign it to the ID before inserting it.

Upvotes: 0

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

Set ID column to be "Identity" column. Then, you can execute your queries as:

insert into XYZ values('Name')

I think that you can't use ALTER TABLE to change column to be Identity after column is created. Use Managament Studio to set this column to be Identity. If your table has many rows, this can be a long running process, because it will actually copy your data to a new table (will perform table re-creation).

Most likely that option is disabled in your Managament Studio. In order to enable it open Tools->Options->Designers and uncheck option "Prevent saving changes that require table re-creation"...depending on your table size, you will probably have to set timeout, too. Your table will be locked during that time.

Upvotes: 2

Kyle
Kyle

Reputation: 4278

Let the database handle selecting the ID for you. It's obvious from your code above that what you really want is an auto-incrementing integer ID column, which the database can definitely handle doing for you. So set up your table properly and instead of your current insert statement, do this:

insert into XYZ values('Name')

If your database table is already set up I believe you can issue a statement similar to:

alter table your_table modify column you_table_id int(size) auto_increment

Finally, if none of these solutions are adequate for whatever reason (including, as you indicated in the comments section, inability to edit the table schema) then you can do as one of the other users suggested in the comments and create a synchronized method to find the next ID. You would basically just create a static method that returns an int, issue your select id statement in that static method, and use the returned result to insert your next record into the table. Since this method would not guarantee a successful insert (due to external applications ability to also insert into the same table) you would also have to catch Exceptions and retry on failure).

Upvotes: 2

Related Questions