Solving the Mysteries of SQLCLR and System.Transactions

My previous three articles covered the nitty gritty of CLR integration in SQL Server 2005 (SQLCLR). In addition to discussing the correct usage, they also explored various SQLCLR features (such as context connections) and their uses and restrictions.

One topic in the previous article was the ability to work with an underlying database. It presented an example application that calculated prime numbers and used a database table to "remember" what it was doing. Obviously, when you are writing to an underlying table via a CLR database object, a number of questions come up:

  1. Can you enlist SQLCLR objects in the current running transaction?
  2. Can you choose to not enlist in a transaction?
  3. Can a SQLCLR object cause the entire transaction (including client-side code, T-SQL, and the SQLCLR object itself) to roll back?
  4. What if the transaction is started using a SqlTransaction object? Does that make a difference?
  5. What if the transaction has been started using a "BEGIN TRANSACTION" T-SQL statement?

This article demystifies these and many other such questions. First, it runs through the setup of the data source and various other important objects for an example application.

Setting Up the Data Source

The data source is simply a database called Test that includes a table called Persons. You can easily set it up using the following script:

Create database test
go
use test
go

Create Table Persons
(
   PersonID Int Identity Primary Key,
   PersonName Varchar(50)
)
GO

Insert Into Persons (PersonName) Values ('Jimmy The Clown')
GO

Once you've set up the above data source, the next step is to get yourself a CLR object.

Creating the CLR Object: A Stored Procedure

Create a CLR stored procedure called RSSP.InsertPerson (Really Simple Stored Procedure). The following is the code for the stored procedure (you also will find it in the code download):

[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertPerson(string PersonName)
{
   using (SqlConnection contextConn =
      new SqlConnection("context connection=true"))
   {
      SqlCommand insertPersonCmd = contextConn.CreateCommand();
      insertPersonCmd.CommandText =
         "Insert Into Persons (PersonName) Values (@PersonName)";
      insertPersonCmd.Parameters.AddWithValue(
         "@PersonName", PersonName);
      contextConn.Open();
      insertPersonCmd.ExecuteNonQuery();
      contextConn.Close();
   }
}

As you can see, the stored procedure simply uses the context connection to insert a new row into the Persons table. If you need further CLR object details, refer to the previous article.

Using the CLR Stored Procedure in Transactions

Once you've deployed the stored procedure in SQL Server 2005, you can execute it simply by using the following T-SQL Syntax:

Exec InsertPerson 'Superman'

The line above simply inserts a new row into the Persons table. But what if you wanted to execute code that looked like this:

BEGIN TRANSACTION
Exec InsertPerson 'Batman'
ROLLBACK

It would not insert "Batman" into the Persons table. You'd notice that the SQLCLR stored procedure has the ability to enlist itself automatically in the current running transaction, and obey the ultimate issued rollback on the current running transaction. This, in fact, is made possible by the integration of System.Transactions—all the way into SQLCLR. After all, it is the same framework inside or outside SQL Server.

Now, modify the stored procedure code to as follows, and redeploy to SQL Server:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertPerson(string PersonName)
{
   using (SqlConnection contextConn =
      new SqlConnection("context connection=true"))
   {
      SqlCommand insertPersonCmd = contextConn.CreateCommand();
      insertPersonCmd.CommandText =
         "Insert Into Persons (PersonName) Values (@PersonName)";
      insertPersonCmd.Parameters.AddWithValue(
         "@PersonName", PersonName);
      contextConn.Open();
      insertPersonCmd.ExecuteNonQuery();
      contextConn.Close();
      // The line below will need a reference to System.Transactions
      System.Transactions.Transaction.Current.Rollback();
   }
}

Note the newly added line of code:

System.Transactions.Transaction.Current.Rollback();

If you were to execute a T-SQL code block as follows:

BEGIN TRANSACTION
Insert Into Persons(PersonName) Values ('Spiderman')
Exec InsertPerson 'Mr. Incredible'
COMMIT

You should get the following output:

(1 row(s) affected)
Msg 266, Level 16, State 2, Procedure InsertPerson, Line 0
Transaction count after EXECUTE indicates that a COMMIT or
ROLLBACK TRANSACTION statement is missing.
Previous count = 1, current count = 0.
Msg 3902, Level 16, State 1, Line 4
The COMMIT TRANSACTION request has no corresponding
BEGIN TRANSACTION.

Basically, SQL Server is informing you that the current running transaction has been rolled back by one of the participants (SQLCLR) within the transaction, so your commit cannot succeed.

You may be shocked at how simple this code looks. You simply latched on to the current running transaction using System.Transactions.Transaction.Current, and issued a rollback. However, you need to be wary of a few things when using System.Transactions—you should understand what is going on under the sheets to truly make good use of it. So, the following section offers a quick primer on what System.Transactions is, and what it brings to the table.

Solving the Mysteries of SQLCLR and System.Transactions

System.Transactions

I have never met a programmer who intentionally wrote unreliable code. When you write a program, you always want it to behave just as you intend it to, and that traditionally means using transaction support. For years, databases have included great support for transactions; it is the non-database entities such as file systems or e-mail that have lacked good transaction support.

System.Transactions is a new namespace in .NET 2.0 that helps you solve this issue. Its sole purpose is to create resource managers (RMs) that enlist in transactions. Even though you could do that before using technologies such as System.EnterpriseServices, MTS, COM+, and so forth, each had its own downside.

In any discussion about System.Transactions, two words will come up fairly often, RMs and DTCs:

  • RM: An RM is an actual functional, transaction-aware unit that does some useful work. An RM has the ability to enlist within a transaction and interact with a DTC.
  • DTC: A distributed transaction coordinator (DTC) is the policeman, or the unit, that orchestrates the communication between various RMs, as well as the integrity of the entire operation. An example of a DTC on the Microsoft platform is Microsoft Distributed Transaction Coordinator (MSDTC).

Generally, you implement a distributed transaction in what is commonly referred to as a two-phase commit. The following is the typical flow of a distributed transaction:

  1. An application requests a transaction from the MSDTC. This application also is commonly referred to as the initiator.
  2. The application then asks the resource manager to do its work as a part of the same transaction (this is actually configurable), and the resource managers register with the transaction manager as a part of the same transaction. This is commonly referred to as enlisting in a transaction.
  3. If all goes well, the application commits the transaction.
  4. If something fails, either step can issue a rollback.
  5. In either case, the transaction coordinator coordinates with all the RMs to ensure that they all either succeed and do the requested work, or they all roll back their work together.

For the workflow to succeed, a lot of chatty communication must occur between MSDTC and the RMs. MSDTC generally is quite expensive for that reason. Other disadvantages it suffers from include the requirement of a higher isolation level and the possibility of such communication getting blocked by firewalls. For these reasons, Microsoft introduced a new transaction coordinator called Lightweight Transaction Manager (LTM). The idea is LTM can manage any transaction that doesn't need the heavyweight features of MSDTC.

The decision to use LTM or MSDTC is not made by the consumer of the RM, but rather the author of the RM. In other words, if you use SqlConnection, you don't really need to worry about who is managing your transaction; you simply leave that decision to SqlConnection. You will see an example demonstrating this shortly.

When you write a RM, you do need to think about how exactly you wish to enlist in a current running transaction. You can choose to one of three options:

  1. Volatile enlistment: A RM that deals with volatile resources, such as an in memory cache, is a good candidate for volatile enlistment. This means that the RM can perform the first phase of the two-phase commit process, but if it is unable to perform the second, it does not need to explicitly recover the first. In other words, it does not need to provide a recovery contract.
  2. Durable enlistment: A durable enlistment, on the other hand, has permanent (durable) data that depends on the transaction for consistency. A good example of durable enlistment is a transactional file system. Say you are copying a file from one location to another. If you overwrite an existing file, you would need to restore the existing state in the event of an abort (in other words, restore the previous file). Thus, in the case of a failure, a durable transaction will log transactions that it is participating in and recover those transactions with the help of the transaction manager to get the final rolled-back outcome. When it is done recovering the transaction, it responds to the transaction manager to let it know that it has completed recovery, so the transaction manager can report a successful recovery/rollback.
  3. Promotable enlistment: Promotable enlistment takes the best of both worlds. A transaction is first managed by LTM and then escalated or promoted to MSDTC as the situation changes. A transaction will be escalated to MSDTC when any of the following occurs:
    1. At least one durable resource that doesn't support single-phase notifications is enlisted in the transaction.
    2. At least two durable resources that support single-phase notifications are enlisted in the transaction.
    3. A request to marshal the transaction to a different appdomain or different process is done.

In .NET 2.0, a SqlConnection object connecting to a SQL Server 2005 database is an example of a RM that enlists using Promotable enlistment. For instance, look at the following code:

using (TransactionScope ts = new TransactionScope())
{
   using (SqlConnection cn = new SqlConnection(connectionString1))
   {
      SqlCommand cmd = cn.CreateCommand();
      cmd.CommandText =
         "Insert into Persons(PersonName) Values ('SuperMan')";
      cn.Open();
      cmd.ExecuteNonQuery();
      cn.Close();
   }

   using (SqlConnection cn = new SqlConnection(connectionString2))
   {
      SqlCommand cmd = cn.CreateCommand();
      cmd.CommandText =
         "Insert into Persons(PersonName) Values ('BatMan')";
      cn.Open();    // This is where the transaction promotes.
      cmd.ExecuteNonQuery();
      cn.Close();
   }

   ts.Complete();
}

The transaction is managed by LTM until the second cn.Open statement executes. Right after that statement is executed, you should see to it that the transaction gets a distributed transaction identifier using the following code:

Transaction tr = System.Transactions.Transaction.Current ;
Console.WriteLine(tr.DistributedIdentifier.ToString());

And it should hold a GUID that matches the identifier shown under the Transaction List under Component Services in the control panel as shown below.

[Sahil.jpg]

Any Questions?

This article touched upon a number of concepts. Let me summarize by answering the questions that were put forth at the beginning of this article.

Question: Can you enlist SQLCLR objects in the current running transaction?
Answer: Yes, and you get that for free—no code required.

Question: Can you choose to not enlist in a transaction?
Answer: By default, you will be enlisted in the current running transaction. Also by default, any operation spawning from within SQLCLR, like a new database connection to say an Oracle database, will also tie within the same transaction. If you want to break away from the current running transaction, you need to add "enlist=false" in the connection string for the new SqlConnection.

Question: Can a SQLCLR object cause the entire transaction, involving client-side code, T-SQL, and the SQLCLR object itself, to roll back?
Answer: Yes.

Question: What if the transaction is started using a SqlTransaction object? Does that make a difference?
Answer: No, it doesn't. It is perfectly all right to use SqlTransaction. However, a complication occurs in the case of nested transactions, or ambiguous nested transaction scopes between transactions started from the API, T-SQL, or SQLCLR. If you use SqlTransaction, you have to prevent such ambiguity.

Question: What if the transaction has been started using a "BEGIN TRANSACTION" T-SQL statement?
Answer: It doesn't make any difference. It still is only a transaction.

About the Author

Sahil Malik has worked for a number of top-notch clients in Microsoft technologies ranging from DOS to .NET. He is the author of Pro ADO.NET 2.0 and co-author of Pro ADO.NET with VB.NET 1.1. Sahil is currently also working on a multimedia series on ADO.NET 2.0 for Keystone Learning. For his community involvement, contributions, and speaking, he has also been awarded the Microsoft MVP award.



Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • A modern mobile IT strategy is no longer an option, it is an absolute business necessity. Today's most productive employees are not tied to a desk, an office, or a location. They are mobile. And your company's IT strategy has to be ready to support them with easy, reliable, 24/7 access to the business information they need, from anywhere in the world, across a broad range of communication devices. Here's how some of the nation's most progressive corporations are meeting the many needs of their mobile workers …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds