Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Working with Queues in SQL Server Expand / Collapse
Author
Message
Posted Saturday, April 03, 2010 12:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 12:24 PM
Points: 39, Visits: 157
Comments posted to this topic are about the item Working with Queues in SQL Server
Post #896300
Posted Sunday, April 04, 2010 10:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 01, 2012 1:18 PM
Points: 110, Visits: 261
FYI - the OUTPUT clause is available in sql2k5 as well.
-a
Post #896525
Posted Monday, April 05, 2010 2:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 11,168, Visits: 10,932
Setup:
-- For the demo
USE tempdb;
GO
IF OBJECT_ID(N'dbo.WorkQueue', N'U')
IS NOT NULL
DROP TABLE dbo.WorkQueue;

IF OBJECT_ID(N'dbo.GetWorkItem', N'P')
IS NOT NULL
DROP PROCEDURE dbo.GetWorkItem;

IF OBJECT_ID(N'dbo.SetWorkItemComplete', N'P')
IS NOT NULL
DROP PROCEDURE dbo.SetWorkItemComplete;

-- Create table
CREATE TABLE dbo.WorkQueue
(
work_queue_id INTEGER IDENTITY(1,1) NOT NULL
PRIMARY KEY,
name VARCHAR(255) NOT NULL,
status_id TINYINT NOT NULL
CHECK (status_id IN (0, 1, 2)),
status_desc AS
ISNULL
(
CASE status_id
WHEN 0 THEN 'Unprocessed'
WHEN 1 THEN 'In progress'
WHEN 2 THEN 'Processed'
ELSE NULL
END,
'')
);

-- Add data to table
INSERT dbo.WorkQueue
(name, status_id)
SELECT 'A', 0 UNION ALL
SELECT 'B', 0 UNION ALL
SELECT 'C', 0 UNION ALL
SELECT 'D', 0 UNION ALL
SELECT 'E', 0 UNION ALL
SELECT 'F', 0 UNION ALL
SELECT 'G', 0 UNION ALL
SELECT 'H', 0 UNION ALL
SELECT 'I', 0 UNION ALL
SELECT 'J', 0 UNION ALL
SELECT 'K', 0;
GO
-- Index to help find unprocessed items
CREATE NONCLUSTERED INDEX nc1
ON dbo.WorkQueue (status_id);

Procedures:
CREATE  PROCEDURE dbo.GetWorkItem
@WorkQueueID INTEGER OUTPUT
AS
BEGIN
SET NOCOUNT ON;

-- Allocate a work item and set it to in-progress
-- TODO: Add lock timeout and error handler
UPDATE Q
SET status_id = 1,
@WorkQueueID = work_queue_id
FROM (
SELECT TOP (1)
Q2.work_queue_id,
Q2.status_id
FROM dbo.WorkQueue Q2 WITH (UPDLOCK, READPAST, ROWLOCK)
WHERE Q2.status_id = 0
ORDER BY
Q2.work_queue_id ASC
) Q;

RETURN CASE WHEN @WorkQueueID IS NULL THEN 999 ELSE 0 END
END;
GO
CREATE PROCEDURE dbo.SetWorkItemComplete
@WorkQueueID INTEGER
AS
BEGIN
SET NOCOUNT ON;

-- Set the work item's status to complete
-- TODO: Add lock timeout and error handler
UPDATE Q
SET status_id = 2
FROM WorkQueue Q
WHERE Q.work_queue_id = @WorkQueueID
AND Q.status_id = 1;

RETURN CASE WHEN @@ROWCOUNT = 0 THEN 999 ELSE 0 END;
END;
GO

Test:
DECLARE @WorkItemID INTEGER,
@ReturnCode INTEGER;

-- Get a work item
EXECUTE @ReturnCode =
dbo.GetWorkItem @WorkItemID OUTPUT;

-- Show the record
SELECT rc = @ReturnCode,
*
FROM dbo.WorkQueue
WHERE work_queue_id = @WorkItemID;

-- Simulate processing delay
WAITFOR DELAY '00:00:03';

-- Set the work item to complete
EXECUTE @ReturnCode =
dbo.SetWorkItemComplete @WorkItemID;

-- Show the record
SELECT rc = @ReturnCode,
*
FROM dbo.WorkQueue
WHERE work_queue_id = @WorkItemID;

GO
DROP TABLE dbo.WorkQueue;
DROP PROCEDURE dbo.GetWorkItem;
DROP PROCEDURE dbo.SetWorkItemComplete;

Results:
rc  work_queue_id   name    status_id   status_desc
0 1 A 1 In progress
...
0 1 A 2 Processed


Allocation routine query plan: (compute scalars removed)





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Plan.gif (521 views, 5.50 KB)
Post #896580
Posted Monday, April 05, 2010 5:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 24, Visits: 221
Much better article at http://rusanu.com/2010/03/26/using-tables-as-queues/
Post #896610
Posted Monday, April 05, 2010 8:11 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
Hey folks. I recently built a system where users share a pool of work items and I too use a table-based queue. So I feel kind of familiar with some of the issues.

It seems to me that the proposed solution handles the initial concurrency problem well. There's no chance that two people will get the same open item in the queue. So it solves what I call the reservation problem. You want users to be able to reserve or lock an item.

However, another problem is getting disconnected. It's the "orphaned work item" issue. The calling app, if a web application, for instance - may drop the connection. Then a row may stay marked as processed/reserved and stay that way. In order to handle these situations, you either have to ensure that it doesn't happen up-front (sometimes not possible) - or have a way to identify rows not being worked by a user.

I've used the table-based method of reserving rows for a work queue with a web application front-end. There are occasions when a session end or the user gets disconnected. I handle the getting disconnected problem by programming the application "session end" event. In the event, I "un-mark" the row in question using the application. That works most of the time - but there are occasions when that event isn't called - and I then have to un-mark those rows another way.

Now, when that happens, there's the problem of identifying rows that have been marked as reserved but are not being handled by a current user. Due to this problem, I chose to do something slightly different than adding a flag column and using the query hints.

I created an additional history table for item reservations. I add a row when a work item is reserved by a user. It includes a session id, item id, expiration flag, start date and end date. The bi-temporal approach allows me to automatically "unlock" items within a specified period of time - such as a day.

It's actually nice to keep a history table for item reservations - and in some cases, it can help satisfy "chain of custody" requirements. It is also a way to handle the orphaned work item issue.

I welcome your thoughts and critiques. Thanks.


Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Post #896727
Posted Monday, April 05, 2010 8:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
One more comment: The use of READPAST should be carefully considered for two reasons:
1. If you need to preserve the order you must exclude it
2. If you are using READ_COMMITED_SNAPSHOT ON at the database level and The transaction isolation level of the session is READ COMMITTED.




* Noel
Post #896731
Posted Monday, April 05, 2010 8:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 11,168, Visits: 10,932
Bill Nicolich (4/5/2010)
...when that happens, there's the problem of identifying rows that have been marked as reserved but are not being handled by a current user.

A history table works well. For simple implementations, adding columns to the queue table to store the process/user identifier that owns the resource, and the time it acquired it, can work well (enough) too.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896736
Posted Monday, April 05, 2010 9:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
Paul White NZ (4/5/2010)
Bill Nicolich (4/5/2010)
...when that happens, there's the problem of identifying rows that have been marked as reserved but are not being handled by a current user.

A history table works well. For simple implementations, adding columns to the queue table to store the process/user identifier that owns the resource, and the time it acquired it, can work well (enough) too.


When you match up a work item with a user, there are some considerations. What constitutes a user? In my case, a user was a unique session id from the web server. It wasn't a web farm situation. A user could have IE open with one session and FireFox open with another session - and these in my case should be handled separately. So if I just had just a "user id" matching things up in the table, I'd run into problems. So, that's why I added both a session id and a user id in the table.


Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Post #896775
Posted Monday, April 05, 2010 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 19, 2011 7:08 AM
Points: 1, Visits: 9
For the Demo,

If doing this in 2008 and you just use the update "with (READPAST), If you do not move the Delay to before the commit Both sessions will pick up the same record.

Eric.
Post #896814
Posted Monday, April 05, 2010 9:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:46 AM
Points: 123, Visits: 513
when i first looked at the title articles, i was sure it talkes about queues, that work in FIFO - first in -> first out.
but when i saw the row
SELECT TOP(1)
@work_queue_id = work_queue_id, @name = name
FROM
work_queue
WHERE processed_flag = 0

it dosen't ensure me that i will get the first available row from the table!
maybe it will be better to work with the Queue that the sql provides you, rather then using a table, and getting into situation where you find your self with looked rows and so on like Bill said.
Post #896818
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse