Introduction:
A transaction is a unit of work. You use transactions to ensure the consistency and integrity of a database. If a transaction is successful, all of the data modifications performed during the transaction are committed and made permanent. If an error occurs during a transaction, you can roll back the transaction to undo the data modifications that occurred during the transaction.
This article describes how to manage distributed transactions that can span multiple data sources in a Microsoft .NET application.
Distributed Transactions:
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.
Properties of a transaction:
- Atomicity:
A transaction is an atomic unit of work, an indivisible set of operations. The operations that you perform within a transaction usually share a common purpose, and are interdependent. Either all of the operations in a transaction should complete, or none of them should. Atomicity helps to avoid data inconsistencies by eliminating the chance of processing a subset of operations.
- Consistency:
A transaction preserves the consistency of data. A transaction transforms one consistent state of data into another consistent state of data. Some of the responsibility for maintaining consistency falls to the application developer, who must ensure that the application enforces all known integrity constraints.
- Isolation:
A transaction is a unit of isolation. Isolation requires that each transaction appear to be the only transaction manipulating the data store, even though other transactions might be running concurrently. Transactions attain the highest level of isolation when they have the ability to be serialized; at this level of isolation, the results obtained from a set of concurrent transactions are identical to the results obtained by running each transaction serially. A high degree of isolation can limit the number of concurrent transactions, and consequently, applications often reduce the isolation level in exchange for better throughput.
- Durability:
A transaction is the unit of recovery for a set of operations. If a transaction succeeds, the system guarantees that its updates will persist, even if the computer crashes immediately after the application performs a commit operation. Specialized logging allows the system restart procedure to complete unfinished operations so that the transaction is durable.
Creating Distributed Transactions:
The .NET Framework 2.0 includes the System.Transactions namespace, which provides extensive support for distributed transactions across a range of transaction managers, including data sources and message queues. The System.Transactions namespace defines the TransactionScope class, which enables you to create and manage distributed transactions.
To create and use distributed transactions, create a TransactionScope object, and specify whether you want to create a new transaction context or enlist in an existing transaction context. You can also exclude operations from a transaction context if appropriate.
You can open multiple database connections within the same transaction scope. The transaction scope decides whether to create a local transaction or a distributed transaction. The transaction scope, automatically promotes a local transaction to a distributed transaction if necessary, based on the following rules:
- When you create a TransactionScope object, it initially creates a local, lightweight transaction. Lightweight transactions are more efficient than distributed transactions because they do not incur
the overhead of the Microsoft Distributed Transaction Coordinator (DTC).
- If the first connection that you open in a transaction scope is to a SQL Server 2005 database, the connection enlists in the local transaction. The resource manager for SQL Server 2005 works with
the System.Transactions namespace and supports automatic promotion of local transactions to distributed transactions. Therefore, the transaction scope is able to defer creating a distributed
transaction unless and until it becomes necessary later.
- If the first connection that you open in a transaction scope is to anything other than a SQL Server 2005 database, the transaction scope promotes the local transaction to a distributed transaction
immediately. This immediate promotion occurs because the resource managers for these other databases do not support automatic promotion of local transactions to distributed transactions.
- When you open subsequent connections in the transaction scope, the transaction scope promotes the transaction to a distributed transaction, regardless of the type of the database.
Steps of creating distributed transaction:
- Instantiate a TransactionScope object.
- Open a connection with the database.
- Perform your database operations (insert, update & delete).
- If your operations completed successfully, mark your transaction as complete.
- Dispose The TransactionScope object.
If all the update operations succeed in a transaction scope, call the Complete method on the TransactionScope object to indicate that the transaction completed successfully. To terminate a transaction, call the Dispose method on the TransactionScope object. When you dispose a TransactionScope, the transaction is either committed or rolled back, depending on whether you called the Complete method:
- If you called the Complete method on the TransactionScope object before its disposal, the transaction manager commits the transaction.
- If you did not call the Complete method on the TransactionScope object before its disposal, the transaction manager rolls back the transaction.
TransactionScope How To:
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. Bellow is a guide lines on how you can create and use a TransactionScope instance:
- Add a reference to the System.Transactions assembly.
- Import the System.Transactions namespace into your application.
- If you want to specify the nesting behavior for the transaction, declare a TransactionScopeOption variable, and assign it a suitable value.
- If you want to specify the isolation level or timeout for the transaction, create a TransactionOptions instance, and set its IsolationLevel and TimeOut properties.
- Create a new TransactionScope object in a using statement (a Using statement in Microsoft Visual Basic).
Pass a TransactionScopeOption variable and a TransactionOptions object into the constructor, if appropriate.
- In the using block (Using block in Visual Basic), open connections to each database that you need to update, and perform update operations as required by your application. If all updates succeed, call the Complete method on the TransactionScope object
- Close the using block (Using block in Visual Basic) to dispose the TransactionScope object. If the transaction completed successfully, the transaction manager commits the transaction. Otherwise, the transaction manager rolls back the transaction.
Listing 1: TransactionScope C#:
using System.Transactions;
...
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = new TimeSpan(0, 2, 0);
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
using (SqlConnection connA = new SqlConnection(connStringA))
{
using (SqlCommand cmdA = new SqlCommand(sqlStmtA, connA))
{
int rowsAffectedA = cmdA.ExecuteNonQuery();
if (rowsAffectedA > 0)
{
using (SqlConnection connB = new SqlConnection(connStringB))
{
using (SqlCommand cmdB = new SqlCommand(sqlStmtB, connB))
{
int rowsAffectedB = cmdB.ExecuteNonQuery();
if (rowsAffectedB > 0)
{
transactionScope.Complete();
}
} // Dispose the second command object.
} // Dispose (close) the second connection.
}
} // Dispose the first command object.
} // Dispose (close) the first connection.
} // Dispose TransactionScope object, to commit or rollback transaction.
Listing 2: TransactionScope VB.NET:
Imports System.Transactions
...
Dim options As New TransactionOptions()
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
options.Timeout = New TimeSpan(0, 2, 0)
Using scope As New TransactionScope(TransactionScopeOption.Required, options)
Using connA As New SqlConnection(connStringA)
Using cmdA As New SqlCommand(sqlStmtA, connA)
Dim rowsAffectedA As Integer = cmdA.ExecuteNonQuery()
If (rowsAffectedA > 0) Then
Using connB As New SqlConnection(connStringB)
Using cmdB As New SqlCommand(sqlStmtA, connB)
Dim rowsAffectedB As Integer = cmdB.ExecuteNonQuery()
If (rowsAffectedB > 0) Then
transactionScope.Complete()
End If
End Using ' Dispose the second command object.
End Using ' Dispose (close) the second connection.
End If
End Using ' Dispose the first command object.
End Using ' Dispose (close) the first connection.
End Using ' Dispose TransactionScope object, to commit or rollback transaction.
Conclusion:
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.
Also you should know that you can TransactionScope with any Data Provider such as Oracle or OleDB or ODBC.