Solved

Thread safe stored procedure

Posted on 2012-03-22
8
1,107 Views
Last Modified: 2012-03-28
Hi,

I'm working with SQL 2008 R2.

I need a stored that will look through records in a table, identify the next one that should should be processed, mark that record so that no other calls to the same sp can be given the same record.  This procedure will be called by a multi-threaded application so it could be called at the same time by multiple threads so it needs to cater for that.  

Here's what I came up with and thought it had worked but I've found that it seems possible for 2 calls to the sp at the same time to both be given the same record back....

DECLARE @threadId int
	
	BEGIN TRANSACTION
	
		-- Get the next available thread
		SELECT TOP 1 @threadId = threadId FROM vwThreads		
		WHERE timeToProcess  < GETDATE()
		AND thread_status = 'waiting'
		ORDER BY last_pulled DESC

		-- Update thread to prevent others from taking
		UPDATE tblThreads
		SET server_name = @serverName, thread_status = 'pulled',last_pulled = GETDATE()
		WHERE threadId = @threadId
	
	COMMIT TRANSACTION
			
        -- Now select the full  row of data for our thread to send back to application
	SELECT TOP 1 * FROM vwThreads
	WHERE threadId = @threadId

Open in new window


Can someone please spot what I've done wrong, or suggest a better way to achieve this functionality?

Many thanks in advance
0
Comment
Question by:cp30
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 

Author Comment

by:cp30
ID: 37755318
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
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 37757603
The BEGIN...COMMIT you've included won't stop others from viewing your mid-update row - while it can discourage it, other users can still choose to ignore your "warning" by changing their isolation level to something like READUNCOMMITTED.

In this case, you should be using something like an OUTPUT Clause on your query, which would allow you to find a row and mark the row as "used" in a single, isolated statement. However, since OUTPUT requires a temp table to be created first, and you're only fetching a single row, you could even use variables if you wanted to, as I've done here:

UPDATE TOP (1) dbo.MyQueue
   SET ClaimedBy = @Server,
       ClaimTime = @ClaimTime
       @QueueID = QueueID,
       @OutputVar1 = SomeData1,
       @OutputVar2 = SomeData2,
       @OutputVar3 = SomeData3
 WHERE Some Criteria...

Open in new window


However, if you want to use OUTPUT, here's the syntax:

UPDATE TOP (1) dbo.MyQueue
   SET ClaimedBy = @Server,
       ClaimTime = @ClaimTime
OUTPUT INSERTED.QueueID,
       INSERTED.SomeData1,
       INSERTED.SomeDate2,
       INSERTED.SomeData3
  INTO #OutputTable (QueueID, Column1, Column2, Column3)
 WHERE Some Criteria...

Open in new window


In either case, this segment of code (altered for your chosen table and columns, obviously) would replace both your select and update, as well as the transaction. Let me know if you have any questions!
0
 

Author Comment

by:cp30
ID: 37760338
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
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:cp30
ID: 37768654
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

Open in new window


Thanks
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37768739
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'

Open 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!
0
 

Author Comment

by:cp30
ID: 37768761
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
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37768801
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.
0
 

Author Closing Comment

by:cp30
ID: 37779418
Great stuff, thanks for the prompt, thorough and accurate advice.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question