Queue Functionality

I am working on Queue functionality for my application. Queue is nothing but table where same record should not be processed by 2 different people/machines. To simplify

consider table

CREATE TABLE [dbo].[Table_1](

[Col1] [int] NULL,

[Enabled] [bit] NULL

) ON [PRIMARY]

I have procedure that picks up records and stores in table passed as input.

Different apps running on different machines specify their local tables

Create Procedure [dbo].[spTestQueue]

@.Tbl as varchar(100)

AS

Declare @.No varchar(10)

Select Top 1 @.No= Cast(Col1 as varchar(10)) from Table_1(nolock) Where Enabled = 0

Update Table_1 Set Enabled =1 Where Col1 = Cast(@.No as int)

EXEC ('Insert ' + @.Tbl + ' values(' + @.No + ')')

It worked fine during testing but there is nothing to prevent 2 machines to pick up same records.This is highly transactional table

How to efficiently implent locking or transcation to ensure that same record dose not get processed by 2 machines

You need to use transactions.

Code Snippet

BEGIN TRANSACTION

SELECT ... FROM Table_1 WITH (UPDLOCK) WHERE ...

UPDATE ...

COMMIT TRANSACTION

EXEC ...

I'd suggest your queue should be a separate table which should only contain unprocessed items, so deleting a row would remove it from the queue table. This would scale much better.

Code Snippet

BEGIN TRANSACTION

SELECT ... FROM Table_1 WITH (UPDLOCK) WHERE ...

DELETE FROM Table_1 WHERE ...

COMMIT TRANSACTION

INSERT INTO Store VALUES (...)

EXEC ...

You'll have to be very careful if you encounter any errors, you cannot roll the transaction back.

Hope that helps.

Jamie

|||

To ensure that when the first machine runs the procedure the second one can't read the row that is being updated by the first one, add 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' at the beginning of the stored procedure. Also, add BEGIN TRAN and COMMIT TRAN to the beginning and end of the procedure. Here is the updates:

Code Snippet

Create Procedure [dbo].[spTestQueue]

@.Tbl as varchar(100)

AS

Begin

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

Declare @.No varchar(10)

Select Top 1 @.No= Cast(Col1 as varchar(10)) from Table_1(nolock) Where Enabled = 0

Update Table_1 Set Enabled =1 Where Col1 = Cast(@.No as int)

EXEC ('Insert ' + @.Tbl + ' values(' + @.No + ')')

COMMIT TRANSACTION

End

I hope this answers your question.

Best regards,

Sami Samir

|||

I can think of a couple of solutions depending upon just how active this table is.

If you can afford to serialise the access to this table for this routine then you could use an application lock. This allows you to place a lock (like a critical section) over the pair of operation SELECT and UPDATE. This will prevent 1 app running the SELECT before another has run the update. As long as these run quickly then you will not get excessive contention.

You use the sp_getapplock and sp_releaseapplock procedures.

If you set a reasonable timeout on the the sp_getapplock call then this will cleanly serialise the operations.

If you cannot afford to serialise then I would suggest using a GUID to mark your record and retrieve it. You add a column of type uniqueidentifier to your table which starts off as null. And then you select it in this way. There is a sample of doing that below.

Code Snippet

DECLARE @.Tag_ID as uniqueidentifier

SET @.Tag_ID = NEWID()

UPDATE TOP (1) Table_1

SET Select_Key = @.Tag_ID

WHERE (Select_Key IS NULL)

SELECT @.No = CAST(Col1 as varchar(10))

FROM Table_1 (nolock)

WHERE (Select_Key = @.Tag_ID)

UPDATE Table_1 SET Enabled=1

WHERE (Col1 = CAST(@.No as int))

As the guid will be unique you will always get the record and noone else will get it. I have left it using enabled to mark when a record has been taken up.

|||

Went off and found my old applock code. This is a sample of how to use applocks to serialise the multiple runs. It will wait 5 seconds before failing. Any application lock (with owner of transaction) is automatically released if the transaction is committed or rolled back.

Code Snippet

-- Start a transaction and lock the App object
BEGIN TRANSACTION
-- Attempt to acquire the Table_1 queue processing Application Lock
EXEC @.lResVal = sp_getapplock
@.Resource = 'Table_1-QueueProcess',
@.LockMode = 'Exclusive',
@.LockOwner = 'Transaction',
@.LockTimeout = 5000

-- Check for failure
IF (@.lResVal >= 0)
BEGIN
-- The lock was acquired
-- Perform the acquisition of a record
SELECT TOP (1) ....
.
.
UPDATE Table_1 ....

-- Commit the transaction (will release lock) as record has been acquired
COMMIT TRANSACTION

-- Run the rest of the code
EXEC (' ....

END

ELSE
BEGIN
-- Failure to lock the sequence resource - so no action
ROLLBACK TRANSACTION
END

|||I thought about using Isolation level SERIALIZABL. But I don't think that will scale since this is highly transactional table|||

First, if you are using SQL Server 2005 then please don't spend time reinventing the wheel but instead use the Service Broker functionality that is built into the database engine. This gives a scalable queueing infrastructure among other things. See Books Online for more details.

If you are on older version of SQL Server then you can do below instead without need for doing the SELECT:

Code Snippet

DECLARE @.Col1 int;

SET ROWCOUNT 1;

UPDATE Table_1

SET @.Col1 = Col1

, Enabled = 1

WHERE Enabled = 0;

SET ROWCOUNT 0;

The UPDATE statement takes exclusive lock on the row so there will be no conflict. You can simplify it in SQL Server 2005 using the TOP clause like:

Code Snippet

UPDATE TOP(1) Table_1

SET @.Col1 = Col1

, Enabled = 1

WHERE Enabled = 0;