This is my scenario:

I have a table of emails. A process removes a set of emails that are queued up with SELECT TOP 10 * FROM EMAIL WHERE STATUS='OP'. It then sends the emails out through SMTP and updates the status to SE. So far its working great.

Now I want to scale out the solution to have several processes dequeueing. Obviously I need to select the next n records and update their status inside the same transaction so that the other process does not grab them. I am thinking about adding an intermediate status PN to denote when the client is processing those records.

I have three questions:

1. What would the sql look like that grabs the next n emails, updates their status to PN and returns them to the client?

2. Should I hold a lock on those records while the client is processing them or should I release the lock? What if the client dies? I don't want to lock up the table if I don't have to.

3. How can I ensure that if a client puts them into PN status but they never get put into SE status that I can retransmit? Would a timestamp work?

There must be a common pattern that handles this scenario. Is it discussed anywhere?

Thanks in advance