sql - Service Broker 消息处理顺序

Question

我读到的所有地方都说由服务代理处理的消息是按照它们到达的顺序处理的，但是如果你创建一个表、消息类型、契约(Contract)、服务等，并且在激活时有一个存储过程等待 2秒并将消息插入表中，将最大队列读取器设置为 5 或 10，并发送 20 条奇数消息我可以在表中看到它们是乱序插入的，即使当我将它们插入队列并查看队列的内容我可以看到消息的顺序都是正确的。

是不是因为延迟等待等待最近的秒并且每个线程具有不同的亚秒时间然后争夺锁或其他原因？

我在那里有延迟的原因是用连接等模拟延迟

谢谢

演示代码:

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

为了保持相同的顺序，消息必须在同一个 session 中发送。如果您为每条消息生成一个对话(如大多数示例所示)，则无法保证它们的传递顺序。

每次您使用 BEGIN DIALOG 语句时，您都在创建一个新对话。只执行一次，然后在同一个对话中发送所有消息，您将获得预期的顺序。