Introduction and Background

In earlier development tools, the database transaction management was an isolated topic that required a separate thought process. Considering the example of heterogeneous production and development environment where the application is interacting with multiple databases, transaction management was a tedious task.

Several legacy systems are on the verge of migration; however, these legacy systems have a serious constraint from the back-end point of view. Due to the business risks involved, database migration is usually considered a separate project. Also, real time applications cannot take the risk of downtime in production environment due to business constraints.

Under such circumstances, application enhancement or new supplementary automations are required to communicate with legacy databases and latest databases as well. This interaction, often, is observed to be a transaction based interaction.

The new release of Microsoft .NET 2.0 resolves this issue with a new feature called the "Transaction Scope". In this article, we will discuss in brief the overview of this feature.

This article requires prior knowledge of database transaction implementation.

Agenda

1. Transaction Options
2. TransactionScope Options
3. Transaction Scope
4. Transaction Promotion
5. Conclusion

Let's start here....

Transaction Options

What is Transaction Option?

• Encapsulates the timeout and isolation level parameters for a transaction into a single, simple structure.
• Passed to the TransactionScope constructors to create a new transaction with the desired behaviors.

Usage Notes

• Specify Isolation Level using enums:
• Chaos
• ReadCommitted
• ReadUncommitted
• RepeatableRead
• Serializable
• Unspecified
• Specify Timeout -Timeout period for the transaction.
• Parameter to TransactionScope - Once the Options object is ready, pass it on to the TransactionScope.

Implementation

TransactionOptions TransOpt = New TransactionOptions();
TransOpt.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; 
TransOpt.Timeout = New TimeSpan(0, 2, 0);

TransactionScope Options

What is TransactionScope Option?

• Defines the behavior and scope of a transaction scope.
• Defines the behavior of an ambient transaction.
• Start, re-use, or ignore current transaction context.

Usage Notes

• Required - Transaction must present and re-use if already exists.
• RequiresNew - Transaction must present and always create a new transaction.
• Suppress- Transaction should not be used, even if it exists.

Implementation

using(TransactionScope scope = 
  new TransactionScope(TransactionScopeOption.Required, TransOptions))
using(TransactionScope scope = 
  new TransactionScope(TransactionScopeOption.RequiresNew, TransOptions))
using(TransactionScope scope = 
  new TransactionScope(TransactionScopeOption.Suppress, TransOptions))

TransactionScope

What is TransactionScope ?

• A transaction scope defines a block of code that participates in a transaction.
• If the code block completes successfully, the transaction manager commits the transaction. Otherwise, the transaction manager rolls back the transaction.
• If multiple data sources are accessed in the same context, then enlisted transactions are promoted to DTC.

Usage Notes

• Define scope
• Connect a data source
• Connect another data source
• Manipulate data
• On success, set complete flag to true
• Close the scope

Implementation

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
    string strCmd = "SQL to Execute";
    conn = new SqlClient.SqlConnection("Connection to DB1");
    conn.Open()
    objCmd = new SqlClient.SqlCommand(strCmd, conn);
    objCmd.ExecuteNonQuery();
    string strCmd2 = "SQL to Execute";
    conn2 = new SqlClient.SqlConnection("Connection to DB2");
    conn2.Open()
    objCmd2 = new SqlClient.SqlCommand(strCmd2, conn2);
    objCmd2.ExecuteNonQuery();
}

Transaction Promotion

Lightweight Transaction

• A Lightweight Transaction remains with a single data source.
• Lightweight Transactions enable you to incorporate several distinct operations, which occur on single systems, into an atomic action that either succeeds or fails completely.
• Lightweight Transactions are managed by LTM, i.e., Lightweight Transaction Manager.

Distributed Transaction

• A Distributed Transaction spans multiple data sources.
• Distributed Transactions enable you to incorporate several distinct operations, which occur on different systems, into an atomic action that either succeeds or fails completely.
• Distributed Transactions are managed by DTC, i.e., Distributed Transaction Coordinator.

DTC Promotion

• In a defined TransactionScope, if multiple connections are placed in different data sources, the Enlisted Lightweight Transactions are handed over to DTC from LTM.
• This has no impact on the application and requires no special configuration.
• This can be monitored using SQL Server Profiler 2005.

Conclusion

• Transaction Management in a "Multiple Data Source" scenario is no more an issue.
• Completely ACID Transactions with multiple data sources are possible without any additional overhead to applications.
• Extremely powerful feature in .NET 2.0.

Further Readings

• http://msdn.microsoft.com/msdnmag/issues/06/11/DataPoints/default.aspx
• http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx
• http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscopeoption.aspx