May 31, 2015 at 11:01 pm
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.
June 1, 2015 at 2:24 am
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.
-- Gianluca Sartori
June 1, 2015 at 4:02 am
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
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
June 1, 2015 at 5:08 am
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.
June 1, 2015 at 1:20 pm
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]
)
June 2, 2015 at 2:22 am
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
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply