Stored Procedure throws Violation of PRIMARY KEY constraint when called concurrently whit same parameters

Question

Stored Procedure throws Violation of PRIMARY KEY constraint when called concurrently whit same parameters

fabriziodb

Mr or Mrs. 500

Points: 562
More actions
June 13, 2016 at 6:44 am

#317121

CREATE TABLE dbo.SomeTable
(
ID int NOT NULL,
SomeText varchar(10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.SomeTable ADD CONSTRAINT
PK_SomeTable PRIMARY KEY CLUSTERED
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE PROCEDURE [dbo].[InsertSomeText]
@ID int,
@SomeText varchar(10)
AS
BEGIN
BEGIN TRANSACTION
DELETE FROM SomeTable WHERE ID = @ID
INSERT INTO SomeTable (ID, SomeText)
Values (@ID, @SomeText)
COMMIT TRANSACTION
END
the above stored procedure some time throws Violation of PRIMARY KEY constraint when called at the same time with the same @ID value though delete/Insert is inside the same transaction. Why?

Viewing 15 posts - 1 through 15 (of 35 total)

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

Phil Parkin SSC Guru Points: 246978 More actions · Answer 1

Two procs trying to delete and insert the same @Id at the same time? Why would you expect anything other than a PK violation?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Hi,

Why are you doing deleting and insertion on the same id value?

what you want to accomplish from that.

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

fabriziodb Mr or Mrs. 500 Points: 562 More actions · Answer 3

delete insert are inside one transaction, so i think it would be atomic operation.

default level is read committed so it would be impossible another transacion insert and commmit before the first transaction is committed.

fabriziodb Mr or Mrs. 500 Points: 562 More actions · Answer 4

kapil_kk (6/13/2016)
Hi,
Why are you doing deleting and insertion on the same id value?
what you want to accomplish from that.

the goal is to update text valus if a record with that id already exists or insert if not exists.

that stored procedure is called from a multithread windows service, so some times it happens two or more threads to call it with the same ID.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 5

fabriziodb (6/13/2016)
kapil_kk (6/13/2016)
Hi,
Why are you doing deleting and insertion on the same id value?
what you want to accomplish from that.
the goal is to update text valus if a record with that id already exists or insert if not exists.
that stored procedure is called from a multithread windows service, so some times it happens two or more threads to call it with the same ID.

Why not use MERGE to do this?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 More actions · Answer 6

I feel like a Merge would be a better idea here, rather than a delete and insert. Then you can update or insert, depending on if the record is there or not.

Edit; Aww, Phil beat me while i got distracted 🙁

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Y.B. SSChampion Points: 11649 More actions · Answer 7

Maybe something like this would be better suited...

IF EXISTS (SELECT 1 FROM SomeTable WHERE ID = @id)

UPDATE SomeTable SET SomeText = @SomeText WHERE ID = @ID

ELSE

INSERT INTO SomeTable VALUES (@ID, @SomeText)

EDIT: Although this works I did forget about the MERGE. Well suited for that scenario, check it out.

SELECT quote FROM brain WHERE original = 1
0 rows returned

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Y.B. (6/13/2016)
Maybe something like this would be better suited...
IF EXISTS (SELECT 1 FROM SomeTable WHERE ID = @id)
UPDATE SomeTable SET SomeText = @SomeText
ELSE
INSERT INTO SomeTable VALUES (@ID, @SomeText)
EDIT: Although this works I did forget about the MERGE. Well suited for that scenario, check it out.

It might be cheaper to attempt to update the row then check rows updated, rather than finding it twice.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 9

ChrisM@Work (6/13/2016)
Y.B. (6/13/2016)
Maybe something like this would be better suited...
IF EXISTS (SELECT 1 FROM SomeTable WHERE ID = @id)
UPDATE SomeTable SET SomeText = @SomeText
ELSE
INSERT INTO SomeTable VALUES (@ID, @SomeText)
EDIT: Although this works I did forget about the MERGE. Well suited for that scenario, check it out.
It might be cheaper to attempt to update the row then check rows updated, rather than finding it twice.

I'm with Chris.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

ChrisM@Work (6/13/2016)
Y.B. (6/13/2016)
Maybe something like this would be better suited...
IF EXISTS (SELECT 1 FROM SomeTable WHERE ID = @id)
UPDATE SomeTable SET SomeText = @SomeText
ELSE
INSERT INTO SomeTable VALUES (@ID, @SomeText)
EDIT: Although this works I did forget about the MERGE. Well suited for that scenario, check it out.
It might be cheaper to attempt to update the row then check rows updated, rather than finding it twice.

And don't forget the WHERE in the UPDATE.

It hurts to love someone and not be loved in return. But what is more painful is to use UPDATE and forget the WHERE.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Y.B. (6/13/2016)
Maybe something like this would be better suited...
IF EXISTS (SELECT 1 FROM SomeTable WHERE ID = @id)
UPDATE SomeTable SET SomeText = @SomeText
ELSE
INSERT INTO SomeTable VALUES (@ID, @SomeText)

Not atomic, and hence will result in primary key violations, same with Merge.

http://source.entelect.co.za/why-is-this-upsert-code-broken

Sorry about the formatting on that page, I'm trying to get it fixed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11649 More actions · Answer 12

ChrisM@Work (6/13/2016)
Y.B. (6/13/2016)
Maybe something like this would be better suited...
IF EXISTS (SELECT 1 FROM SomeTable WHERE ID = @id)
UPDATE SomeTable SET SomeText = @SomeText
ELSE
INSERT INTO SomeTable VALUES (@ID, @SomeText)
EDIT: Although this works I did forget about the MERGE. Well suited for that scenario, check it out.
It might be cheaper to attempt to update the row then check rows updated, rather than finding it twice.

I would have never thought about it that way but I like it. That being said if ID was indexed I would not think it to be an expensive operation to begin with. Also Phil is right...better to make use of a MERGE here anyway.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Y.B. (6/13/2016)
Also Phil is right...better to make use of a MERGE here anyway.

I disagree MERGE is NOT atomic (it should be, it isn't) and hence both it and the IF EXISTS form are prone to concurrency issues unless elevated isolation levels and/or locking hints are used.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11649 More actions · Answer 14

GilaMonster (6/13/2016)
Y.B. (6/13/2016)
Also Phil is right...better to make use of a MERGE here anyway.
I disagree MERGE is NOT atomic (it should be, it isn't) and hence both it and the IF EXISTS form are prone to concurrency issues unless elevated isolation levels and/or locking hints are used.

Luis - thanks for the catch...I edited the code.

Gail - Although I had no problem executing the code this is a very simple example. Do you have any examples/articles you can point me to so I can check it out (if not I'll just ask Mr. Google). You have me interested...Thanks.

SELECT quote FROM brain WHERE original = 1
0 rows returned