March 21, 2019
Hot Topics:

Solving the Mysteries of SQLCLR and System.Transactions

  • October 17, 2005
  • By Sahil Malik
  • Send Email »
  • More Articles »


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')";
   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.

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 ;

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.

Click here for a larger image.

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.

Download the Code

To download the accompanying source code for the example, click here.

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.

Page 2 of 2

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date