Poison Messages en SQL Server Service Broker, ¿qué son y cómo evitarlos?

En varios proyectos en los que estoy trabajando estamos utilizando una característica que se introdujo en SQL Server 2005, Service Broker, SSB para los amigos. Brevemente diré que SSB proporciona una plataforma de comunicaciones basada en mensajes que forma parte del motor de base de datos, si queréis saber algo más tenéis información detallada aquí y aquí.

El caso es que cuando empezamos a hacer pruebas de nuestra aplicación nos encontramos que a veces se detenía el servicio sin motivo aparente, y buscando información nos encontramos con los poison messages.

¿Qué es un poison message?
Un poison message (traducido literalmente “mensaje envenenado”) es un mensaje en una cola que ha excedido el número máximo de intentos de ser recibido por una aplicación. Esta situación puede producirse, por ejemplo, cuando una aplicación lee un mensaje de una cola como parte de una transacción, pero debido a errores (error de conversión, duplicidad de clave,...) no puede procesarlo. Si la aplicación aborta la transacción en la que se recibió el mensaje, éste es automáticamente devuelto a la cola. Si el problema con el mensaje no es solucionado puede provocarse un bucle infinito, provocando un poison message.

Service Broker provee detección automática de poison messages que deshabilita la cola (y te aseguro que no hace ninguna gracia que te pase esto en producción) si una transacción que recibe mensajes de una cola hace rollback 5 veces. Si se llega a dar este caso, el mensaje debe ser eliminado por una aplicación diferente o por un administrador de forma manual.

La detección automática también genera un evento de tipo Broker: Queue Disabled, que puede ser interceptado por un procedimiento almacenado que reactive la cola (vuelva a establecer su STATUS a ON). Además se pueden usar las alertas de SQL Agent para notificar al administrador que la cola se ha desactivado para que actúe en consecuencia.

¿Cómo tratar los poison message?
Debido a que la detección automática de poison messages provoca la detención de la cola, y por tanto, de todos los procesos asociados a ella, las aplicaciones deberían, como parte de su lógica, detectar y sacar de las colas de forma segura los mensajes que sean susceptibles de convertirse en poison messages.

- Usar bloques TRY ... CATCH para capturar todos los errores. Comprobar si la transacción (en caso de que exista) se pueda confirmar con la función XACT_STATE().

- Cuando se detecte un error que potencialmente pueda provocar un poison message, asegurarnos de que el mensaje ha sido sacado de la cola y finalizar la conversación con END CONVERSATION ... WITH ERROR.

- También es muy recomendable guardar el mensaje que provocó el poison message para su posterior tratamiento (podemos corregirlo y volver a introducirlo en el ciclo normal) y guardarnos un log.

Un control de poison messages podría ser algo así:

BEGIN TRY
    BEGIN TRANSACTION
    RECEIVE
    .
    .
    .  
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
DECLARE @state INT;
    SET @state = XACT_STATE();
    SELECT @error = ERROR_NUMBER(), @error_message  = ERROR_MESSAGE();

    IF @state = 1 -- Hay transacción de usuario activa y se puede confirmar
    BEGIN
          -- Se finaliza la conversación con error
          END CONVERSATION @conversation_handle
WITH ERROR = @error description = @error_message;
          COMMIT;
    END
    ELSE IF @state = -1
    -- Hay transacción de usuario activa y no se puede confirmar, sólo revertir
    BEGIN
          ROLLBACK TRANSACTION; -- Se revierte la transacción     
       
          -- Se saca de la cola el mensaje y se finaliza la conversación con error
          BEGIN TRANSACTION;
          RECEIVE TOP(1) @msgBody = message_body
                FROM [queue]
                WHERE conversation_handle = @conversation_handle;
          END CONVERSATION @conversation_handle
WITH ERROR = @error description = @error_message;
          COMMIT;
    END
END CATCH

He de reconocer que no es una solución perfecta pero por ahora es lo mejor que he encontrado y funciona bastante bien. En todo caso os recomiendo el blog de Remus Rusanu para profundizar en el uso de Service Broker.

Bola extra

Por si a alguien le interesa, buscando información sobre los poison messages en Service Broker me encontré que MSMQ realiza un tratamiento parecido, tenéis más información en esta página.

Edito: (08/09/08) hasta hoy no me di cuenta que lo de Bola Extra es una copia descarada, digo ... homenaje a los camaradas de Halon disparado, si es que de lo que se come se cría :p