service broker message process order

Question

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>')

Answer 1

Not enough info here really, what's the schema of the table? does it have some kind of defaulted timestamp, and that's what you're ordering by? because if not, there's no order to the rows

Answer 2

To remain in the same order the messages have to be sent on the same conversation. If you generate a conversation per message (as most examples show) then the order they're delivered is not guaranteed.

Each time you use the BEGIN DIALOG statement you're creating a new conversation. Do this just once, then send all the messages on that same conversation and you'll get the sequence you expect.