Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question about MAX_QUEUE_READERS Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 2:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 2, 2014 11:14 AM
Points: 103, Visits: 450
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.
Post #1448609
Posted Wednesday, May 1, 2013 10:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 1, 2014 3:42 PM
Points: 364, Visits: 386
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...
Post #1448674
Posted Thursday, May 2, 2013 8:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 1,890, Visits: 3,472
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.
Post #1448826
Posted Thursday, June 6, 2013 12:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 6, 2013 12:29 AM
Points: 4, Visits: 12
You may use some Advanced SQL Concepts while implementing this feature as it will be best.

Resort in silvassa
Post #1460538
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse