SQL Server and message queues

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.

From stackoverflow

• 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

  John Leidegren : Thanks for the info, I was looking for something like this, great answer!

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

  If you need a queue, use a queue.

• Add a "grabbed" column to the messages table. Everyone can try to grab a row with a single SQL statement:

  UPDATE m
  SET grabbed = 1
  FROM messages m
  WHERE id = @myid
  and grabbed = 0
  

  After this, if @@ROWCOUNT is 1, the row is yours. Otherwise, somebody else grabbed the row before you.

  SQL Server Broker or MSMQ or MQSeries introduce massive complexity and maintenance overhead. Be sure the operations guy that's receiving your stuff agrees before you start using it.