Service Broker Transactional Support in SQL Server 2005 Express Edition

February 19, 2009

In the recent installments of our series covering topics related to SQL Server 2005 Express Edition, we have been focusing on specifics of its Service Broker implementation. Throughout our discussions, we have been applying a certain degree of simplification, primarily for the sake of clarity and conciseness. It is important, however, to note that some of these temporary shortcuts should be treated purely as such and avoided in "real-world" deployments. An important example in this category is the role of transactions in processing Service Broker dialogs. In this article, we will provide its overview and present an example illustrating its usage.

Transactional functionality is essential in database operations, in order to satisfy atomicity, consistency, isolation and durability requirements (which are frequently referenced collectively using the acronym ACID). These terms describe features critical to data processing, which ensure that each modification has a clearly defined outcome, resulting in either success or failure (atomicity), thus preventing potential corruption of data (consistency), executes independently from other changes (isolation), and, once completed, leaves underlying data in a defined state until another transaction takes place (durability). Complying with these rules is especially important when dealing with multiple, interdependent changes. The scope of these changes can vary, ranging from a few database objects within the same database or server instance, to any number of distributed systems, as is frequently the case in online processing or messaging scenarios. Incorporating several distinct activities into the same transaction guarantees that if any of them fails, all of the remaining ones are rolled back, returning the system to its original state. (In other words, all of them have to either succeed or fail - no other outcome is permitted).

Service Broker relies on the transactional mechanism inherent to SQL Server (which, incidentally, constitutes one of its bigger strengths). This means that you can incorporate its operations into arbitrary transactions, in order to ensure that a sequence of related actions (including processing of messages and related database updates) has a determinate, consistent outcome (either all of them succeed or fail). Enclosing such sequence into a transaction means that rollback will effectively prevent messages from being sent out if any associated SQL Server 2005 database-bound actions fail (and, in the equivalent manner, commit will serve as the guarantee that all of them have completed).

Let's consider our most recent example illustrating a secure Service Broker dialog with internally activated stored procedure dbo.cspProcessqRecv. We will first take a look at the T-SQL code executed on the initiator side (hosted on a SQL Server 2005 Express Edition instance), responsible for starting the conversation. To demonstrate transactional characteristics of Service Broker, we will incorporate into this process an additional task, which inserts a row representing each message targeting //databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv service into the tbMsgSend table. We will enclose all of these actions (including BEGIN DIALOG, SEND ON CONVERSATION, and INSERT INTO) into a single transaction, whose boundaries are marked with BEGIN TRANSACTION and COMMIT TRANSACTION statements. We will also take advantage of BEGIN TRY\END TRY and BEGIN CATCH\END CATCH blocks, which allow you to redirect the flow of execution whenever an error is detected (for more information regarding their syntax and characteristics, refer to SQL Server 2005 Books Online). In case of such an occurrence, we invoke ROLLBACK TRANSACTION statement, which reverts any actions that have been performed since the beginning of the current transaction :

-- on srvExp01
USE dbSBExp01
GO

BEGIN TRY

   BEGIN TRANSACTION;

      DECLARE @convHandle UNIQUEIDENTIFIER;
      DECLARE @msgTypeName SYSNAME;
      DECLARE @status TINYINT;
      DECLARE @srvName NVARCHAR(512);
      DECLARE @srvConName NVARCHAR(256);
      DECLARE @msgTypeValidation AS NCHAR(2);
      DECLARE @msgBody AS NVARCHAR(400);

      SET @srvName = '//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv'
      SET @srvConName = '//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV'
      SET @msgTypeName = '//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV'
      SET @msgBody = 'Aloha bruddah'

      BEGIN DIALOG CONVERSATION @convHandle
	FROM SERVICE [//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend]
	TO SERVICE @srvName
	ON CONTRACT @srvConName
	WITH ENCRYPTION = ON;
      SEND ON CONVERSATION @convHandle
	MESSAGE TYPE @msgTypeName (@msgBody);
      INSERT INTO 
	tbMsgSend(convHandle, msgTypeName, srvName, srvConName, msgBody)
	VALUES(@convHandle, @msgTypeName, @srvName, @srvConName, @msgbody);

   COMMIT TRANSACTION;

END TRY

BEGIN CATCH

   ROLLBACK TRANSACTION

END CATCH

As long as no issues are encountered (and assuming that you have configured automatically activated stored procedure on the target side, as described in the previous article of this series), you should notice a new row added to the tbMsgSend table representing the newly sent out message, its acknowledgement in the qSend queue (constituting a confirmation that its delivery and processing have been successful), and a single conversation entry in the sys.conversation_endpoints table with DISCONNECTED_INBOUND state (since, in on our example, the target service ends the conversation once a message is extracted from qRecv queue). You can also find out what would be the outcome of a transaction that is interrupted by an obstacle preventing database updates or Service Broker actions from occurring. One way to accomplish this would be to set the UNIQUE constraint on the msgBody column of tbMsgSend table and attempt sending two identical messages, which should lead to the transaction rollback, leaving content of the table as well as the qSend queue and sys.transmission_queue catalog view intact (with no impact on the target).

We could further fine tune tasks responsible for error handling (in order to accommodate different types of issues that might surface when executing our T-SQL code) by extending logic incorporated into the TRY ... CATCH blocks. (Review Using TRY...CATCH in Transact-SQL Books Online article for more information on this subject), but the example presented above satisfies the goal we set for ourselves in the beginning of this article. In essence, it demonstrates that it is possible to enclose a number of statements responsible for processing Service Broker queues and associated database actions into a single transaction in order to facilitate their consistent outcome. In the next article of this series, we will describe how to apply a similar approach to a target and its internally activated stored procedure, including more elaborate error handling functionality.

» See All Articles by Columnist Marcin Policht