SQL Server and message queues

Question

I'm trying to build a reliable message service, or at least that's how I would describe it.

Here's my problem: I have a table, I insert data into this table, I have at least two applications which select data from this table. However, I need a reliable way for the two different applications to never select the same rows at any given time.

How would I go about writing a transaction or select statement that is guaranteed to not select the same rows that other applications have recently selected.

I'm not an SQL Server expert, but I would expect something similar to this.

Select work from table, this will give the application exclusive access to some rows. The application would then process those rows. Some rows get deleted, some rows are returned to the database.

My main concern is that if the application fails to complete it's processing, SQL Server would eventually time out and return the checked out data to the application data pool.

Answer 1

this is quite an old question and probably the author's problem is long gone now but there's a little SQL trick that solves it and can be useful to others: the 'readpast' hint. With readpast sql select will skip locked rows in a query. What you want is to grab first unlocked row and immediately lock it so no one else will get it:

select top 1 * 
from messages with(readpast, updlock) 
order by id

This will select and lock the first free message in the table and so no one else will be able to modify it. Actually I'm using the same approach in SQL-based message bus: http://http://code.google.com/p/nginn-messagebus/ and it works very smoothly (sometimes faster than MSMQ)

Answer 2

Take a look at SQL Server Service Broker, it is designed for this kind of problem.

If you need a queue, use a queue.

Answer 3

Add a 'Being Processed' Flag to each record, which is set in an 'atomic' way.

There have been several questions and many answers here on SO, quite similiar to this:

How do I lock certain SQL rows while running a process on them?

Queues against Tables in messaging systems

What’s the best way of implementing a messaging queue table in mysql

Posting Message to MSMQ from SQL Server