SQL Server 2012 : Distributed Transactions (part 2) - Distributed Transactions in the .NET Framework

3. Distributed Transactions in SQL Server

SQL Server supports distributed transactions using the BEGIN DISTRIBUTED TRANSACTION statement. This statement requests the start of a T-SQL distributed transaction managed by the MS DTC. It uses the following syntax:

BEGIN DISTRIBUTED { TRAN | TRANSACTION }
     [ transaction_name | @tran_name_variable ]

The easiest way to enlist remote instances of the SQL Server Database Engine in a distributed transaction is to execute a distributed query that references a linked server. For example, you can link ServerB and execute a query that looks like this:

DELETE FROM ServerB.TestDB.TestTable Where TestID = 1

Enlisting the preceding query in a distributed transaction is rather simple:

BEGIN DISTRIBUTED TRANSACTION
  DELETE TestDB.TestTable WHERE TestID = 1
  DELETE ServerB.TestDB.TestTable WHERE TestID = 1
COMMIT

The obvious shortcoming of this implementation is that the second query has no way to explicitly enlist. This might seem trivial, but if the second query is a stored procedure that calls ServerC, which in turn calls ServerD, all of them will be tied up in one really expensive transaction, all managed by the one MS DTC on the initiating server.

You can get around this issue by configuring the default behavior to not promote linked server queries to MS DTC. You can do this in two ways. First, you can do it at the server level, by using the following T-SQL command:

sp_configure remote proc trans 0

Or you can do it at the connection level, by using the following syntax:

SET REMOTE_PROC_TRANSACTIONS OFF

You can then use BEGIN TRANSACTION and have the SQL Server Database Engine manage the transactions for you. Conversely, if you use a setting of 1 or ON, a BEGIN TRANSACTION statement involving linked servers will then involve MS DTC—but this is an all-or-nothing approach.

4. Distributed Transactions in the .NET Framework

The concept of distributed transactions was not introduced with the .NET Framework. Prior to the .NET Framework, you could enlist in distributed transactions using third-party transactions coordinators such as COMTI or solutions such as COM+ or Microsoft Transaction Server (MTS) to enlist in distributed transactions. Starting with the .NET Framework 1.0, you could also use the System.EnterpriseServices namespace to enlist within a distributed transaction. System.EnterpriseServices essentially wraps the COM+ infrastructure.

The problem with EnterpriseServices-based solutions was that you had to implement your operation as a class library, decorate it with the TransactionOption attribute, strongly name it, and register it in the global assembly cache (GAC). This made debugging and deployment difficult. Also, the TransactionOption attribute hard-coded the transactional behavior of your operation to one of the following values:

• Disabled Does not participate in transactions. This is the default value.

• NotSupported Runs outside the context of a transaction.

• Supported Participates in a transaction if one exists. If one doesn’t exist, the operation will not request or create one.

• Required Requires a transaction. If no transaction exists, one is created. If one exists, the operation enlists itself in the transaction.

• RequiresNew Requires a transaction and creates a new transaction for itself.

You really couldn’t enlist on demand in the .NET Framework 1.0, and debugging and deployment were difficult. The .NET Framework 1.1 offered a slightly better solution, which was made possible by the ServiceConfig class. This solution, known as “services without components,” did not require you to register your assembly in the GAC or even strongly name it, but unfortunately it was at one time limited to Microsoft Windows 2003 and Windows XP with Service Pack 2, so it didn’t gain a strong following. It has since been rolled out to other Windows-based operating systems but is still relatively unknown. Even so, you couldn’t use concepts such as promotable enlistment. Thus, the .NET Framework 2.0 introduced a new Transaction Management API in a namespace named System.Transactions to address all of these issues.

Let’s look at the behavior of a System.Transactions-based transaction by creating an example. You will set up two databases and execute one query on each.

Execute the script shown in Example 1. The code creates the two databases and names them Test1 and Test2. It then creates a table named FromTable in Test1 and a table named ToTable in Test2, both of them with one int column named Amount. A row is then inserted into the FromTable table with the value 100, and another row is inserted into the ToTable table with the value 0.

Example 1. Preparing test databases for programming distributed transactions.

CREATE DATABASE Test1
GO
USE Test1
GO
CREATE TABLE FromTable (Amount int)
GO
INSERT INTO FromTable(Amount) VALUES (100)
GO

CREATE DATABASE Test2
GO
USE Test2
GO
CREATE TABLE ToTable (Amount int)
GO
INSERT INTO ToTable(Amount) VALUES (0)
GO

Now create a C# console application and name it DistributedTrans. Because you’re using the Transaction Management API, you need to reference the System.Transactions assembly. Right-click the project in Solution Explorer and choose Add Reference. In the Add Reference dialog, click the .NET tab, scroll to find the System.Transactions component, and double-click it. Then add the code shown in Example 2 to Program.cs.

Example 2. Programming distributed transactions in .NET.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

namespace DistributedTrans
{
  class Program
  {
    static void Main(string[] args)
    {
      const string ConnStr1 =
       "Data Source=(local);Initial Catalog=Test1;Integrated Security=SSPI;";
      const string ConnStr2 =
       "Data Source=(local);Initial Catalog=Test2;Integrated Security=SSPI;";

      const string CmdText1 = "UPDATE FromTable SET Amount = Amount - 50";
      const string CmdText2 = "UPDATE ToTable SET Amount = Amount + 50";

      using (var tsc = new TransactionScope())
      {
        using (var conn1 = new SqlConnection(ConnStr1))
        {
          SqlCommand cmd1 = conn1.CreateCommand();
          cmd1.CommandText = CmdText1;
          conn1.Open();
          cmd1.ExecuteNonQuery();
        }

        // Operation #1 is done, going to Operation #2

        using (var conn2 = new SqlConnection(ConnStr2))
        {
          SqlCommand cmd2 = conn2.CreateCommand();
          cmd2.CommandText = CmdText2;
          conn2.Open();
          cmd2.ExecuteNonQuery();
        }

        tsc.Complete();
      }
    }
  }
}

The aim of this example is to execute two queries, one on each database. One query will subtract 50 from FromTable in Test1, and the other will add 50 to ToTable in Test2. Both operations must be bound within the same transaction.

Important

Before you can run this code, you need to make sure that the DTC service is started. To do so, click Start and type services. In the list of services, find Distributed Transaction Coordinator. If not already started, right-click on it now and choose Start.

Run the code (don’t worry if you don’t understand it just yet—we’ll explain it in a moment). Notice that the two command objects cmd1 and cmd2 run in a distributed transaction. If the second query fails, the first one will automatically roll back. It’s really just that simple. Of course, more complicated implementations are possible, but the basic process of implementing a distributed transaction using System.Transaction is really that easy. Now take a closer look at the code you just wrote.

At the beginning of the code snippet is a using block:

using (var tsc = new TransactionScope())
{
  // ...Do transactional operations here...
}

The using block ensures that the Dispose method is always called on the TransactionScope instance tsc when the scope of the using statement ends. The instantiation of a TransactionScope starts a new transaction and Dispose is called when the scope of the enclosing using block ends. By disposing tsc, the distributed transaction is committed. Thus, within the using block, any RM will attempt to enlist itself in the current running transaction and will commit the transaction, assuming the TransactionScope’s Complete method is called prior to exiting the using block’s scope and there is no exception within the block. The process of executing a query on the database is implemented in regular ADO.NET code, as follows:

using (SqlConnection conn1 = new SqlConnection(connStr1))
{
  SqlCommand cmd1 = conn1.CreateCommand();
  cmd1.CommandText = CmdText1;
  conn1.Open();
  cmd1.ExecuteNonQuery();
}

The magic here is that the SqlConnection instance conn1 knows that it is working inside a TransactionScope. When the transaction scope is entered, it creates an ambient transaction. Because transactional code executing within the transaction scope now has an active transaction in which to enlist, it enlists itself with the appropriate TM.

Notice that we said “appropriate TM,” not MS DTC. This is because when a SqlConnection instance is connected with a SQL Server database, it exhibits PSPE—that is, the transaction is managed by LTM and not MS DTC until the conn2.Open statement is called.

Set a breakpoint on the line of code conn2.Open and run the application again. The breakpoint is reached just before the second connection is opened. Now examine the following value in the Immediate Window pane:

Transaction.Current.TransactionInformation.DistributedIdentifier.ToString()

While still in debug mode, execute the conn2.Open statement and check this value again. What do you see? You will see that right before conn2 is opened, the value is a null globally unique identifier (GUID):

"00000000-0000-0000-0000-000000000000"

Step over the conn2.Open statement. Right after the second connection is opened, notice that this value changes to an actual GUID value, similar to the following:

"2c67c7d5-9b32-485f-9e1c-8e43174598aa"

If you immediately go to Administrative Tools | Component Services, and then navigate to the Transaction List, you will notice the very same GUID running there, as shown in Figure 1.

Figure 1. The current active transaction being managed by MS DTC.

What this tells you is that right before conn2.Open was executed, the transaction was being managed by the LTM and hence didn’t have a valid DistributedIdentifier. But as soon as the second RM enlisted in the transaction, the transaction was bumped up to MS DTC and got a valid DistributedIdentifier. If you were to run this same code sample against a SQL Server 2000 database, the DistributedIdentifier would have a valid value right after conn1.Open executes, thus proving that SqlConnection enlists durably when connecting with a SQL Server 2000 database yet exhibits PSPE when connecting with a SQL Server 2012, 2008, or 2005 database.

It could be argued that you could easily achieve the same thing through the T-SQL statement BEGIN DISTRIBUTED TRANSACTION, without using the System.Transactions namespace. It is important to realize that System.Transactions provides a much more flexible architecture in which you can logically choose to enlist or not, and it deals with more than just database transactions. You can theoretically write an RM that encapsulates any operation in a transactional manner, as you’ll do next.