Hi,

I actually managed to find something that I think may help, but some confirmation would be cool.

From what I've read, I should add WITH (UPDLOCK) to my initial select so that no other thread can get the same row until I have finished the transaction, so this should prevent my issue of 2 (or more) threads being able to pull down the same row (threadId)?

Cheers

Hi ryanmccauley

Thanks for that, that sounds like a good way to go, didn't realise I could do it all in one statement.  I will try to implement this into test system early next week and report back on how successful it has been in curing my problem.

Cheers

Hi,

Just trying to implement your suggestion and got a little stuck on how to ensure that the 1 record I get is the record with the oldest last_pulled date, was trying similar to the following but get an error on the order by

UPDATE TOP (1) tblThreads
	   SET 
		server_name = @serverName, 
		thread_status = 'pulled',
		last_pulled = GETDATE(),
		@threadId = threadId	  
	WHERE 
		timeToProcess < GETDATE() AND thread_status = 'waiting'
		ORDER BY last_pulled DESC

Select allOpen in new window

Thanks

That's a bit tricky - you can't use an ORDER BY in an UPDATE statement.

The way we solved this problem was to put a clustered index on time column in our processing queue table, forcing SQL Server to sort the physical data in ascending order on that key. While I always learned not to rely on the order of results in a statement that didn't include an ORDER BY clause, in practice, this effectively returns the oldest row in the table. It's not an official solution, but since order wasn't really critical for us and was really just a suggestion (best-effort instead of true FIFO), this took care of us.

Officially, the answer is to use a sub-query, like this:

UPDATE TOP (1) tblThreads
	   SET 
		server_name = @serverName, 
		thread_status = 'pulled',
		last_pulled = GETDATE(),
		@threadId = threadId	  
    FROM tblThreads t1
    JOIN (select min(last_pulled) as last_pulled from tblThreads
           where thread_status = 'waiting') t2
      ON t1.last_pulled = t2.last_pulled
	WHERE 
		timeToProcess < GETDATE() AND thread_status = 'waiting'

Select allOpen in new window

That way, your inner query finds the oldest "Last_Pulled" value in the table (where the row is "waiting", whatever that means in your application) and then your outer query only pulls a single row with that value, so you'll get the oldest row in teh table (or one of, if there are multiples with that same datetime).

I hope this answers your question!

Thanks, I think I will try the subquery approach as don;t think we can add a clustered index on that column.  I assume this should still be thread safe even with the subquery as it's all executed under on transaction?

Thanks

Correct - it's all executed atomically. I've used both approaches and I don't believe we've ever had an issue where the same record is acted on twice in either approach.

Great stuff, thanks for the prompt, thorough and accurate advice.