System.Transactions Integration with SQL Server (ADO.NET)
The .NET Framework version 2.0 introduced a transaction framework that can be accessed through the System.Transactions namespace. This framework exposes transactions in a way that is fully integrated in the .NET Framework, including ADO.NET.
In addition to the programmability enhancements, System.Transactions and ADO.NET can work together to coordinate optimizations when you work with transactions. A promotable transaction is a lightweight (local) transaction that can be automatically promoted to a fully distributed transaction on an as-needed basis.
Starting with ADO.NET 2.0, System.Data.SqlClient supports promotable transactions when you work with SQL Server 2005. A promotable transaction does not invoke the added overhead of a distributed transaction unless the added overhead is required. Promotable transactions are automatic and require no intervention from the developer.
Promotable transactions are only available when you use the .NET Framework Data Provider for SQL Server (SqlClient) with SQL Server 2005.
Creating Promotable Transactions
The .NET Framework Provider for SQL Server provides support for promotable transactions, which are handled through the classes in the .NET Framework System.Transactions namespace. Promotable transactions optimize distributed transactions by deferring creating a distributed transaction until it is needed. If only one resource manager is required, no distributed transaction occurs.
Note
In a partially trusted scenario, the DistributedTransactionPermission is required when a transaction is promoted to a distributed transaction. For more information, see Transaction Management Escalation.
Promotable Transaction Scenarios
Distributed transactions typically consume significant system resources, being managed by Microsoft Distributed Transaction Coordinator (MS DTC), which integrates all the resource managers accessed in the transaction. A promotable transaction is a special form of a System.Transactions transaction that effectively delegates the work to a simple SQL Server 2005 transaction. System.Transactions, System.Data.SqlClient, and SQL Server 2005 coordinate the work involved in handling the transaction, promoting it to a full distributed transaction as needed.
The benefit of using promotable transactions is that when a connection is opened by using an active TransactionScope transaction, and no other connections are opened, the transaction commits as a lightweight transaction, instead of incurring the additional overhead of a full distributed transaction.
Promotable Transactions in SQL Server 2008
In version 2.0 of the .NET Framework and SQL Server 2005, opening a second connection inside a TransactionScope would automatically promote the transaction to a full distributed transaction, even if both connections were using identical connection strings. In this case, a distributed transaction adds unnecessary overhead that decreases performance.
Starting with SQL Server 2008 and version 3.5 of the .NET Framework, local transactions are no longer promoted to distributed transactions if another connection is opened in the transaction after the previous transaction is closed. This requires no changes to your code if you are already using connection pooling and enlisting in transactions. The following sections describe the situation in more detail.
Understanding Lightweight Transactions
A lightweight transaction is an agreement between SqlClient and the transaction, where SqlClient manages transacting the work on the transaction’s behalf. SqlClient starts a local server transaction when enlisting into the transaction, and the transaction sends a request to roll back or commit the work at the end.
When a second resource tries to enlist in the transaction using the same connection, the local transaction manager does not detect that there is already a connection enlisted to the same data source. In this situation, the local transaction promotes to a full distributed transaction.
Even though both connections are connecting to the same data source, connection pooling will not enable you to open another connection and enlist in the same transaction.
Understanding Connection Pooling and Transactions
A SqlConnection consists of two parts: the public instance that your code interacts with (the outer connection) and a hidden connection that represents an actual server connection (the inner connection).
When you call the Open method on the outer connection, the connection manager looks for a free inner connection from the pool that is associated with the transaction. It will create a new inner connection if one does not exist. When the Close method is called on the outer connection, it returns the inner connection to the pool, where the connection State is reset to ensure a clean slate when it is reused from the pool. SQL Server does not distinguish between the outer and inner connections, so only one outer connection can be used with one inner connection at a time.
When a pooled connection is reset, cursors are closed, options are set back to defaults, the database context is switched back to the one specified in the connection string, and all outstanding local transactions are rolled back. This behavior provides a known starting state every time that you open a connection from the pool. It also prevents you from reusing the inner connection until the local transaction finishes. When you close the transaction's outer connection, the inner connection is set aside pending a commit or rollback request from the transaction manager. When you open a new outer connection while the inner connection is still active, the pool will be empty, and a new inner connection is created. The local transaction manager will then detect that two different resources are trying to enlist in the same transaction. Because SQL Server connections cannot share in local transactions, the transaction manager must promote the transaction to a full distributed transaction.
Extensions Added for SQL Server 2008
SQL Server 2008 adds a new connection reset mode that does not roll back local transactions. This enables SqlClient to return the inner connection to the pool to be reused, even when the connection is associated with a lightweight transaction. A second connection to the same data source can be associated with the existing local transaction without the transaction manager detecting a second transaction.
However, if you try to open a second outer transaction before closing the first connection, there will not be a free connection in the pool. A second inner connection will then be enlisted, and the transaction will be promoted. If connection pooling is turned off, or if a second connection uses a slightly different connection string, the inner connection will be unavailable even if it is sitting idle in the pool. For more information about SQL Server connection pooling, see SQL Server Connection Pooling (ADO.NET).
No changes to your code are necessary in order to take advantage of this new functionality in SQL Server 2008. However, if you are enlisting in SQL Server 2005 transactions, pooling behavior reverts back to version 2.0 of the .NET Framework.
The Enlist and Transaction Binding Connection String Keywords
The ConnectionString property supports a keyword, Enlist, which indicates whether System.Data.SqlClient will detect transactional contexts and automatically enlist the connection in a distributed transaction. If Enlist=true, the connection is automatically enlisted in the opening thread's current transaction context. If Enlist=false, the SqlClient connection does not interact with a distributed transaction. The default value for Enlist is true. If Enlist is not specified in the connection string, the connection is automatically enlisted in a distributed transaction if one is detected when the connection is opened.
The Transaction Binding keywords in a SqlConnection connection string control the connection's association with an enlisted System.Transactions transaction. It is also available through the TransactionBinding property of a SqlConnectionStringBuilder.
The following table describes the possible values.
Keyword |
Description |
|---|---|
Implicit Unbind |
The default. The connection detaches from the transaction when it ends, switching back to autocommit mode. |
Explicit Unbind |
The connection remains attached to the transaction until the connection is closed or EnlistTransaction(null) is called. The connection will fail if the associated transaction is not active or does not match Current. |
For more information, see Implementing An Implicit Transaction Using Transaction Scope.
Using TransactionScope
The TransactionScope class makes a code block transactional by implicitly enlisting connections in a distributed transaction. You must call the Complete method at the end of the TransactionScope block before leaving it. Leaving the block invokes the Dispose method. If an exception has been thrown that causes the code to leave scope, the transaction is considered aborted.
We recommend that you use a using block to make sure that Dispose is called on the TransactionScope object when the using block is exited. Failure to commit or roll back pending transactions can significantly damage performance because the default time-out for the TransactionScope is one minute. If you do not use a using statement, you must perform all work in a Try block and explicitly call the Dispose method in the Finally block.
If an exception occurs in the TransactionScope, the transaction is marked as inconsistent and is abandoned. It will be rolled back when the TransactionScope is disposed. If no exception occurs, participating transactions commit.
Note
The TransactionScope class creates a transaction with a IsolationLevel of Serializable by default. Depending on your application, you might want to consider lowering the isolation level to avoid high contention in your application.
Note
We recommend that you perform only updates, inserts, and deletes within distributed transactions because they consume significant database resources. Select statements may lock database resources unnecessarily, and in some scenarios, you may have to use transactions for selects. Any non-database work should be done outside the scope of the transaction, unless it involves other transacted resource managers. Although an exception in the scope of the transaction prevents the transaction from committing, the TransactionScope class has no provision for rolling back any changes your code has made outside the scope of the transaction itself. If you have to take some action when the transaction is rolled back, you must write your own implementation of the IEnlistmentNotification interface and explicitly enlist in the transaction.
Example
Working with System.Transactions requires that you have a reference to System.Transactions.dll.
The following function demonstrates how to create a promotable transaction against two different SQL Server instances, represented by two different SqlConnection objects, which are wrapped in a TransactionScope block. The code creates the TransactionScope block with a using statement and opens the first connection, which automatically enlists it in the TransactionScope. The transaction is initially enlisted as a lightweight transaction, not a full distributed transaction. The second connection is enlisted in the TransactionScope only if the command in the first connection does not throw an exception. When the second connection is opened, the transaction is automatically promoted to a full distributed transaction. The Complete method is invoked, which commits the transaction only if no exceptions have been thrown. If an exception has been thrown at any point in the TransactionScope block, Complete will not be called, and the distributed transaction will roll back when the TransactionScope is disposed at the end of its using block.
' This function takes arguments for the 2 connection strings and commands in order
' to create a transaction involving two SQL Servers. It returns a value > 0 if the
' transaction committed, 0 if the transaction rolled back. To test this code, you can
' connect to two different databases on the same server by altering the connection string,
' or to another RDBMS such as Oracle by altering the code in the connection2 code block.
Public Function CreateTransactionScope( _
ByVal connectString1 As String, ByVal connectString2 As String, _
ByVal commandText1 As String, ByVal commandText2 As String) As Integer
' Initialize the return value to zero and create a StringWriter to display results.
Dim returnValue As Integer = 0
Dim writer As System.IO.StringWriter = New System.IO.StringWriter
' Create the TransactionScope in which to execute the commands, guaranteeing
' that both commands will commit or roll back as a single unit of work.
Using scope As New TransactionScope()
Using connection1 As New SqlConnection(connectString1)
Try
' Opening the connection automatically enlists it in the
' TransactionScope as a lightweight transaction.
connection1.Open()
' Create the SqlCommand object and execute the first command.
Dim command1 As SqlCommand = New SqlCommand(commandText1, connection1)
returnValue = command1.ExecuteNonQuery()
writer.WriteLine("Rows to be affected by command1: {0}", returnValue)
' If you get here, this means that command1 succeeded. By nesting
' the Using block for connection2 inside that of connection1, you
' conserve server and network resources by opening connection2
' only when there is a chance that the transaction can commit.
Using connection2 As New SqlConnection(connectString2)
Try
' The transaction is promoted to a full distributed
' transaction when connection2 is opened.
connection2.Open()
' Execute the second command in the second database.
returnValue = 0
Dim command2 As SqlCommand = New SqlCommand(commandText2, connection2)
returnValue = command2.ExecuteNonQuery()
writer.WriteLine("Rows to be affected by command2: {0}", returnValue)
Catch ex As Exception
' Display information that command2 failed.
writer.WriteLine("returnValue for command2: {0}", returnValue)
writer.WriteLine("Exception Message2: {0}", ex.Message)
End Try
End Using
Catch ex As Exception
' Display information that command1 failed.
writer.WriteLine("returnValue for command1: {0}", returnValue)
writer.WriteLine("Exception Message1: {0}", ex.Message)
End Try
End Using
' If an exception has been thrown, Complete will
' not be called and the transaction is rolled back.
scope.Complete()
End Using
' The returnValue is greater than 0 if the transaction committed.
If returnValue > 0 Then
writer.WriteLine("Transaction was committed.")
Else
' You could write additional business logic here, notify the caller by
' throwing a TransactionAbortedException, or log the failure.
writer.WriteLine("Transaction rolled back.")
End If
' Display messages.
Console.WriteLine(writer.ToString())
Return returnValue
End Function
// This function takes arguments for the 2 connection strings and commands in order
// to create a transaction involving two SQL Servers. It returns a value > 0 if the
// transaction committed, 0 if the transaction rolled back. To test this code, you can
// connect to two different databases on the same server by altering the connection string,
// or to another RDBMS such as Oracle by altering the code in the connection2 code block.
static public int CreateTransactionScope(
string connectString1, string connectString2,
string commandText1, string commandText2)
{
// Initialize the return value to zero and create a StringWriter to display results.
int returnValue = 0;
System.IO.StringWriter writer = new System.IO.StringWriter();
// Create the TransactionScope in which to execute the commands, guaranteeing
// that both commands will commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection1 = new SqlConnection(connectString1))
{
try
{
// Opening the connection automatically enlists it in the
// TransactionScope as a lightweight transaction.
connection1.Open();
// Create the SqlCommand object and execute the first command.
SqlCommand command1 = new SqlCommand(commandText1, connection1);
returnValue = command1.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
// if you get here, this means that command1 succeeded. By nesting
// the using block for connection2 inside that of connection1, you
// conserve server and network resources by opening connection2
// only when there is a chance that the transaction can commit.
using (SqlConnection connection2 = new SqlConnection(connectString2))
try
{
// The transaction is promoted to a full distributed
// transaction when connection2 is opened.
connection2.Open();
// Execute the second command in the second database.
returnValue = 0;
SqlCommand command2 = new SqlCommand(commandText2, connection2);
returnValue = command2.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
}
catch (Exception ex)
{
// Display information that command2 failed.
writer.WriteLine("returnValue for command2: {0}", returnValue);
writer.WriteLine("Exception Message2: {0}", ex.Message);
}
}
catch (Exception ex)
{
// Display information that command1 failed.
writer.WriteLine("returnValue for command1: {0}", returnValue);
writer.WriteLine("Exception Message1: {0}", ex.Message);
}
}
// If an exception has been thrown, Complete will not
// be called and the transaction is rolled back.
scope.Complete();
}
// The returnValue is greater than 0 if the transaction committed.
if (returnValue > 0)
{
writer.WriteLine("Transaction was committed.");
}
else
{
// You could write additional business logic here, notify the caller by
// throwing a TransactionAbortedException, or log the failure.
writer.WriteLine("Transaction rolled back.");
}
// Display messages.
Console.WriteLine(writer.ToString());
return returnValue;
}
See Also
Other Resources
Transactions and Concurrency (ADO.NET)