Everywhere I read says that messages handled by the service broker are processed in the order that they arrive, and yet if you create a table, message type, contract, service etc , and on activation have a stored proc that waits for 2 seconds and inserts the msg into a table, set the max queue readers to 5 or 10, and send 20 odd messages I can see in the table that they are inserted out of order even though when I insert them into the queue and look at the contents of the queue I can see that the messages are all in the right order.
Is it due to the delay waitfor waiting for the nearest second and each thread having different subsecond times and then fighting for a lock or something?
The reason i've got a delay in there is to simulate delays with joins etc
Thanks
demo code:
--create the table and service broker
CREATE TABLE test
(
id int identity(1,1),
contents varchar(100)
)
CREATE MESSAGE TYPE test
CREATE CONTRACT mycontract
(
test sent by initiator
)
GO
CREATE PROCEDURE dostuff
AS
BEGIN
DECLARE @msg varchar(100);
RECEIVE TOP (1) @msg = message_body FROM myQueue
IF @msg IS NOT NULL
BEGIN
WAITFOR DELAY '00:00:02'
INSERT INTO test(contents)values(@msg)
END
END
GO
ALTER QUEUE myQueue
WITH STATUS = ON,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = dostuff,
MAX_QUEUE_READERS = 10,
EXECUTE AS SELF
)
create service senderService
on queue myQueue
(
mycontract
)
create service receiverService
on queue myQueue
(
mycontract
)
GO
--**********************************************************
--now insert lots of messages to the queue
DECLARE @dialog_handle uniqueidentifier
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>1</test>');
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>2</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>3</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>4</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>5</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>6</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>7</test>')
