CODEWITHSUNDEEP
sql-server-2008service-broker
ca9163d9
ca9163d9

Reputation: 29159

Stop a queue's N consumers

I have an SSBS queue and a main process send M messages to the queue. There are N sub-processes which fetch the messages one by one and process them. The sub-processes will exit after all the messages are processed. Right now I am doing the following,

Approach 1:

The main process send N "EndOfData" after sent M messages. However it doesn't work well since some sub-processes may receive more than one "EndOfData" messages so some sub-processes will never get the message.

Approach 2: (extend approach 1)

Assign each sub-process an ID, also embed an ID in the "EndOfData" message. The sub-process rollback if the ID in the message doesn't match its ID. However, it causes "poise message" issue because of too many rollback and the queue get disable. 

begin tran
begin try
    WAITFOR(
        RECEIVE TOP(1)
            @MessageType = message_type_name,
            @MessageBody = CAST(message_body AS xml)
        FROM
            TargetQueue
    ) , TIMEOUT 1000
    if @MessageType = 'EndOfData' and 
       @MessageBody.value('(//ID/text())[1]', 'int') <> @ID
    BEGIN 
        rollback tran
        waitfor delay '00:00:02'
    END

Is there a good way to implement it?

Update:
The sub-processes will do the following steps.

  1. Receive one message
  2. If "Work",
    1. process the message (may take several minutes)
    2. Send a "Info" message to the queue to info main process the message has been processed
  3. If "EndOfData",
    1. Send a message "Exit" to main process
    2. Receive thehttps://..../EndDialog of "Exist" message and exit
  4. If https://.../EndDialog of messages sent in 2.2, just receive it

Upvotes: 0

Views: 72

Answers (2)

ca9163d9
ca9163d9

Reputation: 29159

Using the following code and put the working in the transaction. So the script block can exit after receive the EndOfData message.

$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.DatabaseName = "..."
$conn.BeginTransaction()
$conn.ExecuteNonQuery("Receive message;")
# do work
$conn.CommitTransaction()

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.committransaction.aspx

Upvotes: 0

Serg
Serg

Reputation: 2427

One more approach, supposing N = 3:

  1. Send message of "EndOfData" type containing 3.
  2. One of 3 sub-processes receives this message, sends response message containing 2, exits. So 2 running sub-processes left.
  3. Receive response with 2 and send message of "EndOfData" type containing 2.
  4. One of 2 sub-processes receives this message, sends response message containing 1, exits. So 1 running sub-processes left.
  5. Receive response with 1 and send message of "EndOfData" type containing 1.
  6. The last running sub-process receives this message, sends response message containing 0, exits. No running sub-processes left.
  7. Receive response with 0 and stop sending messages of "EndOfData" type.

Upvotes: 1

Related Questions