Double Insert?

  • Hello,

    I have a asp.net application hooked up to SQL Server 2005. The .net app calls a stored procedure on sql server which looks like this:

    ALTER PROCEDURE [dbo].[p_SaveRepairStage]

    @stageID bigint OUTPUT,

    @repairID bigint,

    @stageType tinyint,

    @result tinyint,

    @userid varchar(50),

    @station varchar(50),

    @endDate datetime=null,

    @comments varchar(500),

    @lastTestStageID bigint

    AS

    if (@stageID=-1)

    begin

    INSERT INTO SystemRepairStage

    (

    RepairID,

    StageType,

    Result,

    UserID,

    Station,

    EndDate,

    Comments,

    LastTestStageID

    )

    VALUES

    (

    @repairID,

    @stageType,

    @result,

    @userid,

    @station,

    @endDate,

    @comments,

    @lastTestStageID

    )

    SET @stageID=IDENT_CURRENT('SystemRepairStage')

    end

    else

    begin

    UPDATE SystemRepairStage SET

    Result=@result,

    EndDate=@endDate,

    Comments=@comments

    WHERE StageID=@stageID and RepairID=@repairID and EndDate is null

    end

    Every now and then (and it is very sporadic) I get duplicated records that look like the example below.

    Not always the same number of them (i was only expecting to insert 1)

    Please note the StartDate (the column is defaulted to GetDate())

    StageIDRepairIDStageTypeResultStartDateUserIDEndDateCommentsLastTestStageIDStation

    1051324305259102009-03-24 10:31:29.720phanyv2009-03-24 13:51:02.400no videoNULLCR-TEST1

    1051331305259102009-03-24 10:31:29.783phanyv2009-03-24 13:51:19.307no videoNULLCR-TEST1

    1051332305259112009-03-24 10:31:29.783phanyvNULLNULLNULLCR-TEST1

    Has anyone ever seen anything like this ? Am i doing something wrong here or is it sql server?

  • The only time I've seen that happen was when the .NET app was accidentally calling the proc multiple times. Either the "Submit" button (or its equivalent) was being clicked repeatedly, or there was something wrong with error-handling and it kept trying.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • That was my first thought as well but the start date (which is generated by sql server)

    is too close (sometimes identical) for it to be a double click by the user.

    Incidentally i have measures in place to prevent that sort of situation.

  • could there be a trigger on the destination table that is causing the issue?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • No. Not a single trigger on the entire database.

  • Have you debugged the app to make sure it's not calling the stored proc twice?

  • By the way, although I don't think that it is the cause of your current problem, you probably should be using SCOPE_IDENTITY() instead of IDENT_CURRENT().

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks for all the suggestions. I will convert all my stored procs to use scope_identity as i do think that may be part of my problem

    It does not appear that the web app itself is at fault.

  • I agree that it could also be a situation where there could be a

    trigger on that table causing a double insert.

    However, you should also have a unique index (if possible) on

    that table. That should prevent dupes and you can have the

    procedure throw an exception back to the application if it

    encounters that condition.

    Also, I have had to add some javascript on the client side to

    disable the save/submit/etc.. button, to prevent the user from

    the IUS (Impatient User Syndrome). Particularly with web apps.

    I couldn't tell from the data you provided, if a unique index could

    be applied. Maybe you can add a compound index to generate a

    unique entry from column data?

    Hope this helps.

    AL

  • I'm just asking because I'm puzzled:

    You define parameter @StageID as OUTPUT but one of the very first things you do is test it --if (@stageID=-1)-- but at that point it is not initialized, I would think, so wouldn't that have unpredictable results?

  • @stageID is acually INPUTOUTPUT and is initialized in the web app to -1

    I use to decide if i'm dealing with a "new" object (insert needed) or an existing one (update needed)

  • More generally, the OUTPUT parameter modifier does not prevent them being used for input also.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I should have thought of this before, but wasn't thinking clearly.

    To prevent dupes also you could wrap your insert in "if not exists" t-sql statement with Begin/End.

    ie:

    if not exists ( select * from xxxx where stageid = @stageID and .... )

    Begin

    insert ...

    End

    I really don't know what you are doing with the -1 there.

    Al

  • Thanks for the clarification on the OUTPUT parameter...

    I'd be suspicious of your logic in the asp.net app which decides whether it is 'New' or not, and thus sets that parameter to -1.

    Someone whos know SS better than I might comment, but why not let the stored proc decide? I.e., pass in the data and try an update. If @@Rowcount <> 1 then the record is not found (or something similar) and then do an insert.

    What type of control are you using in ASP.NET (SQLDatasource, ObjectDataSource, inline code, maybe there's something there you can post here)?

  • You are correct i could use the not exist but why? Checking a variable for a value you know the column can never have is just as effective but quicker

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

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