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

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

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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/

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

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

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

  • 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

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

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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
  • 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
  • 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

  • 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
  • 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

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

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