SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Concurrent SELECT question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bbollard
Starting Member

1 Posts

Posted - 04/15/2004 :  08:50:58  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 04/16/2004 :  20:35:54  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 04/16/2004 20:37:50
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000