Processing table rows by multiple threads

[Marcin]

Hi,

My problem is probably common... I have a table with sms messages,
with statuses like New, Processing, Sent. There is one application
component that inserts messages and several instances of another
component that actually send those messages and update status to Send.
This is paralell processing.

My solution was to write a cursor (select * ... where status=new) that
reads new messages in packages by ten and changes status to
Processing. Each instance of sending component gets messages to send
using this cursor and then updates each message status after sending.

The question is: Am I forced to use cursor? The cursor performance is
terrible and I'd like to avoid using it.

m.

[Erland Sommarskog]

Post by Marcin
My problem is probably common... I have a table with sms messages,
with statuses like New, Processing, Sent. There is one application
component that inserts messages and several instances of another
component that actually send those messages and update status to Send.
This is paralell processing.
My solution was to write a cursor (select * ... where status=new) that
reads new messages in packages by ten and changes status to
Processing. Each instance of sending component gets messages to send
using this cursor and then updates each message status after sending.
The question is: Am I forced to use cursor? The cursor performance is
terrible and I'd like to avoid using it.

Since this obviously is a queue, did you ever consider using Service Broker?

Else you can do:

UPDATE TOP (@batchsize) tbl
SET status = 'Processing'
OUTPUT inserted.*
FROM tbl

This will update 10 rows in the table and return these to the client.
However, if the client crashes, the rows will be in Processing forever,
unless you have means to recover from this.

You can achieve better concurrency with the READPAST hint, but it still
requires careful consideration. With Service Broker, Microsoft has done a
lot of that work for you.