Two Users Pulling Same Account Problem

Question

Two Users Pulling Same Account Problem

texpic

SSCertifiable

Points: 5887
More actions
October 15, 2006 at 8:36 am

#90507

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

Viewing 13 posts - 1 through 12 (of 12 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Is it possible to kill the spid after finding the account is being used? We can include a message before killing the spid.

Bill Scott SSCrazy Points: 2338 More actions · Answer 2

Bill Scott

SSCrazy

Points: 2338

October 16, 2006 at 8:49 am

#666027

Have you looked into using Transactions?

texpic SSCertifiable Points: 5887 More actions · Answer 3

I'm not sure what you mean but would appreciate any suggestions.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 4

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

...

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 5

As soon as ur application reads a row can u flag it as "WORKING" so that ur application does not pick it up?

Ken McKelvey SSCoach Points: 18842 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

texpic SSCertifiable Points: 5887 More actions · Answer 8

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.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 9

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.

texpic SSCertifiable Points: 5887 More actions · Answer 10

I'll keep ou up-to-date. Think we will test it this afternoon with 10 users.

texpic SSCertifiable Points: 5887 More actions · Answer 11

I tested this with 10 users using UPDLOCK and there were no duplicates pulled. Thanks again, looks like it works well.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 12

Ken McKelvey

SSCoach

Points: 18842

October 18, 2006 at 9:26 am

#666544

Thanks for the feedback.