WangcChiKaBastar (5/1/2013)
Guys,I have a Question about MAX_QUEUE_READERS option in Service Broker.
In a high transaction environment where inserts/updates/deletes are very high, would it be affected by
having MAX_QUEUE_READERS value higher than 1 ?
What happens when a INSERT statement is issued and then Immediately DELETE is issued for the same record
and because of MAX_QUEUE_READERS being 2 or 3 ...DELETE gets processed before INSERT (Parallel Processing) ?
OR I am not correct with my understanding of MAX_QUEUE_READERS ?
I have a Production Issue where sometimes Queue backs up because of high User Activity and we have MAX_QUEUE_READERS set as 1.
I am thinking of increasing it to may be 5, but would like to know ,that we don't end up with Orphan Records due
to multi-threaded parallel process created by MAX_QUEUE_READERS.
Thanks.
Service Broker guarantees in order processing of messages only within the same conversation or conversation group. If the INSERT and DELETE statements are on different conversations or conversation groups, then there is no guarantee the the INSERT message will be processed before the DELETE message. This can also happen with only one queue reader.