Would it happen for no instance to process the second message?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Lee - 29 Dec 2005 05:31 GMT

if MySP_QueueReader is the program activated by MyQueue and
MyQueue.Max_Queue_Read=1, and the codes of MySP_QueueReader is as followed,

create MySP_QueueRead as
begin
Begin Transaction
receive top(1) xxxxxx from [MyQueue]
exec ProcessSomeThing...........
Commit
end

if messages come faster than MySP_QueueRead could process, would it happen
for no instance to process the second message?

1.After first MySP_QueueRead is activated, the second message is arriving
then. Because MyQueue.Max_Queue_Read=1, the second message would not make
another MySP_QueueRead instance to be activated.

2.The first MySP_QueueRead instance finished processing for first message
finally, and it is ended and disappear.

3.No instance to process the second message.

Would it happen?

Reply to this Message

Roger Wolter[MSFT] - 29 Dec 2005 07:24 GMT

This is definitely a bad coding practice - you should receive messages from
a queue until the queue is empty to maximize efficiency - but it will work.
Activation will activate the procedure twice to process the two messages.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

> if MySP_QueueReader is the program activated by MyQueue and
> MyQueue.Max_Queue_Read=1, and the codes of MySP_QueueReader is as
[quoted text clipped - 21 lines]
>
> Would it happen?

Reply to this Message

Remus Rusanu [MSFT] - 29 Dec 2005 17:30 GMT

The procedure will be activated again. It will also be activated, if there
are messages to be received, when the server is started up or when the
broker in the database is enabled (ALTER DATABASE ... SET ENABLE_BROKER) if
it was disabled. If there are messages to be received, activation is
enabled, the broker in the database is enabled but no procedure is launched
it would be a bug.

Ans, as Roger already said, it is much more efficient to have a receive loop
with a small timeout (seconds), to avoid the procedure from being launched
again and again for each message.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

> if MySP_QueueReader is the program activated by MyQueue and
> MyQueue.Max_Queue_Read=1, and the codes of MySP_QueueReader is as
[quoted text clipped - 21 lines]
>
> Would it happen?

Reply to this Message

Frank Lee - 30 Dec 2005 10:57 GMT

Thanks to Roger and Remus.

I know two things now,
1.the QueueReader will be activated again even it pass the time when the
message had been arriving.
2.It is much more efficient to have a receive loop with a small timeout.

However, I still would like to know the reason why the QueueReader will be
activated again. If it's the time to activated QueueReader when there is
message arriving (as I read from SQL Server help and some other docs), why
will QueueReader be activated again after it ending? In the queue/the
Service Broker has a mechanism inside to make sure there is always one
QueueReader (at least) to process the messages? (Sorry to ask again, I just
want to make sure the reason.)

"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon> ¼¶¼g©ó¶l¥ó·s»D:u3G4W2JDGHA.272@TK2MSFTNGP10.phx.gbl...

> The procedure will be activated again. It will also be activated, if there
> are messages to be received, when the server is started up or when the
[quoted text clipped - 6 lines]
> loop with a small timeout (seconds), to avoid the procedure from being
> launched again and again for each message.

Reply to this Message

Roger Wolter[MSFT] - 30 Dec 2005 15:59 GMT

Activation check periodically to see if there are messages in the queue with
no process activated to handle them. This is to deal with cases such as
yours where the queue reader exits before the queue is empty.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

> Thanks to Roger and Remus.
>
[quoted text clipped - 23 lines]
>> loop with a small timeout (seconds), to avoid the procedure from being
>> launched again and again for each message.

Reply to this Message

Frank Lee - 31 Dec 2005 04:34 GMT

I see. It's good.

And thank you.

"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:%23Q9nToVDGHA.3856@TK2MSFTNGP12.phx.gbl...

> Activation check periodically to see if there are messages in the queue
> with no process activated to handle them. This is to deal with cases such
[quoted text clipped - 26 lines]
>>> loop with a small timeout (seconds), to avoid the procedure from being
>>> launched again and again for each message.

Reply to this Message