Multi-threaded application queue : Select work item stored proc executed by 5 worker threads

See more:

C#

SQL

multi-threading

, +

I wrote a below stored proc to retrieve work items from database table which is acting as a queue. There are five threads running which is polling table called ReqResp for work to be done and once it picks the work it can update the response back to that record. I have a primary key called SSID on that table and a flag IsProcessedByWorker to indicate if work is picked by worker or not.

Will below stored proc be sufficient to make sure work item can be picked by only one worker ?

ALTER PROCEDURE [dbo].[SelectWorkItem]
	@Worker varchar(25),
	@PickedTime datetime,
	@SSID varchar(200) OUTPUT
AS
BEGIN
BEGIN TRANSACTION;

WITH    q AS
        (
        SELECT  TOP 1 *
        FROM    ReqResp
        WHERE   IsProcessedByWorker = 0
        ORDER BY RequestIn 
        )
UPDATE  q
SET IsProcessedByWorker=1,PickedByWorkerNo=@Worker,RequestPicked=@PickedTime,@SSID=SSID

COMMIT TRANSACTION;

END