MS SQL - How to handle a table that queue

soozh
soozh used Ask the Experts™
on
Hello,

I have a table that represents a queue, where many applications are reading and processing the top of the queue.  The top item is not deleted from the queue - just marked as "being processed"

How do i best handle this so that i dont end up with two applications symultaneously reading the top entry and trying to handle it?  Should i put all my code in a stored procedure and wrap a transaction around it?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
you have a column that identifies the application that processes the item (a guid, for example), and do the update:

set rowcount 1
update yourtable
  set processing_app_guid = 'my_guid', processed_flag = 'being_processed', locked_date_time = getdate()
 WHERE processing_app_guid IS NULL
    and processed_flag = 'to_be_processed'

Open in new window


and then, you fetch "your" row:
select t.*
  from yourtable t
 where process_app_guid = 'my_guid'

Open in new window


if that returns 0 rows, the update had nothing updated, so there was nothing to process

once done, you "clear" the row:

  update yourtable
   set processed_flag = 'processed'
     , processed_app_guid = NULL
  where processed_app_guid = 'my_guid'

Open in new window

optionally, that update can include the rows' PK field to make it 100% safe

however, you should include some cleanup process in case your application may crash, so after x minutes/hours,
you clear the "lock"
update yourtable
   set processed_app_guid = null, locked_date_time = null
 where processed_app_guid is not null
   and locked_date_time  < dateadd(hour,-1, getdate())

Open in new window


hope this helps
soozhCEO

Author

Commented:
Hej,

My programmer has written the following :

UPDATE TOP (1) ReportTemplateSchedules SET sch_Running = 'T', sch_NextRuntime = NULL OUTPUT inserted.* WHERE sch_Running = 'F' AND sch_id in (SELECT TOP 1 sch_id FROM ReportTemplateSchedules WHERE sch_NextRunTime < 'some date' ORDER BY sch_NextRunTime asc)

He sets the sch_Running flag true, and the sch_Runtime to NULL to the top record and then checks to see any data is returned.

DO you see anything wrong with this... it seems to be similar to your example except it uses on statement?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial