We help IT Professionals succeed at work.

MS SQL - How to handle a table that queue

soozh asked

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?

Watch Question

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
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



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?