Get Account Number using MIN - Multiple Users on System
-
This is the first time I've ever written anything where there will be multiple users on the system (GULP!).
The Visual Basic 6 application grabs the lowest RiskId that is not tagged. My concern is if you have a bunch of users that hit the get the next account button, which executes this stored procedure, the second person is going to get an error that #TempQueue1 already exists.
I'm assuming this type of thing is done on many systems (get a record based upon a MIN or MAX id field) ... BUT ... I'm thinking there is a way to do this that will not result in what I'm anticipating to be "data collisions."
Thanks!!!
-- get the lowest RiskId
-- if the account has not been tagged
SELECT
MIN(A.RiskId)as RiskId
INTO #TempQueue1
FROM RiskQueue A
INNER JOIN _FinalFlat B
ON A.AcctNo = B.AcctNo
WHERE B.Tagged = 0
-- tag the account just grabbed
UPDATE _FinalFlat SET
Tagged = 1
FROM _FinalFlat A
INNER JOIN RiskQueue B
ON A.AcctNo = B.AcctNo
INNER JOIN #TempQueue1 C
ON B.RiskId = C.RiskId
-- Visual Basic app uses this account number
SELECT
AcctNo as AcctNoUsed
FROM RiskQueue A
INNER JOIN #TempQueue1 B
ON A.RiskId = B.RiskId
DROP TABLE #TempQueue1
-
This concept may solve my problem. This query works:
SELECT TOP 1 A.RiskId
FROM RiskQueue A
INNER JOIN _FinalFlat B
ON A.AcctNo = B.AcctNo
WHERE B.Tagged = 0
ORDER BY A.RiskId
BUT .... I get an error "Incorrect syntax near the keyword 'TOP'." when I run this:
-- grab top account
DECLARE @UseThisAccount as numeric
SELECT @UseThisAccount = TOP 1 A.RiskId
FROM RiskQueue A
INNER JOIN _FinalFlat B
ON A.AcctNo = B.AcctNo
WHERE B.Tagged = 0
ORDER BY A.RiskId
Once I get the above working, this is the rest of the code.
-- tag the account just grabbed
UPDATE _FinalFlat SET
Tagged = 1
FROM _FinalFlat A
INNER JOIN RiskQueue B
ON @UseThisAccount = B.AcctNo
-- Visual Basic app uses this account number
SELECT @UseThisAccount as AcctNoUsed
-
Solution seems to be:
SET ROWCOUNT 1
SELECT A.RiskId
FROM RiskQueue A
INNER JOIN _FinalFlat B
ON A.AcctNo = B.AcctNo
WHERE B.Tagged = 0
ORDER BY A.RiskId
Gets away from using the temp table and its a lot faster
-
Actaully, each user gets a different #TempQueue1.
The name #TempQueue1 is a local reference to an actual table name that is much longer in length, and is unique.
However, why not try something like this:
DECLARE @UseThisAccount as numeric
SELECT @UseThisAccount = Min(A.RiskId)
FROM RiskQueue A
INNER JOIN _FinalFlat B
ON A.AcctNo = B.AcctNo
WHERE B.Tagged = 0
-
Guys concurrency
Whats to stop another client grabbing exactly the same miniumum account number?
NothingWhether it be through time splicing, multi processor systems or whatever it is perfectly possible for another client to get the same minimum account number.
You need to explicitly use a transaction, set the isloation level to serializable or repeatable read and explicitly use an update lock in your select statement where you get the minimum account number. This will cause all other transactions to queue on access to the select statement like on a monitor and you will not get converstion deadlocks
e.g.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANS
SELECT SOMETHING FROM SOMETHING(UPDLOCK)
UPDATE SOMETHING SET WHATEVER = SOMETHINGOROTHER
COMMIT TRANS;
hth
David
-
Absolutely correct.
For full information behind this statement I would suggest againt to read BOL.
Especially if you gonna assume anything.
In this case pay attention to the topic "CREATE TABLE", section "Temporary Tables".
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply