Introduction
Some time ago, we were developing an application in which multiple levels of transactions were playing in.
Sometimes we had to update just one database, at other points multiple databases had to be updated from a single application domain and there were scenarios where we were updating multiple databases running across application domains which were exposed as WCF service.
As you can see, these transaction management issues become more complex as the number of parties increase and as the number of participating application domains increase.
In this article, we will describe transactions, how MSDTC helps us, how to work on them in SQL Server, in .NET and in WCF. This builds up the ground work for the article which is around how to manage transactions in such heterogeneous environments.
There are many good articles for some of the ground up, hence after giving a quick idea I will point towards better and more detailed articles on the same. Further the audience of this article are architects and designers. I would assume most of us already know these concepts and only a few of us may require a quick refresher.
This article is for application architects and designers and deals with logical design issues, hence no code has been posted with the same.
What are Transactions
A transaction is a group of operations that have the following properties: atomic, consistent, isolated, and durable (ACID).
What this means in terms of databases or data stores is, either all databases are updated or no database is updated.
This article on MSDN provides a good amount of detail on this topic.
What is MSDTC
MSDTC stands for Microsoft distributed transaction coordinator. It is a small utility (small in size but really big in capabilities) which comes as part of the Windows operating system. It allows managing and maintaining transactions across application domains in the same or across servers. It has a rich API set which can be accessed from the .NET Framework API.
In short, it allows us to have transactions across servers.
This excellent video by Michele Leroux Bustamante on MSDN talks in depth on MSDTC and its practical usage.
Transactions in SQL Server
SQL server has a rich Transaction management set in T-SQL using which transactions their level and other things can be managed. This article on CodeProject describes using transactions in SQL Server.
The techniques described here allow us to manage transactions across tables in a given database.
Transactions in .NET
The .NET class library has transaction management capabilities using MSDTC. The article on MSDN describes the mechanism using the TransactionScope class.
The techniques described in this article can be applied to managing transactions across databases which are in a given application domain. I, of course, am not considering the fact that the database is running in a separate application domain.
Transactions in WCF
WCF has transaction management capabilities which allow transaction management to be done across application domains. The video tutorial by Michele Leroux Bustamante describes in details about using transactions in WCF. It also had code snippets on .NET transactions.
Some Best Practices about Transactions
With this ground up, we are ready to discuss the details around best practices as to where transactions should be started and how to manage the same.
Rule Number 1 (and the Only Rule): Start Transaction as Late as Possible
A transaction should be started in the life of an application as late as possible. Consider the following examples.
In case multiple tables have to be updated in a single stored procedure, then transactions should be managed in the stored procedure. However if multiple databases are to be updated, then transactions should be managed in the .NET component.
A transaction should be started as late as possible because it improves reliability, performance and gives better failover support. The later one starts a transaction, the lesser is a chance of a deadlock.
Rule Number 2 (There is Never Only One Rule): Each Participating Component Should Manage its Own Transactions
This rule is under debate. One school of thought says that only one master component should manage all the transactions, however I believe each participating component should manage its own transaction. In other words, let us assume that a .NET component calls multiple stored procedures than each of the stored procedure.
This is recommended so that each participating component is extendable and can be called independently. Nested transactions have better reliability.
History
- 11th October, 2009: Initial post
