assume you have a queue table (queue1) with three extra columns -
(1) processmask
0 - unprocessed
1 - processing
2 - processed
(2) spid
(3) lastUpdate
declare @starttime datetime
begin
set rowcount 20
select @starttime = getdate()
update queue1 set processmask = 1, spid=@@spid, lastUpdate = @starttime
set rowcount 0
/* begin process */
/* use batch mode to use cursor to process the 20 records by select criteria
processmask=1 and spid=@@spid and lastUpdate>=@starttime
/* end process */
update queue1 set processmask = 2
where processmask =1 and spid = @@spid and lastUpdate >= @starttime
end
From performance perspective, use row-level locking for the table.
actually it will be better to define some rule to distribute the work to each client
add index (in my case, lastUpdate+spid+processmask)
(1) processmask
0 - unprocessed
1 - processing
2 - processed
(2) spid
(3) lastUpdate
declare @starttime datetime
begin
set rowcount 20
select @starttime = getdate()
update queue1 set processmask = 1, spid=@@spid, lastUpdate = @starttime
set rowcount 0
/* begin process */
/* use batch mode to use cursor to process the 20 records by select criteria
processmask=1 and spid=@@spid and lastUpdate>=@starttime
/* end process */
update queue1 set processmask = 2
where processmask =1 and spid = @@spid and lastUpdate >= @starttime
end
From performance perspective, use row-level locking for the table.
actually it will be better to define some rule to distribute the work to each client
add index (in my case, lastUpdate+spid+processmask)