May 1, 2013 at 2:19 pm
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.
May 1, 2013 at 10:04 pm
Regardless of the number of queue readers, items in the queue will be picked up in a FIFO manner. From the Service Broker point of view, it will always be logically correct.
However, you'll need to test the actual work inside the activation procedures to ensure your process still remains in sequence.
A somewhat facetious example:
create procedure pInsert
as
begin
waitfor delay '0:00:20';
insert into dbo.table1 values ('blah', 'blah', 'blah');
end;
create procedure pDelete
as
begin
delete from dbo.table1 where column1 = 'blah';
end;
It *is* possible that your delete could outrun your insert and produce undesirable results...
May 2, 2013 at 8:01 am
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.
June 6, 2013 at 12:33 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply