I have an application which use a table to figure out the next work it need to process. Application is running from multiple machines & multi-threaded also. I am using a database table, let say WorkQueue to track all works.
WorkID, WorkName, Status are the column name of the table.
Status='NEW' is a new work
Status='INPROCESS' is in process
Status='COMPLETE' is complete
I am trying to write a stored procedure, which will update single record status from NEW to INPROCESS and return WorkID. I'm trying to prevent more than one instances of the process grabbing the same record where the status = 'NEW'
Help....
I have 2 suggestions:
First Suggestion:
Create a stored procedure that will return the next work item to be processed. In the case that there has been a conflict, the stored procedure will return a certain value (I've assumed this to be -2). From the code itself you will manage that if a -2 value returns, you need to re-execute the stored procedure. If a -1 value is retuned then there are no new items. Here is the procedure I wrote:
CREATE PROCEDURE GetNextWorkItem
AS
BEGIN
-- @.WorkID will contain either the next work item to be processed
-- or -1 if there is no available 'New' work items
-- or -2 if there was a conflict and it needs to be executed again
DECLARE @.WorkID AS INT
-- Get the next 'New' work item and put the ID in @.WorkID
SELECT TOP 1 @.WorkID = WorkID
FROM WorkQueue
WHERE Status = 'New'
-- If @.@.RowCount is 0 then there are no 'New' work items
-- Return -1
IF @.@.RowCount = 0
BEGIN
SELECT -1 AS WorkID
RETURN
END
-- Update the status of the work item you've selected
-- making sure that its status is still 'New'
UPDATE WorkQueue
SET Status = 'In Progress'
WHERE WorkID = @.WorkID
AND Status = 'New'
-- If @.@.RowCount is 0 then the status of this work item was changed before you could update it
-- A -2 value will be returned and the code should execute this stored procedure again
IF @.@.RowCount = 0
SELECT -2 AS WorkID
ELSE
SELECT @.WorkID AS WorkID
END
GO
Second Suggestion:
Add a new column called WorkKey (in my example its datatype is UniqueIdenitfier). You will randomly generate a value in the stored procedure and update the WorkKey field of the first 'New' work item with this value. A certain value will indicate that there are no new work items (-1 in my procedure). Here is the procedure I wrote:
CREATE PROCEDURE GetNextWorkItem
AS
BEGIN
-- @.WorkKey is a temporary variable that you will use to select the work item that you will process next
DECLARE @.WorkKey AS UniqueIdentifier
SET @.WorkKey = NewID()
-- Update the first 'New' available work item with the key you have just generated
UPDATE WorkQueue
SET WorkKey = @.WorkKey, Status = 'In Progress'
WHERE WorkID IN
(SELECT TOP 1 WorkID
FROM WorkQueue
WHERE Status = 'New')
-- If @.@.RowCount is 0 then there are no 'New' work items. Re-execute this stored procedure later
IF @.@.RowCount = 0
BEGIN
SELECT -1 AS WorkID
RETURN
END
-- Get the WorkID that you have just updated
SELECT WorkID
FROM WorkQueue
WHERE WorkKey = @.WorkKey
END
GO
Please tell me if this answers your question.
Best regards,
Sami Samir
|||
You could make use of the OUTPUT clause that's available in SQL Server 2005, see the example below. This would avoid you having to use table hints.
Chris
SET NOCOUNT ON
DECLARE @.WorkQueue TABLE (WorkID INT, WorkName VARCHAR(100), Status VARCHAR(10))
INSERT INTO @.WorkQueue VALUES (1, 'Answer a forum question.', 'NEW')
INSERT INTO @.WorkQueue VALUES (2, 'Wash the car.', 'NEW')
INSERT INTO @.WorkQueue VALUES (3, 'Have a beer.', 'LATER')
INSERT INTO @.WorkQueue VALUES (4, 'Feed the cat.', 'COMPLETE')
DECLARE @.Output TABLE (WorkID INT)
UPDATE w
SET w.Status = 'INPROCESS'
OUTPUT inserted.WorkID INTO @.Output
FROM @.WorkQueue w
WHERE w.WorkID = (SELECT TOP 1 w2.WorkID FROM @.WorkQueue w2 WHERE Status = 'NEW' ORDER BY w2.WorkID)
DECLARE @.WorkID INT
SELECT @.WorkID = WorkID FROM @.Output
SELECT *
FROM @.WorkQueue
WHERE WorkID = @.WorkID
|||
Below is a code sample that you could put in a procedure. The sample starts a transaction a does a select to get the workid and workstatus. I used 3 lock hints readpast, rowlock, holdlock and updlock. The readpast will allow better concurrency in a multiple user system, however it is possible that a row may not be returned in all pages or rows are locked at the time the procedure is invoked. The rowlock hint makes sure that a pagelock isn't used so more users can access the data. The holdlock and updlock will lock the rows for the duration of the transaction and make sure that no other user can acquire the data. I have used this technique before for "work queue" and it works well. HTH.
DECLARE @.WorkID INT
,@.WorkName VARCHAR(20)
BEGIN TRAN
SELECT @.WorkID = WorkID, @.WorkName = WorkName
FROM WorkQueue(HOLDLOCK,UPDLOCK,READPAST,ROWLOCK)
UPDATE WorkQueue SET status = 'In Process'
WHERE
WorkID = @.WorkID;
COMMIT TRAN
|||
we are planning to sql server 2000. Sorry I forget to mention it.
I see different way do it. any suggestion which one I should use
|||
I think your answer is correct one. does this Guarantee uniqueness between concurrent & multiple user system. Do anyone see a problem in using it
|||
I AM GETTING FOLLOWING ERROR WITH YOUR SQL
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels
|||I added WITH before the locking hints and it worked fine. Here is the updated query:
SELECT @.WorkID = WorkID, @.WorkName = WorkName
FROM WorkQueue WITH (HOLDLOCK, UPDLOCK, READPAST, ROWLOCK)
Best regards,
Sami Samir
|||
Sorry about forgetting the with clause. Yes it will guarantee concurrency and uniqueness. The readpast technique is great for a work queue. Haven't found it to be helpful in many other situations. Since this is a work queue make sure you do not put any uncessary indexes on it and/or have connections access it via different paths (i.e. indexes). Since work queues by nature are "busy" tables you do not want to introduce deadlocking by having multiple procedures access the data down different retrieval paths. HTH.
|||
Look at the below sql and tell me what is wrong with it. it doesn't have any transaction before it. does this works
CREATE PROC dbo.GetNextWork @.WorkItem BIGINT=0 OUTPUT as
UPDATE dbo.WorkQueue
SET Status='InProcess',@.WorkItem=WorkItemID
WHERE WorkItemID = ( SELECT TOP 1 WorkItemID
FROM dbo.WorkQueue WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE Status = 'NEW' ORDER BY WorkItemID )
Variable @.WorkItem will be returned from stored procedure
|||
That would work, I created some sample code below for the forum. Learned something new today, never tried variable assignment in an update statement.
create table #workqueue (
workitemid int,
status varchar(30)
);
insert into #workqueue (workitemid,status) values (1,'new')
insert into #workqueue (workitemid,status) values (2,'new')
Declare @.WorkItem int;
UPDATE #WorkQueue
SET Status='InProcess',@.WorkItem=WorkItemID
WHERE WorkItemID = ( SELECT TOP 1 WorkItemID
FROM #WorkQueue WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE Status = 'NEW' ORDER BY WorkItemID )
select @.workitem
