In this last entry on Service Broker enhancements I inadvertantly referred to the new poison message handling as poison conversation handling. Well, maybe it wasn't so inadvertant. So what's the difference between Service Broker's poison message handling and traditional poison message handling?
A poison message is a fact of life in transactional messaging. When a message is received from a queue, often some database action occurs as part of the same transaction. If the database action fails (say, insert of a row based on a field in the message that happens to be a duplicate key) the message is put back on the queue. Where it is received again… If the database condition that caused the first rollback to happen hasn't been resolved, the transaction will roll back again..and again..hence the term posion message.
Usually poison message handling shunts the message off to a dead letter queue. Where it can be safely ignored while the application goes on. Oh. The problem with this is: suppose the message you are ignoring is a million-dollar order. Or the executive's December check. The database transaction may have rolled back because overflow occurred on an internal variable (especially with extremely large dollar figures). I've personally seen the “executive December check overflows payroll counters“ one, back in the days of COBOL. They used fixed point decimal just like SQL/RDBMSs do today.
Since the primitive concept of Service Broker is the conversation, not the message, the message should not be ignored.
You could lose the million dollar order. Or produce cranky executives. The programmer who designed such an app (and didn't watch the dead letter queue) could be fired. There's something wrong with the conversation, it should be shut down.
The new "posion message handling" actually goes further than that. After 5 receives of the same message, Service Broker shuts down *the queues on both sides of the conversation*. You can recover from this by:
1. Either end the conversation or recieve the message without a rollback
2. And reenable the queues
You can still implement your own poison message handling, using any of the suggestions we described in our "First Look" book. You have 4 retries to do something on your own, before the automatic poison behavior kicks in.