Concurrent SELECT question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Concurrent SELECT question

Author

Topic

bbollard
Starting Member

1 Post

Posted - 2004-04-15 : 08:50:58

Hello, I am new to SQL Server and do not have a grip on the SQL Server architecure so forgive me if this is a newbie question. There is a table where each row is an item that needs to be processed, sort of like an outgoing message queue. There will be two or more processes on different machines that will need to grab x number of records out of this queue at some interval and process them and then remove those items from the table. My concern is how to design the stored procedure so that the records that are grabbed by each process are unique. I say this because this procedure will be run often and I am worried that concurrency issues might arise. Thank you for your assistance!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-16 : 20:35:54

Easiest solution is to lock the table while you're grabbing recs but that will be slow.
Put an ID on the table and have a status
So

MainTbl
id int, Status int default 0, ....

Now the processs to get recs to process will be

set rowcount 1000
update MainTbl
set Status = @@spid
where Status = 0
set rowcount 0

Now the process only accesses the data with it's spid number for processing.
When complete it set the Status to -1.

Improvements to this.
You can detect batches that have failed and these should be dealt with before getting a new batch (maybe process row by row and so find the row in error).

Create a new table for the ID's to process - this means you only need the ID to be indexed on the main table so will probably be more efficient even though the getting of the batch will take longer.

ProcTbl
ID int, Status int

set rowcount 1000
begin tran
insert ProcTbl
select ID, @@spid
from MainTbl (tablockx)
where Status = 0

update MainTbl
set Status = @@spid
from MainTbl
join ProcTbl
on MainTbl.ID = ProcTbl.ID
where ProcTbl.Status = @@spid
commit tran
set rowcount 0

I use set rowcount rather than top so that you can use a variable and tailor it to the memory available.
This is the sort of thing I did for a telecoms rating system which rated a million calls a day and could run happily on my desktop with the batch size set smaller.
It was built so that different processes could be handled by different threads perhaps on different machines but was so efficient this was never needed so it single threaded.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources