SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question about MAX_QUEUE_READERS


Question about MAX_QUEUE_READERS

Author
Message
WangcChiKaBastar
WangcChiKaBastar
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 485
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.
Mansfield
Mansfield
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 394
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...
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2449 Visits: 3575
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search