Poison Messages - 5 times's a charm

Hello again,
I have some poison message detection in place, based on the BOL sample. My problem is that after the 5th message retry my queue goes down - that is the fifth retry on any message. In actuallity, the first message is retried 3 times and it is taken off the queue [for real], the second message comes in and on the second retry - pooof - the queue is down.

I though the poison mechanism should work on a per message basis. It there a setting for the queue I missed? Is my only chance for to fix this: re-enable the queue upon BROKER_QUEUE_DISABLED event notification?

Thanks,

Lubomir

The poison message counter is based on RECEIVE verbs being rolled back. So five RECEIVEs rolled back in a row and your queue is disabled. The number of individual messages received/rolled back is irelevant. Any RECEIVE commited will reset the counter to 0.

Unfortunately this is not configurable in any way, you only have the BROKER_QUEUE_DISABLED notification as a way to react.

HTH,
~ Remus

|||Sorry to unmark the answer. I want to have a check after a ROLLBACK on the queue to see if the queue is DISBLED? i just could not figure out the TSQL for that?

if QUEUE is disabled
ALTER [dbo].[ProcessQueue] WITH STATUS = ON

EVENT NOTIFICATION does not allow for a direct TSQL exectuion from what I have seen so far, getting the status of queue and acting on it is not straight forward.

Thanks,

Lubomir|||

Event notifications are deliverd through normal Service Broker messages, so the way to execute TSQL on an event notification is to have an activated procedure on the queue that receives the event notification and execute the TSQL in it. The name of the queue being disabled will be part of the notification message.

To get the queue status use the is_receive_enabled or is_enqueue_enabled in sys.service_queues:

if (1 = (select is_receive_enabled from sys.service_queues where name = '...'))
begin
alter queue [...] with status = on;
end

Trying to enable back the queue from the activated procedure, after the rollback, however, will probably not work, because the queue disabling mechanism is asynchronous so you're verification check query will sometimes get the status disabled, sometimes not.

I recommend you try to avoid the rollbacks in the first place. If you try to incorporate rollback in the logic then you might hit a real poison message situation in production and you system will halt to a grind, spining on this one message.

HTH,
~ Remus

|||Thank you for the reply Remus,
My logic does take out the message after 3 retries, so I figure I should be safe, no?
I perform the rollbacks to freshly process the message in the hope conditions that caused the failure have disappeared - slim chance on that and probably something that will cause more trouble than good.
What do yo think?

Lubomir|||

If what you're looking for is a retry mechanism, then you should probably consider dialog timers, perhaps used in a fashion similar to this:

BEGIN TRANSACTION;
WAITFOR(RECEIVE ... FROM ...), TIMEOUT ...;
IF MESSAGE TYPE IS 'UNRELIABLE REQUEST'
BEGIN
INSERT REQUEST MESSAGE BODY INTO USER TABLE;
BEGIN DIALOG TIMER @.someretrytimeout;
END
ELSE IF MESSAGE TYPE IS 'TIMER'
BEGIN
SELECT REQUEST MESSAGE BODY FROM USER TABLE;
-- Re-arm the timer
BEGIN DIALOG TIMER @.someretrytimeout;
END
COMMIT;

DO UNRELIABLE WORK HERE BASED ON THE RECEIVED/SELECTED REQUEST

IF SUCCEEDED
BEGIN
BEGIN TRANSACTION
SEND BACK RESPONSE;
-- RESET TIMER TO 0 (or END CONVERSATION, as appropiate)
BEGIN DIALOG TIMER 0;
COMMIT
END

This way you separate the error prone processing outside the transaction and you don't need to rollback on failure. Also, you get to controll the retry details: number of retries, time between retries, backout policy etc.

HTH,
~ Remus

Poison Message Sample in BOL

Hello Remus,

I have implemented Event Notification sample you provided in my logic, but I am perplexed that even though my watched queue does go down, I do not get message in the Event_Collector queue all the time. The Event_Collector queue does stay enabled though. Any thoughts? I guess any suggestions on debugging the event notification portion would be helpful.

Lubomir
Upon further look at the sys.event_notifications view, I discovered that my event notification is not there any more - it disappears. So the first time the queue goes down, the notification is successful and the Event Notification is still in sys.event_notifications. Second attemp removes the Event Notification from sys.event_notifications.

Lubomir|||

Can you check if the event notification infrastructure isn't receving back an error or an end dialog message on the system dialog that delivers the notifications? Look into errorlog or in Event Viewer (Application), see if you find a record similar to one of these:

Event notification conversation on dialog handle '{4B086B77-C691-DA11-AD12-00030D303B72}' closed without an error.

or:

Closed event notification conversation endpoint with handle '{B1663957-C791-DA11-AD12-00030D303B72}', due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service &apos;s&apos;.</Description></Error>'.

HTH,
~ Remus

|||Thank you Remus,

Yup it was the End Dialog message I had; Things work great now.

Lubomir

Poison Message Sample in BOL

Hi,

We've been trying to diagnose a problem with service broker's poison message detection and tried to run the sample script in BOL to see how it solved the problem. It appears the example doesn't seem to work correctly,

IF (@.messageTypeName =
'//Adventure-Works.com/AccountsPayable/ExpenseReport')
BEGIN
DECLARE @.expenseReport NVARCHAR(MAX) ;
SET @.expenseReport = CAST(@.messageBody AS NVARCHAR(MAX)) ;
EXEC AdventureWorks.dbo.AddExpenseReport
@.report = @.expenseReport ;
IF @.@.ERROR <> 0
BEGIN
/* THIS ROLLBACK INCREMENTS THE FAILURE COUNT */
ROLLBACK TRANSACTION UndoReceive ;
EXEC TrackMessage @.conversationHandle ;
END ;
ELSE
BEGIN
EXEC AdventureWorks.dbo.ClearMessageTracking
@.conversationHandle ;
END ;
END ;
ELSE

In the scenario where AddExpenseReport fails it will retry the message the next time round the loop - TrackMessage looks to see how many times the message has failed:

IF @.count > 20
/* @.count WILL NEVER GET ABOVE 5 */
BEGIN
EXEC dbo.ClearMessageTracking @.conversationHandle ;
END CONVERSATION @.conversationHandle
WITH ERROR = 500
DESCRIPTION = 'Unable to process message.' ;
END ;
ELSE

But the counter will not get to 20 as after 5 the queue will be disabled which causes ProcessExpenseReport to fail. Subsequent message cannot then be processed until the queue is re-enabled.

We have changed the sample so that once the message has failed four times it is then written to a dead message table and we can then call commit so that the queue can carry on without being disabled:

[in ProcessExpenseReport]
...
EXEC AdventureWorks.dbo.AddExpenseReport @.report = @.expenseReport ;
IF @.@.error <> 0
BEGIN
-- The message has failed, we need to work out if we are safe to
-- retry or not. If this message has failed four times already
-- we need to recieve it, save it into another table and commit to
-- avoid service broker from disabling the queue. Otherwise we can
-- rollback and try again to see if it works next time.

-- Find out how many times this message has failed
declare @.failCount int
exec GetFailCount @.conversationHandle, @.failCount output
-- If we are about to reach the threshold
if @.failCount >= 4
begin
-- Insert the message into the dead message table. A sql agent
-- job will at some point in the future put the contents of this
-- table back onto the queue for reprocessing
EXEC DeadMessage @.conversationHandle, @.expenseReport
end
else
begin
-- We are safe to rollback and take another run at it
ROLLBACK TRANSACTION UndoReceive ;
EXEC TrackMessage @.conversationHandle ;
end
END ;
ELSE
BEGIN
-- Call to SP worked we can clear down our message tracking
EXEC ClearMessageTracking @.conversationHandle ;
END ;
END ;
...
/* Procedure to find out how many times this message
has failed to be processed */
CREATE PROCEDURE GetFailCount
@.conversationHandle uniqueidentifier,
@.failCount int output as
begin
SELECT @.failCount = [count] FROM ExpenseServiceFailedMessages
WHERE conversation_handle = @.conversationHandle
end
go

/* Procedure to insert the message into the Dead message
table and end the conversation */
create procedure DeadMessage
@.conversation_handle uniqueidentifier,
@.message_body varchar(max)
as
begin
insert into ExpenseServiceDeadMessages ( message_body ) values ( @.message_body )

EXEC dbo.ClearMessageTracking @.conversation_Handle ;
END CONVERSATION @.conversation_Handle
WITH ERROR = 500
DESCRIPTION = 'Unable to process message.' ;

end
go

/* Updated version of TrackMessage which no longer checks the
fail count - it just increments each time */
CREATE PROCEDURE TrackMessage
@.conversationHandle uniqueidentifier
AS
BEGIN
IF @.conversationHandle IS NULL
RETURN ;

DECLARE @.count INT ;
SET @.count = NULL ;
SET @.count = (SELECT count FROM dbo.ExpenseServiceFailedMessages
WHERE conversation_handle = @.conversationHandle) ;

/* TrackMessage no longer checks the failure count */
IF @.count IS NULL
BEGIN
INSERT INTO dbo.ExpenseServiceFailedMessages
(count, conversation_handle)
VALUES (1, @.conversationHandle) ;
END ;
ELSE
BEGIN
UPDATE dbo.ExpenseServiceFailedMessages
SET count=count+1
WHERE conversation_handle = @.conversationHandle ;
END ;
END ;
GO

/* New table to hold the Dead messages */
CREATE TABLE ExpenseServiceDeadMessages (
message_body varchar(max)
) ;

Now this solves the problem. If a message arrives that can't be processed (either becuase the message itself is bad, or because of an system fault with another system that we interact with) the message is moved to the DeadMessage table and the queue carries on and does not get disabled.

In our application however none of this code should be required, as long as we are confident that the data in the messages are "valid". In our case the only reason the processing would fail is if an external system we are dependant fails for some reason (e.g. Maybe writing the message to disk and the disk is full ). In this scenario we want to retry the message indefinately until the error clears. If we could disable the poision message detection this would solve the problem and remove the need for this solution.

Any thoughts on this? Have we missed the point somewhere....

Cheers,

Neil.

The main problem with just disabling the poison message check is that when you roll back a message, it's still at the top of the queue so nothing else gets processed until the message succeeds. You're probably better off commiting the receive and re-sending the meaasge - maybe to some kind of retry queue - so that you don't block other messages on the queue. Keep in mind that if you have multiple messages on the same dialog you'll have to be careful how you handel this so you don't end up changing the message order. I've seen a couple examples of people who use conversation timers to implement a timed retry for cases like this when there is a possibility of the remote resource being unavailable for an extended time.|||

I'd like to add that when a queue is being disabled (e.g. because of a poison message), a BROKER_QUEUE_DISABLED event notification is fired. Here is a small demo to ilustrate this:

use tempdb;
go

create procedure sp_rollback_on_pm
as
begin
set nocount on;
begin transaction;
receive * from q_pm;
rollback;
end;
go

create queue q_pm
with activation (
status = on,
max_queue_readers = 1,
procedure_name = [sp_rollback_on_pm],
execute as owner);
go

create service s_demo_pm on queue q_pm ([DEFAULT]);
go

create queue q_en;
create service s_en on queue q_en ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification ev_qm on queue q_pm for broker_queue_disabled to service 's_en', 'current database';
go

declare @.dh uniqueidentifier;
begin dialog conversation @.dh
from service s_demo_pm
to service 's_demo_pm'
with encryption = off;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
go

select cast(message_body as xml),* from q_en
go

An administrator can receive notifications when queues are being disabled as a result of a poison message and it can inspect the queue and fix the issue or remove the problem dialog. Or an activated procedure on the q_en queue can automatically fix the issue and re-enable the q_pm queue (of course, assuming that the issue can be fixed automatically).

HTH,
~ Remus

|||Thanks for the replies - this has allowed us to progress to a past this problem.

I do entirely understand why service broker is doing what it's doing, it will quickly enable you to locate any messages that break the system doe to an application bug. However in the scenario where a message cannot be processed due to a situation beyond your control this features requires you to implement a lot more code then perhaps you should.

In our scenario, if we cannot process message A which is at the front of the queue, we are not going to be able to process message B either, so there's no point trying. The ability to turn of poision message detection would be very useful.

I imagine this has been discussed elsewhere but it also strikes me as odd that I can now write code:

-- About to start a transaction
BEGIN TRANSACTION
RECEIVE * FROM myQueue
ROLLBACK TRANSACTION
-- The database is now in a different state from when begin tran was called!!

I personally have always relied on the premise that EVERYTHING I DO (with SQL) inside a transaction is undone when I call rollback. This no longer seems to be true?

Cheers,

Neil|||

Everything you did in the transaction is undone when the transaction is roled back. The message is back in the queueand any other data updates are reversed. The queue is disabled as a result of the rollback, after the rollback occured.

In the case you describe, since the application is able to detect the problem message, I wouldn't actually call it a poison message. Is more like a message your app understands but is not willing to process. It seems to me that a more appropiate action would be to move the message into a different queue (i.e. forward the message to a secondary service) or end the dialog with an error.
The poison message was indeed thought more for cases when the application crashes when processing the message and the system spins on this message, w/o making any progress.

|||

I can see Neil's issue with the poison message handling, and the inability to turn it off. I guess he's got a queue whose processing ultimately depends on some external system (say a web service call or something). In the scenario where the web service is unavailable, you'd pull a message from the queue and try to call the web service. If the web service fails (for example, due to network issues), then you want to rollback the receive to enable that message to be retried in the future. It's not really possible for an application to know whether a network-based call is going to work in advance.

Moving the message of to another queue merely delays the inevitable - the other queue needs processing, and of course it can fail in the same way. I guess you could just take messages from the second queue and add them back to the first, but this seems like a poor solution (it also causes the messages to be reordered, which could have its own issues).

Much as I understand (and applaud) the reasoning behind poison message detection, it does seem that there are scenarios where it would be useful to just turn it off.

|||For the scenario you describe I'd recommend using conversation timers, not rollback the message. Rolling back would make the message visible again immeadetly, triggering another attempt. Instead, using a timer would allow for a delayed retry, like this:

begin transaction
Receive
if web request message
store the request data in table
else if timer message
read request data from table
begin conversation timer (e.g. 1 minute)
commit
do the web request
if succeeded
begin transaction
reset the conversation timer
send back response
commit

Ultimately, everything in the server is optimized for the commit case. Rollbacks should really be extraordinarily and this is how we approached the problem.

As a last resort, the BROKER_QUEUE_DISABLED event notification can be used to enable back the queue automatically.

HTH,
~ Remussql

Pls help. Cannot index the view...It contains one or more disallowed constructs

Thank you for your help.
What am I missing here?
I read the BOL. I just dont see what is the problem.
SET ANSI_PADDING ON
GO
SET ANSI_PADDING ON
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OrderLineSAItems]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[OrderLineSAItems] (
[olnID] [int] NOT NULL ,
[saiID] [int] NOT NULL ,
[olnsQuantity] [int] NOT NULL
CONSTRAINT [PK_OrderLineSAItems_olnID] PRIMARY KEY CLUSTERED
(
[olnID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OrderLineOptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[OrderLineOptions] (
[olnID] [int] NOT NULL ,
[optID] [int] NOT NULL ,
[olnoValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[olnoEngValue] [decimal](19, 6) NULL ,
CONSTRAINT [PK_OrderLineOptions_olnID_optID] PRIMARY KEY CLUSTERED
(
[olnID],
[optID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
IF OBJECT_ID('dbo.OrderLineOptionChecksums') IS NOT NULL and
objectproperty(OBJECT_ID('dbo.OrderLineOptionChecksums'),'IsView') = 1
DROP VIEW dbo.OrderLineOptionChecksums
GO
CREATE VIEW dbo.OrderLineOptionChecksums
WITH SCHEMABINDING
AS
SELECT
olns.olnID
,CHECKSUM_AGG(
binary_checksum (
olns.saiID
, olno.optID
, olno.olnoValue
, olno.olnoEngValue
)
)
as OptionsChecksum
,count_big(*) as CntBig
FROM dbo.OrderLineSAItems olns
JOIN dbo.OrderLineOptions olno ON olno.olnID = olns.olnID
GROUP BY olns.olnID
GO
CREATE UNIQUE CLUSTERED INDEX UX_OrderLineOptionChecksums_olnID ON
dbo.OrderLineOptionChecksums ( olnID )
go
CREATE NONCLUSTERED INDEX IX_OrderLineOptionChecksums_OptionsCheck
sum ON
dbo.OrderLineOptionChecksums ( OptionsChecksum )
go
-- $Log$
GOThank you, Jacco, for your explanation. It's not what I wanted to hear
though. I think it's also quite a limitation. That means that I need to use
trigger to maintain this checksum.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>I think the problem is with CHECKSUM_AGG. The idea behind the
>implementation of indexed views is that when a row is
>inserted/updated/deleted in one of the underlying tables, the new values in
>the indexed views can be calculated from just the changes in the underlying
>tables, without having to access any other rows in the table(s). I don't
>think this is the case with CHECKSUM_AGG, or in other words, if for example
>you delete a row from a table, you can't calculate the new value for the
>CHECKSUM_AGG by deducting the checksum for the deleted row from the value
>that was in the view previously. You would have to access the other rows in
>the table to recalculate the checksum.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23YeZeqyUFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Jacco
I was just reading more on indexed views and I have a question.
How would you explain
SUM(X), COUNT_BIG(X)
being allowed then? It has to go and re-read all other rows to get a new sum
if a row is deleted/updated/inserted.
Thanks
Vlad
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>I think the problem is with CHECKSUM_AGG. The idea behind the
>implementation of indexed views is that when a row is
>inserted/updated/deleted in one of the underlying tables, the new values in
>the indexed views can be calculated from just the changes in the underlying
>tables, without having to access any other rows in the table(s). I don't
>think this is the case with CHECKSUM_AGG, or in other words, if for example
>you delete a row from a table, you can't calculate the new value for the
>CHECKSUM_AGG by deducting the checksum for the deleted row from the value
>that was in the view previously. You would have to access the other rows in
>the table to recalculate the checksum.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23YeZeqyUFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||No, because if you add a row (or multiple rows) to a base table for an
indexed view you can calculate the new values for SUM and BIG_COUNT from the
existing values for SUM and BIG_COUNT and the values for the inserted
row(s).
If you have a table
CREATE TABLE t(i INT IDENTITY PRIMARY KEY, v INT NOT NULL)
with an indexed view SELECT SUM(v) as sum_v, COUNT_BIG(*) as cnt
If you have 2 rows in the table:
1,2 and 2,3
The indexed view will have the values: 5, 2
When you insert another row into the table with v = 4, you can update the
view by adding 4 to the value for sum_v that is already there (5), and
increasing the value for cnt by 1. There is no need to revisit the rows that
are already in the table. This doesn't work for CHECKSUM_AGG though.
Jacco Schalkwijk
SQL Server MVP
"Farmer" <someone@.somewhere.com> wrote in message
news:%23IgX3PYVFHA.544@.TK2MSFTNGP15.phx.gbl...
> Jacco
> I was just reading more on indexed views and I have a question.
> How would you explain
> SUM(X), COUNT_BIG(X)
> being allowed then? It has to go and re-read all other rows to get a new
> sum if a row is deleted/updated/inserted.
> Thanks
> Vlad
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>|||Thank you.
Your explanation makes total sense and your logic is very sound.
However, I am still disapointed that this is the case, even though you are
right, that SQL does not deal with this problem.
It would have been such a powerful feature.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:OvC5WzYVFHA.3544@.TK2MSFTNGP12.phx.gbl...
> No, because if you add a row (or multiple rows) to a base table for an
> indexed view you can calculate the new values for SUM and BIG_COUNT from
> the existing values for SUM and BIG_COUNT and the values for the inserted
> row(s).
> If you have a table
> CREATE TABLE t(i INT IDENTITY PRIMARY KEY, v INT NOT NULL)
> with an indexed view SELECT SUM(v) as sum_v, COUNT_BIG(*) as cnt
> If you have 2 rows in the table:
> 1,2 and 2,3
> The indexed view will have the values: 5, 2
> When you insert another row into the table with v = 4, you can update the
> view by adding 4 to the value for sum_v that is already there (5), and
> increasing the value for cnt by 1. There is no need to revisit the rows
> that are already in the table. This doesn't work for CHECKSUM_AGG though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23IgX3PYVFHA.544@.TK2MSFTNGP15.phx.gbl...
>|||Yes, but it wouldn't have been possible to have all the performance
optimizations that indexed views provide if constructs like CHECKSUM_AGG
were allowed. And you can still do what you want with a non-indexed view or
a trigger.
Jacco Schalkwijk
SQL Server MVP
"Farmer" <someone@.somewhere.com> wrote in message
news:efPG8QZVFHA.548@.tk2msftngp13.phx.gbl...
> Thank you.
> Your explanation makes total sense and your logic is very sound.
> However, I am still disapointed that this is the case, even though you are
> right, that SQL does not deal with this problem.
> It would have been such a powerful feature.
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:OvC5WzYVFHA.3544@.TK2MSFTNGP12.phx.gbl...
>