Service Broker Transactional Support in SQL Server 2005 Express Edition

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 TRYEND TRY and BEGIN CATCHEND 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