October 15, 2006 at 8:36 am
I have a VB6 app that uses SQL Server as the database. I have several queues that users can be working. The system allows more than one user to be logged on to a work queue.
PROBLEM: I'm still having problems with users pulling up the same account. We tested it with 8 users on the same queue pulling 20 accounts each within a two minute or so period. Instead of 160 accounts tagged worked, only 158 accounts were tagged. They wrote the account numbers down so I know they didn't miscount.
QUESTION: What is the best way to avoid two accounts pulled up by the same user? Table locking is not an option. Is there some way to do a ROWLOCK?
Basically I'm selecting the next account based on something I'm calling a RiskId, grabbing the lowest RiskId that has not been tagged worked.
My Procedure:
IF @WorkQueue > 100 AND @WorkQueue < 201 AND @txtQueueOrder = 1 BEGIN
UPDATE _FinalFlat SET
Tagged = 1,
WorkStart = GETDATE(),
UserCode = @UserCode,
@UseIt1 = A.AcctNo
FROM _FinalFlat A
INNER JOIN
(
SELECT TOP 1 A.AcctNo
FROM _FinalFlat A
INNER JOIN QueueOrderBatch B
ON A.AcctNo = B.AcctNo
INNER JOIN rmFailure C
ON A.AcctNo = C.AcctNo
INNER JOIN QueueComboBox D
ON C.CodeFailureNumeric = D.ProgramKeyField
WHERE A.Tagged = 0
AND D.ProgramKeyField = @WorkQueue
AND (
(A.SpreadFile < CASE WHEN @SpreadSelect = 0 THEN 9 ELSE 0 END) OR
(A.SpreadFile = CASE WHEN @SpreadSelect <> 0 THEN @SpreadSelect ELSE 0 END)
)
ORDER BY B.RiskId
) Z
ON A.AcctNo = Z.AcctNo
GOTO FinishIt
END
FinishIt:
SELECT @UseIt1 as AcctNoUsed
EXEC rm_ActiveAcctHistory @UseIt1, @UserCode
October 15, 2006 at 9:37 am
Is it possible to kill the spid after finding the account is being used? We can include a message before killing the spid.
October 16, 2006 at 8:49 am
Have you looked into using Transactions?
October 16, 2006 at 8:52 am
I'm not sure what you mean but would appreciate any suggestions.
October 16, 2006 at 9:28 am
You are probably using the default isolation level of Read Committed. Try either setting the isolation level to Repeatable Read or using the RepeatableRead locking hint on the table to be updated in the query.
eg
UPDATE A1
SET
Tagged = 1,
WorkStart = GETDATE(),
UserCode = @UserCode,
@UseIt1 = A1.AcctNo
FROM _FinalFlat (REPEATABLEREAD) A1
INNER JOIN
(
SELECT TOP 1 A.AcctNo
FROM _FinalFlat (REPEATABLEREAD) A
INNER JOIN QueueOrderBatch B
...
October 16, 2006 at 11:17 am
As soon as ur application reads a row can u flag it as "WORKING" so that ur application does not pick it up?
October 16, 2006 at 11:44 am
On considering this again, it may be safer to use the UPDLOCK hint. eg:
UPDATE A1
SET
Tagged = 1,
WorkStart = GETDATE(),
UserCode = @UserCode,
@UseIt1 = A1.AcctNo
FROM _FinalFlat (UPDLOCK) A1
INNER JOIN
(
SELECT TOP 1 A.AcctNo
FROM _FinalFlat (UPDLOCK) A
INNER JOIN QueueOrderBatch B
...
ps I have always been a bit confused about the differences, if any, between the UPDLOCK and REPEATABLEREAD hints in this type of situation.
I would be grateful if anyone could enlighten me.
October 16, 2006 at 8:38 pm
Long term transactions will cause huge wait periods and, possibly, deadlocks. I like Sreejith's method and have used it before... we used a column called "IsDirty" and it contained the USERID of the person doing the update. The code was written so that no other person could write to the row except that USERID so long as a USERID was in the "IsDirty" column.
--Jeff Moden
October 16, 2006 at 8:46 pm
I'm curious, could a procedure be written that would pull the "next" account where say 1000 users were on the system and no one user would get the same account?
The "UPDLOCK" method seems to be working (tested with three users at the same time). My "IsDirty" field is "Tagged." I'm trying to tag it as quick as I can with the immediate UPDATE. It will never have 1000 users, but 10 or 20 users might use it some day at the same time.
October 17, 2006 at 3:12 am
I read up on this last night and think that UPDLOCK should work no matter how much contention there is. The UPDLOCK should ensure that no other session can read the row with an UPDLOCK until your UPDATE statement (an implicit transaction) has completed.
The alternatives, REPEATABLEREAD or placing a WHERE Tagged <> 1 in the outer UPDATE, could potentially allow other sessions to read the row before the first UPDATE had completed. This would required error handling to retry the failed statement (transaction).
The original update, with READCOMMITTED, could allow two transactions to read the same row. In this case the last transaction would overwrite the first transaction with no errors being produced.
I would be interested in the results of extended testing with UPDLOCK.
October 17, 2006 at 4:50 am
I'll keep ou up-to-date. Think we will test it this afternoon with 10 users.
October 18, 2006 at 9:23 am
I tested this with 10 users using UPDLOCK and there were no duplicates pulled. Thanks again, looks like it works well.
October 18, 2006 at 9:26 am
Thanks for the feedback.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply