Monday, December 3, 2007

Avoiding MSDTC with Linq to Sql

Now MSDTC is not evil; it's just overkill when it isn't needed. It slows performance, increases complexity and requires client side configuration... and it can activate when you least expect it.

Take the following code:
void CreateUsers()
{
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
CreateUser("Fred");
CreateUser("Joe");

scope.Complete();
}
}

void CreateUser(string name)
{
using (var context = new UserDataContext())
{
context.Users.InsertOnSubmit(new User { name = name });
context.SubmitChanges();
}
}

This code uses the recommended (and very helpful) TransactionScope class - and unfortunately invokes MSDTC.

Since we're using separate instances of UserDataContext this creates separate database connections - and causes TransactionScope to "promote" the transaction to require MSDTC.

Avoiding this is as simple as sharing the database connection - after all, it is the same database - but passing database connections to sub methods gets ugly really quick.


My Solution

To solve this problem in a cleaner manner, I wrote a Transaction Resource Manager - which works behind the scenes and makes the above code work without change.

It simply shares database connections across the instances (only within a transaction). It's thread safe, and easy to integrate - simply change the DataContext base class in the dbml designer.



Grab the code and check it out. There's an example program and a whole slew of unit tests (around 275 - testing the various permutations of nested TransactionScopes).

Kudos to Nick Guerrea's Weak Dictionary - which I use to weakly track the transactions and share the connections.


Same Solution for other Scenarios

There are other scenarios where this problem arises:

Say you've got a database with many tables (or a plug-in system that shares a database) - a modular approach uses multiple DataContext classes.

Our problem occurs when you need to modify tables from different DataContexts within a single transaction.

Thankfully, the root cause is still the same and our solution solves these cases as well.


This has been a great learning experience for me, and I must say that the Transaction Resource Managers are very interesting (might even make a good alternate ScopeGuard implementation?)

EDIT: See updated article for extra goodness.

12 comments:

--rj said...

Pingback from http://oakleafblog.blogspot.com/2007/12/linq-and-entity-framework-posts-for.html

--rj

Brett said...
This comment has been removed by the author.
Brett said...

This is awesome. I just plugged it in and it worked! Fantastic, thank you so much!

Anonymous said...

Nicely done.
Should be more coders like you.

Brendan Burns said...

Perfect! Just what I was looking for. Thanks for posting your code.

Anonymous said...

I've tested it and it looks good I'm going to use it in a enviroment of more than 10 datacontexts and more than 200 tables, is there any isuee or bug detected?

Luke Marshall said...

Hi,

Make sure you check out the updated post for some extra functionality.

I originally wrote the code for a system with a similar size db and it seemed to work well - but I'd love your feedback.

No real issues/bugs that I'm aware of. I have heard about one person having a problem with stored procs (but I haven't actually reproduced it myself).

Happy coding!
Luke

Anonymous said...

Hello,

this is quite an old post, but it might be able to solve my problem.

Do you know if it would also avoid the promotion to a Distributed Tranasction, if one uses both dataContext and asp.net MembershipProvider in one transaction (on the same DB)?

Unknown said...

Very nice what you made...tks

Max Headroom said...

Hi, Can you upload the code again its not available anymore

Shafaqat Ali said...

Hi

will this work with 2 different databases?

khchew said...

Hi, Can you upload the code again its not available anymore