Get Work system - avoid duplicate inserts

  • kdfischer

    SSC Veteran

    Points: 217

    I keep struggling to figure out how to explain this, so this is the best I can come up with. I am an intermediate SQL developer. I know enough but not an expert. I can't even figure out the correct Google search to even get ideas.

    I have a Intranet web-based app that is used by some 150+ users with a large contingent of offshore users. There is a button called Get Work. When a user clicks that button, it is to go to a table of unassigned work. This work is unassigned if the unassigned column is null. It is to then take data from that table, namely a key and some descriptive stuff, and insert it into another table that serves as the master table of all work requests. Essentially the person that clicked Get Work gets a new assignment to them entered into this work request table.

    Sounds simple enough to me. And I thought I built a nice application.

    However, no matter how I've coded for things in SQL, I continue to have duplicate records inserted into this master work request table. Sometimes 4 or 5 inserts. In other words, 2 or more people were assigned the same work. The only thing different is the inserted time and the worker assigned that request. Though it does happen with my on-shore users, it's more prevalent with the offshore users. I swear, it's as if they all start working at exactly the same time and everybody clicks the Get Work button at exactly the same time.

    These inserts happen within milliseconds of each other most of the time. Sometimes (and I for the life of me can't figure this out) it's the exact same time inserts, and even here and there I get exactly the same record inserted (same person) at exactly the same time. Nothing but the identity key different.

    I've tried transactions, but I'm apparently doing them wrong because it gives deadlocks left and right.

    Any approaches you can suggest would be greatly appreciated. I spend a lot of time every day checking for and removing duplicates and I just can't keep this up and certainly can't support it when I'm on vacation.

    Thanks.

  • spaghettidba

    SSC Guru

    Points: 105661

    Without seeing the code or the underlying database schema, the only suggestion I can come up with is: don't allow duplicates with a meaningful UNIQUE constraint.

    It would be really helpful if you could post some more details about both your code and your schema.

  • ChrisM@Work

    SSC Guru

    Points: 186040

    Some ideas here[/url]. As Gianluca says, difficult to tell what is going wrong with no code. Your statement could be something like this:

    UPDATE UnassignedWork

    SET unassigned = 1

    OUTPUT

    INSERTED.column1,

    INSERTED.column2

    -- etc

    INTO WorkRequestTable

    WHERE unassigned IS NULL

    WITH (ROWLOCK,UPDLOCK,READPAST)

    -- lock rows, exclusive lock, read past rows locked by others

    [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

  • Ed Wagner

    SSC Guru

    Points: 286955

    In your OP, you said the work is unassigned if the Assigned column is null. You also said that you're inserting into an Assignments table, but are you also updating the original Assigned column to something other than null? If you're checking this column but never updating it, this could be your problem.

    A different approach would be to check for missing rows in your Assignments table instead of the Assignments column on your main work table. Either way should be able to get you there.

    To be of any real help, we're going to need to see your table structures and code.

  • kdfischer

    SSC Veteran

    Points: 217

    My code:

    UPDATE SWProjectGrid.dbo.tblServiceRequests

    SET TesterID_AA = @LanID

    ,Tester_AA = dbo.Func_BT_BatchQueueGetName(@LanID)

    ,AutoAssignedDate = getdate()

    ,ManagerAutoAssign = 0

    OUTPUT inserted.ID

    ,inserted.[SR Number]

    ,inserted.AccountName

    ,inserted.Area

    ,inserted.[Sub Area]

    ,inserted.Carrier

    ,inserted.Div

    INTO @tblSRInserts

    WHERE ID = (

    SELECT TOP 1 t1.ID

    FROM SWProjectGrid.dbo.tblServiceRequests AS t1

    INNER JOIN SWProjectGrid.dbo.tbl_AutoAssign_TesterCategoryAssignments AS A ON dbo.func_AutoAssign_CategoryCheck(t1.[SR Number]) = A.CatID

    LEFT JOIN SWProjectGrid.dbo.tblTestOpsValues AS V ON V.ID = dbo.func_AutoAssign_CategoryCheck(t1.[SR Number])

    LEFT JOIN SWProjectGrid.dbo.Medicare_Renewal_Copays t2 ON t2.SR = t1.[SR Number]

    AND t2.[Copay Grid Status] = 'Copay Automation Complete'

    AND t2.[Accumulator/CBM Status] = 'Setup Complete'

    AND isnull(t2.AutoAssigned, 0) = 0

    WHERE t1.[Sub Area] NOT IN (

    'Question'

    ,'DIV Implementation'

    )

    AND t1.[Closed Date] IS NULL /* Not Closed */

    AND t1.STATUS = 'Set-up Complete' /* Status is Set-up Complete */

    AND ISNULL(t1.TesterID_AA, '') = '' /* A tester has not been assigned */

    AND A.UserID = @LanID /* The user that clicked GetWork */

    AND ( /* If 137 then it must be Automation Complete and Setup Complete other than 137 not necessary */

    dbo.func_AutoAssign_CategoryCheck(t1.[SR Number]) <> 137

    OR dbo.func_AutoAssign_CategoryCheck(t1.[SR Number]) = 137

    AND [Copay Grid Status] = 'Copay Automation Complete'

    AND [Accumulator/CBM Status] = 'Setup Complete'

    AND isnull(AutoAssigned, 0) = 0

    )

    ORDER BY t1.[SR Number]

    )

  • ChrisM@Work

    SSC Guru

    Points: 186040

    There are many impediments to performance in your query. You want it to run as quickly as possible. Test this as a functional equivalent.

    ;

    WITH TheChosenOne AS (

    SELECT TOP 1

    t1.TesterID_AA,

    t1.Tester_AA,

    t1.AutoAssignedDate,

    t1.ManagerAutoAssign,

    t1.[SR Number]

    FROM SWProjectGrid.dbo.tblServiceRequests AS t1

    /* CJM GET RID OF THIS FUNCTION! It will kill performance! */

    INNER JOIN SWProjectGrid.dbo.tbl_AutoAssign_TesterCategoryAssignments AS A

    ON dbo.func_AutoAssign_CategoryCheck(t1.[SR Number]) = A.CatID

    /* CJM this join is redundant. It doesn't filter any rows */

    --LEFT JOIN SWProjectGrid.dbo.tblTestOpsValues AS V

    -- ON V.ID = dbo.func_AutoAssign_CategoryCheck(t1.[SR Number])

    INNER JOIN SWProjectGrid.dbo.Medicare_Renewal_Copays t2

    ON t2.SR = t1.[SR Number]

    /* CJM Redundant - repeated in WHERE clause */

    --AND t2.[Copay Grid Status] = 'Copay Automation Complete'

    --AND t2.[Accumulator/CBM Status] = 'Setup Complete'

    --AND ISNULL(t2.AutoAssigned, 0) = 0

    WHERE t1.[Sub Area] NOT IN ('Question','DIV Implementation')

    AND t1.[Closed Date] IS NULL /* Not Closed */

    AND t1.STATUS = 'Set-up Complete' /* Status is Set-up Complete */

    AND ISNULL(t1.TesterID_AA, '') = '' /* A tester has not been assigned */

    AND A.UserID = @LanID /* The user that clicked GetWork */

    AND

    --(

    /* If 137 then it must be Automation Complete and Setup Complete other than 137 not necessary */

    /* CJM don't these cancel out and become a NULL filter, which in turn is rendered */

    /* redundant by the equal to operator in the join between t1 and A? */

    --dbo.func_AutoAssign_CategoryCheck(t1.[SR Number]) <> 137

    --OR dbo.func_AutoAssign_CategoryCheck(t1.[SR Number]) = 137

    -- these turn the LEFT JOIN t2 into an INNER JOIN

    t2.[Copay Grid Status] = 'Copay Automation Complete'

    AND t2.[Accumulator/CBM Status] = 'Setup Complete'

    AND ISNULL(t2.AutoAssigned, 0) = 0

    --)

    ORDER BY t1.[SR Number]

    )

    UPDATE TheChosenOne SET

    TesterID_AA = @LanID,

    Tester_AA = dbo.Func_BT_BatchQueueGetName(@LanID),

    AutoAssignedDate = GETDATE(),

    ManagerAutoAssign = 0

    /* CJM why not insert directly into the permanent table? */

    OUTPUT inserted.ID

    ,inserted.[SR Number]

    ,inserted.AccountName

    ,inserted.Area

    ,inserted.[Sub Area]

    ,inserted.Carrier

    ,inserted.Div

    INTO @tblSRInserts

    [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

Viewing 6 posts - 1 through 6 (of 6 total)

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