MS SQL - How to handle a table that queue


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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
soozhCEOAuthor Commented:

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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.