I’m a DBA, so that flavors my my response, but here’s what I’d do:
- If you’re using SQL 2005+, use Service Broker to store the messages
in the database rather than storing them in a table. You get a
queueing mechanism with this, so you can get rid of MSMQ. You’ll also have a table, but it’s just going to store the conversation handle (essentially, a pointer to the message) along with how many times it attempted this message. Lastly, you’ll want some sort of a “dead letter box” where messages that reach your retry threshold go.
- In your message processing code, do the following:
- Begin a transaction
- Receive a message off of the queue
- If the retry count is greater than the threshold, move it to the dead letter box and commit
- Increment the counter on the table for this message
- Process the message
- If the processing succeeded, commit the transaction
- If the processing failed, put a new message on the queue with the same contents and then commit the transaction
Notice that there aren’t any planned rollbacks. Rollbacks in Service Broker can be bad; if you rollback 5 times without a successful receive, the queue will become disabled for both enqueuing and dequeuing. But you still want to have transactions for the case when your message processor dies in the middle of processing (i.e. the server crashes).