Are you using 2005? If so, look into the OUTPUT clause on DML statements
like DELETE.

[quoted text, click to view]

What version of SQL Server do you have? If it's SQL 2005, it already has
queues. Read up on Service Broker for the complete story.

If all you have is SQL 2000, then you can try creating a table with an
identity as the primary key. You can then to a SELECT TOP 1 with ORDER BY
on the primary key. The main problem I see is that if you are dequeuing in
order, then having multiple threads will not buy you anything - they will
block each other. Is it OK from a business perspective to have the threads
pull concurrently?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
[quoted text, click to view]
Hi all,

I have a SQL table which needs to act as a "queue". Multiple
threads/connections will be requesting the next record from the table.

What is the best way to structure the table so that unique records dequeued
to users (i.e. 2 users never have access to the same record).

Would running a transaction in Serializable mode work?

I'm a bit at lost on how to do it ... as I'm a .NET programmer. In code we
can just Synclock objects :-)

Thanks!

DML = Data Manipulation Language, and includes the basic statements that
work with data: SELECT , INSERT, UPDATE and DELETE
SQL 2000 has these statements also, just not the OUTPUT clause DELETE.

A SQL language also has DDL statements = Data Definition Language, which
create and manipulate objects: CREATE, ALTER, DROP

and also DCL statements = Data Control Language, for managing permissions:
GRANT, DENY, REVOKE

--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com


[quoted text, click to view]

Hi all,

I have a SQL table which needs to act as a "queue". Multiple
threads/connections will be requesting the next record from the table.

What is the best way to structure the table so that unique records dequeued
to users (i.e. 2 users never have access to the same record).

Would running a transaction in Serializable mode work?

I'm a bit at lost on how to do it ... as I'm a .NET programmer. In code we
can just Synclock objects :-)

The trick everyone uses to implement a database queue is to use the READPAST hint with an ORDER BY on the identity
column and TOP 1, for example:

BEGIN TRAN

DECLARE @Id INT;

SELECT TOP 1 @Id = Id
FROM MyFifoTable WITH (READPAST, XLOCK)
ORDER BY Id ASC;

IF @Id IS NOT NULL BEGIN

-- Process your data here...

DELETE MyFifoTable
WHERE Id = @Id
END;

COMMIT TRAN;

It's easy to see why this works so well. Don't be tempted to replace the SELECT...DELETE logic with a status column.
You'll have deadlock / concurrency problems under load, guaranteed.

--
Cheers,
Stefan Delmarco

http://www.fotia.co.uk

Yes, always delete the record from the queue table. Don't use status columns to indicate whether the record is waiting
to be dequeued / has been dequeued etc.

The query can be adapter to prioritised according to other criteria. You asked for a FIFO which means ordering on an
IDENTITY column. If you want to change the ordering just change the clustered index to match your order by and where
clause.

The READPAST / XLOCK hints, coupled with the deletes, is what gives this solution very good concurrency.

--
Cheers,
Stefan Delmarco

http://www.fotia.co.uk

"Mike C#" <xyz@xyz.com> wrote in
news:#YHWE2rWHHA.1016@TK2MSFTNGP04.phx.gbl:

[quoted text, click to view]

Unfortunately I'm using 2000. Hmmm DMLs do look interesting, I'm still
trying to grasp their syntax :-)

"Stefan Delmarco" <StefanDeOnline@fotia.co.uk> wrote in
news:ua4yGUsWHHA.1000@TK2MSFTNGP05.phx.gbl:

[quoted text, click to view]

Thanks for the tip - it looks great.

When you mean replace with a status column, do you mean I need to delete
the record outright from the queue?

Also, would your query work if I need to do the queue selection based on a
couple criterias (i.e. oldest record first, perhaps 1 or 2 other
criterias)?

I guess I shouldn't have said "FIFO" since it's not true FIFO, but rather I
only want the ability to select the "next pending record" once (i.e. single

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in
news:e0fIl3rWHHA.5092@TK2MSFTNGP03.phx.gbl:

[quoted text, click to view]

Unfortunately I'm using SQL Server 2000 still.

I guess FIFO queue was the wrong terminology - I do have a queue in a
sense, but the "next available record" is based on a couple selection
criteria (i.e. oldest records first, with the oldest last contact date, of
a particular campaign - ORDER BY EntryDate, ContactDate, CampaignID for
example). Thus I can't really sort by my PK.

OK, when you said FIFO before, then we all went in that direction. What it
appears that you have is multiple threads doing inserts and only one thread
doing deletes. Your determination of what is the first to be deleted can be
done based on a datetime column. Bear in mind that it is possible to have
more than one row with the same datetime value, so use a SELECT TOP 1. It
is possible to do the delete of the so-called first row and manage the
processing via a trigger. Failing that, you can do a select inside a
transaction to get the row but maintain the lock on that row until the
commit, as was shown in another post.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
[quoted text, click to view]
"Stefan Delmarco" <StefanDeOnline@fotia.co.uk> wrote in
news:ua4yGUsWHHA.1000@TK2MSFTNGP05.phx.gbl:

[quoted text, click to view]

Thanks for the tip - it looks great.

When you mean replace with a status column, do you mean I need to delete
the record outright from the queue?

Also, would your query work if I need to do the queue selection based on a
couple criterias (i.e. oldest record first, perhaps 1 or 2 other
criterias)?

I guess I shouldn't have said "FIFO" since it's not true FIFO, but rather I
only want the ability to select the "next pending record" once (i.e. single
thread the reads).