Wednesday, February 29, 2012

LINQ: "MSDTC on server is unavailable"

"MSDTC on server 'server name' is unavailable" - sometimes you get this exception while using LINQ to SQL. It means that service called "Distributed Transaction Coordinator" is currently disabled on your server. It is easy to turn it on, but what is wrong with the code, if you get such exception?

Let's try to recreate the exception using small piece of code. Imagine we have database called TestDB with a table called "Products":

And we already added LinqToSql data context classes to our project. Now let's add some logic to manage this database. I will not use DataAccessor / Repository patterns here, this is just a sample - quick and ugly, but it will show you the idea.

We implement two methods for our database. First to update single product description:

public static void UpdateProductDescription(TestDbDataContext dataContext, int id, string description)
       {
           var oldProduct = (from p in dataContext.Products where p.ProductId == id select p).SingleOrDefault();
           if (oldProduct == null)
               return;
           oldProduct.Description = description;
           dataContext.SubmitChanges();
       }

Notice, that this method has data context as an input parameter.

The second method will add new product into database:

public static void AddProduct(Product product)
       {
           using (var dataContext = new TestDbDataContext())
           {
               dataContext.Products.InsertOnSubmit(product);
               dataContext.SubmitChanges();
           }
       }

Notice, that data context here created inside the method.

Now we will write test method in order to reproduce MSDTC exception:

public static void TestMSDTC()
       {
           using(var dataContext = new TestDbDataContext())
           {
               using (var scope = new TransactionScope())
               {
                   UpdateProductDescription(dataContext, 1, "MSDTC");
                   var newProduct = new Product {Name = "Rubber duck", Description = "It can quack"};
                   AddProduct(newProduct);

                   scope.Complete();
               }
           }
       }

The only reason why this example looks the way it does is that i met exactly the same code structure in real life, in a big project developed by a serious team. Sure, it was not about adding rubber ducks into database, but the structure was quite similar.

And the two reasons, why TransactionScope is used here: first - we might have some complicated transactions inside that method which need to be rolled back if something fails, second - TransactionScope manages transactions using Distributed Transaction Coordinator :)

Now If you run this method while "Distributed Transaction Coordinator" service is disabled you will probably get the error while trying to insert new record into Products table. It is all about using those multiple data contexts within transaction scope. So you have either to change your code to make it work with a single database context, or to leave this job to mssql by enabling distributed transaction service.

Using multiple contexts is not always the brilliant idea, but if you still need to enable this feature go to Control Panel -> Administrative Tools -> Services find the "Distributed Transaction Coordinator" service and start it.

2 comments :

  1. Hi

    Thank you for this article, Just a question, I think you are using same database but with 2 different data-context objects, what happens when you deal with 2 different databases? Will same code work with 2 different data-context object, each pointing to a different database?

    ReplyDelete
    Replies
    1. If you have 2 databases and you work with both contexts in one transaction, then yes - this situation managed by transaction coordinator. Do i understand your question correctly?

      Delete