June 25, 2009 at 5:57 pm
Hi all, my first post, stunning forum!
I have a table with a list of job numbers:
JobID JobCode Issued IssuedTo
1 AAA 1 Mike
2 AAB 0 null
3 AAC 0 null
4 AAD 0 null
etc
I have a web application having to get the next available job number (where issued = 0).
This is the simple way to allocate the next job:
set @JobID=select top 1 JobID from Job where Issued=0
update Job set Issued=1, IssuedTo='John' where JobID=@JobID
However, on a threaded web application, one job is allocated to more than one person because there is nothing to prevent another select from the Job table before the update executes.
I've changed the code to this:
begin transaction
update Job set Issued=1, IssuedTo='John' where JobID in (
select top 1 JobID from Job where Issued=0)
select max(JobID) from Job where IssuedTo='John'
commit
However now I get repeated deadlocks happening here, which is a frustration to handle and debug.
What would you recommend is the best way to issue JobIDs from the Job table?
June 25, 2009 at 7:05 pm
What about using a loop? Not that this is the best way but it's getting late and it was the first thing that popped into my head...
I haven't done any concurrency checking (deadlocks, etc.) with this so you'll want to make sure that it's solving your problems.
DECLARE @nextJobID INT;
WHILE (1=1)
BEGIN
SELECT @nextJobID = COALESCE(MIN(JobID),0) FROM Job WHERE Issued=0 AND IssuedTo IS NULL;
IF @nextJobID = 0 -- If there are no jobs left to get, don't want to get into an endless loop
BREAK;
UPDATE Job SET Issued=1, IssuedTo='John'
WHERE JobID = @nextJobID
AND Issued=0 -- Only update if Issued is still zero and
AND IssuedTo IS NULL; -- IssuedTo is still NULL
IF @@RowCount = 1
BREAK; -- Found and updated successfully, so break out of loop
END
PRINT @nextJobID
-- Must handle if no available jobs left to grab
June 25, 2009 at 7:17 pm
Hi
In SQL Server 2005 you can use the OUTPUT clause to work without a loop:
DECLARE @t TABLE
(
JobId INT,
JobCode VARCHAR(10),
Issued BIT,
IssuedTo VARCHAR(30)
)
INSERT INTO @T
SELECT '1', 'AAA', '1', 'Mike'
UNION ALL SELECT '2', 'AAB', '0', null
UNION ALL SELECT '3', 'AAC', '0', null
UNION ALL SELECT '4', 'AAD', '0', null
DECLARE @JobFound TABLE (JobId INT)
UPDATE TOP(1) @t SET
Issued = 1
OUTPUT inserted.JobId
INTO @JobFound
WHERE Issued = 0
SELECT * FROM @JobFound
June 26, 2009 at 12:38 am
And to make sure Jobs really are taken FIFO style, use
UPDATEf
SETIssued = 1
OUTPUTinserted.JobId
FROM(
SELECT TOP 1JobID,
Issued
FROM@t
WHEREIssued = 0
ORDER BYJobID
) AS f
N 56°04'39.16"
E 12°55'05.25"
June 26, 2009 at 1:18 am
Wow, what a response!
DBA with OCD: Thanks, fantastic idea, I'm going to try it!
Florian: Wow, I did not know that, gonna try that too!
Peso: It will be intersting to see if that makes a difference, it's pretty close to what I'm doing now.
:smooooth:
June 26, 2009 at 1:25 am
It does make a difference, especially if you have multiple concurrent users trying to access the same table.
N 56°04'39.16"
E 12°55'05.25"
June 26, 2009 at 1:31 am
Peso (6/26/2009)
It does make a difference, especially if you have multiple concurrent users trying to access the same table.
That's the problem, I might have 50 clients doing at least one access to the procedure per 10 seconds. So I'll be delighted!
Thank you very much. I'm going to try a bit later and get back to you.
June 29, 2009 at 7:33 am
Thanks a million, she's purring like a kitten now for 3 days. No deadlocks, no duplicate jobs issued.
I decided to go with SSCommitted's simple implementation. Works a charm and fast.
My bottleneck now is the batch insert statement, where 1000 records are inserted from a csv file for every job completed.
Many thanks again!
June 29, 2009 at 7:48 am
Just a note, SSCommitted is a status, not an Alias. 😉
N 56°04'39.16"
E 12°55'05.25"
June 29, 2009 at 8:03 am
lol sorry more haste less speed. I meant Florian, with Peso's inputs 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply