Implementing TransactionScope Using .Net 2.0

Introduction and Background

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

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

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

New release of Microsoft .net 2.0 resolves this issue with a new feature called as 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

Then lets 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
  • Unspecifie
• 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?

• Define behavior and scope of transaction scope.
• Defines behavior of 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.
• Suppres- Transaction should not be used, even if 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 same context, then ENLISTED transactions are promoted to DTC.

Usage Notes

• Define Scope
• Connect one 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 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 connection are placed to different data sources, the Enlisted Lightweight Transactions are handed over to DTC from LTM.
• This has no impact on application and requires no special configuration.
• This can be monitored using SQL Server Profiler 2005.

Conclusion

• Transaction Management in "Multiple Data Source" scenario is no more issue.
• Completely ACID Transaction with multiple data sources without any additional overhead to application.
• 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