Export (0) Print
Expand All
Expand Minimize

Transaction Control

 

Priya Dhawan
Microsoft Developer Network

November 2001

Summary: This article describes how to run local and distributed transactions in your Microsoft .NET applications. (14 printed pages)

Contents

Introduction
Local and Distributed Transactions
   Database Transactions
   Manual Transactions
   Automatic Transactions
Conclusion

Introduction

A transaction is a series of operations performed as a single unit of work. By binding a set of related operations together in a transaction, you ensure the consistency and reliability of the system despite any errors that occur. All operations in a transaction must complete successfully in order to make the transaction successful.

A transaction has a beginning and an end that specify its boundary within which it can span processes and machines. All resources within a transaction boundary participate in the same transaction. To maintain consistency across resources within a transaction boundary, a transaction must exhibit ACID properties, which are Atomicity, Consistency, Isolation, and Durability. See Processing Transactions in the Microsoft .NET Framework SDK for details on transaction processing fundamentals.

In this article, we will show how you can run local and distributed transactions in your Microsoft .NET applications.

Local and Distributed Transactions

A local transaction is one whose scope is a single transaction-aware data resource, such as a Microsoft® SQL Server™ database or MSMQ message queue. For example, a single database system can enforce the ACID rules when it holds all the data involved in a transaction. In the case of SQL Server, there is an internal transaction manager that provides commit and rollback behavior.

Distributed transactions can span heterogeneous transaction-aware data resources and may include a wide range of activities such as retrieving data from a SQL Server database, reading messages from a Message Queue Server, and writing to other databases. Programming of distributed transactions is simplified by software that coordinates commit and abort behavior and recovery across several data resources. Microsoft Distributed Transaction Coordinator (DTC) is one such technology. It implements a two-phase commit protocol that ensures the transaction outcome is consistent across all data resources involved in a transaction. DTC only supports applications that have implemented compatible interfaces for management of transactions. These applications are referred to as Resource Managers (see Distributed Transactions in the .NET Framework Developer's Guide for more information on this topic) and many are currently available, including MSMQ, Microsoft SQL Server, Oracle, Sybase, and others.

Database Transactions

Invoking a stored procedure that wraps required operations within the BEGIN TRANSACTION and COMMIT/ROLLBACK TRANSACTION statements yields the best performance by allowing you to run the transaction in a single round-trip to the database server. Database transactions also support nested transactions, which means you can start a new transaction from within an active transaction.

In the following code snippet, the BEGIN TRANSACTION statement begins a new transaction. You can end a transaction either by committing changes to the database using the COMMIT TRANSACTION statement or by undoing all the changes if any error occurs using the ROLLBACK TRANSACTION statement:

CREATE PROCEDURE Proc1
…
AS
   -- Begin the transaction
   BEGIN TRANSACTION
   -- Do transaction operations
   …
   -- Check for any Error
   If @@Error <> 0
      -- Rollback the Transaction
      ROLLBACK TRANSACTION
   …
   -- Commit the Transaction
   COMMIT TRANSACTION

The next stored procedure accepts an XML representation of an Order as an input parameter. To make appropriate insertions into the Orders and OrderDetails tables, the stored procedure loads and parses the XML using the sp_xmlpreparedocument system stored procedure. As you see in the code, the stored procedure wraps all the operations in an explicit transaction so that if any of the operations fail to execute all the changes made are rolled back.

Note that the procedure sets XACT_ABORT to ON, which specifies that the SQL server will automatically roll back the transaction in case any of the statements fail to complete.

CREATE PROCEDURE InsertOrder 
@Order  NVARCHAR(4000) = NULL
, @OrderId int Output
AS
   SET NOCOUNT ON
   DECLARE @hDoc INT
   DECLARE @PKId  INT
   -- Specify that the SQL Server automatically rolls back the current 
   -- transaction if a Transact-SQL statement raises a run-time error.
   SET XACT_ABORT ON
   -- Begin the transaction
   BEGIN TRANSACTION
   -- Load and Parse the incoming XML represeting an Order into an
   -- XMLDocument
   EXEC sp_xml_preparedocument @hDoc OUTPUT, @Order
   -- Select order header from the Order node in the XMLDocument and      
   -- insert it into the Orders table
   INSERT Orders(CustomerId,
                  OrderDate,
                  ShipToName,
                  ShipToAddressId,
                  OrderStatus)
SELECT CustomerId, CONVERT(DateTime,OrderDate), ShipToName,
 ShipToAddressId, OrderStatus
   FROM OPENXML(@hDoc, '/NewDataSet/Orders') 
   WITH ( CustomerId int 'CustomerId',
          OrderDate nvarchar(23) 'OrderDate',
          ShipToName nvarchar(40) 'ShipToName',
          ShipToAddressId int 'ShipToAddressId',
          OrderStatus  int 'OrderStatus')
   -- Select the OrderId of the Order just inserted into the Orders table
   -- to use it while inserting order details
   SELECT @PKId = @@IDENTITY
   -- Select order details from the Details node in the XMLDocument and      
   -- insert them into the OrderDetails table
   INSERT OrderDetails (OrderId,
                       ItemId,
                       UnitPrice,
                       Quantity) 
   SELECT @PKId as OrderId, ItemId, UnitPrice, Quantity
   FROM OPENXML(@hDoc, '/NewDataSet/Details') 
   WITH (ItemId int 'ItemId',
         UnitPrice money 'UnitPrice',
         Quantity int 'Quantity')
   -- Set the Output parameter
   Select @OrderId = @PKId 
   -- Commit the transaction
   COMMIT TRANSACTION
   EXEC sp_xml_removedocument @hDoc 
   RETURN 0
GO

Although it provides good performance, you need to code in Transact SQL, which is not as easy to code in as a .NET-compatible language.

Manual Transactions

A manual transaction allows you to explicitly control the transaction boundary with explicit instructions to begin and end the transaction. This model also supports nested transactions that allow you to start a new transaction from within an active transaction. The trade off for this control is that there is an extra burden on you to enlist data resources with the transaction boundary and coordinating these data resources. There is no built-in support for distributed transactions and so it will be a lot of responsibility if you choose to control a distributed transaction manually; you will need to control every connection and resource enlistment, and provide implementation to maintain ACID properties of the transaction.

ADO.NET manual transactions

Both Microsoft ADO.NET data providers enable manual transactions by providing a set of objects that create a connection to the data store, begin a transaction, commit or abort the transaction, and finally close the connection. We will be using ADO.NET SQL managed provider for our examples.

To perform operations within a single transaction, you need to create an SQLTransaction object, begin the transaction using an SQLConnection object, ensure our database interaction occurs within a transaction, and commit or abort the transaction. The SQLTransaction object provides a variety of methods and properties to control a transaction. You can commit changes made to the database using the Commit method if every operation in the transaction completed successfully. To rollback changes use the Rollback method of the SQLTransaction object.

Note   The Transaction property of the Command object must be set to an already started transaction for it to execute within the transaction.

Visual Basic .NET

Dim conn as SQLConnection
Dim cmd as SQLCommand
Dim txn As SQLTransaction
conn = New SQLConnection("ConnString")
cmd = New SQLCommand
' Open a conection
conn.Open()
' Begin a transaction 
txn = conn.BeginTransaction()
' Set the Transaction in which the command executes
cmd.Transaction = Txn
…

Visual C# .NET

SQLConnection Conn = New SQLConnection("ConnString");
SQLCommand Cmd = New SQLCommand;
// Open a connection
Conn.Open();
// Begin a transaction
SQLTransaction Txn = Conn.BeginTransaction();
// Set the Transaction in which the command executes
Cmd.Transaction = Txn;
…

In the following example, we execute two SQL commands within the boundary of a transaction. The first command inserts the order header of an order into the Orders table and returns the OrderId of the newly inserted order, which is used by the second command to insert the detail of the same order into the OrderDetails table. The transaction is aborted if either of the two commands fails to execute thereby preventing the rows from being added to the database.

Visual Basic .NET

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim tran As SqlTransaction
' Create a New Connection
conn = New SqlConnection("ConnString")
' Open the Connection
conn.Open()
' Create a new Command object
cmd = New SqlCommand()
' Create a new Transaction
tran = conn.BeginTransaction
' Set the Transaction within which the Commands execute
cmd.Transaction = tran
Try
  ' Insert the Order header
  ' Set the Command properties
   With cmd
      .CommandType = CommandType.StoredProcedure
      .CommandText = "InsertOrderHeader"
      .Connection = conn
      ' Add input and output parameters
      .Parameters.Add("@CustomerId", SqlDbType.Int)
      .Parameters("@CustomerId").Direction = ParameterDirection.Input
      …
      ' Set the parameter values
      .Parameters("@CustomerId").Value = 1
      …
      ' Execute the command
      .ExecuteNonQuery()
      ' Get the OrderId of the newly selected order header
      OrderId = .Parameters("@OrderId").Value
      ' Clear the parameters for the next command
      .Parameters.clear()
   End With

   ' Insert the Order Details
   ' Set Command properties 
   With cmd
      .CommandType = CommandType.StoredProcedure
      .CommandText = "InsertOrderDetail"
      .Connection = conn
      ' Add parameters
      .Parameters.Add("@OrderId", SqlDbType.Int)
      .Parameters("@OrderId").SourceColumn = "OrderId"
      .Parameters("@OrderId").Direction = ParameterDirection.Input
      …
      ' Set the parameter values
      .Parameters("@OrderId").Value = OrderId
      .Parameters("@ItemId").Value = 100
      …
      ' Execute the command
      .ExecuteNonQuery()
      ' Repeat the above few lines for each order detail
   End With
   
   ' Commit the Transaction
   tran.Commit()
Catch
   ' Rollback the Transaction
   tran.Rollback()
Finally
   ' Cleanup Code
   ' Close the Connection
   conn.Close()
End Try

As you see in the above code fragment, the two commands are being executed as part of a single transaction. If either of them fails, the transaction aborts and any changes made to the database are rolled back. Wrapping the code in a try/catch/finally block ensures that your transaction executes correctly; the transaction is committed at the very end of the try block when both the commands have been executed successfully. Any exception thrown is caught in the catch block where the transaction is aborted to undo changes made within the transaction.

Controlling a transaction through ADO.NET objects results in less efficient locking as compared to using explicit transactions in stored procedures. The reason is that an ADO.NET manual transaction takes at least as many round trips to the DBMS as there are operations to execute within the transaction in addition to trips that begin and end the transaction. You keep hold of locks while calls are sent back and forth between your ADO.NET code and the database server.

MSMQ manual transactions

The .NET Framework supports MSMQ transactions in two different ways: manually (internally) by allowing multiple messages to be sent or received as part of a transaction and automatically (externally) by participating in Distributed Transaction Coordinator (DTC) transactions.

MSMQ manual transactions are supported through the MessageQueueTransaction class and are handled entirely inside the MSMQ engine. Please refer to Duncan Mackenzie's article, Reliable Messaging with MSMQ and .NET, for details.

Automatic Transactions

The .NET Framework relies on MTS/COM+ services to support automatic transactions. COM+ uses the Microsoft Distributed Transaction Coordinator (DTC) as a transaction manager and a transaction coordinator to run transactions in a distributed environment. This enables a .NET application to run a transaction that combines diverse activities across multiple resources such as inserting an order into a SQL Server database, writing a message to a Microsoft Message Queue (MSMQ) queue, sending an e-mail message, and retrieving data from an Oracle database.

By providing a programming model based on declarative transactions, COM+ makes it very simple for your application to run transactions that span heterogeneous resources. The caveat is that it pays a performance penalty due to DTC and COM interoperability overhead and there is no support for nested transactions.

ASP.NET pages, Web Service methods, and .NET classes can be marked to be transactional by setting a declarative transaction attribute.

ASP.NET

<@ Page Transaction="Required">

ASP.NET Web Service

<%@ WebService Language="VB" Class="Class1" %>
<%@ assembly name="System.EnterpriseServices" %>
…
Public Class Class1
   Inherits WebService
   <WebMethod(TransactionOption := TransactionOption.RequiresNew)> _ 
Public Function Method1()
…

To participate in automatic transactions, a .NET class must be inherited from the System.EnterpriseServices.ServicedComponent class, which enables it to run inside COM+. When you do this, COM+ interacts with the DTC to create a distributed transaction and also enlists all resource connections behind the scenes. You also need to set a declarative transaction attribute on the class to determine its transactional behavior.

Visual Basic .NET

<Transaction(TransactionOption.Required)> Public Class Class1
    Inherits ServicedComponent

Visual C# .NET

[Transaction(TransactionOption.Required)]
public class Class1 : ServicedComponent {
…
}

The transaction attribute for a class can be set to any of the following options:

  • Disabled—indicates that the object is never created in a COM+ transaction. The object can directly use DTC for transactional support.
  • NotSupported—indicates that the object is never created in a transaction.
  • Supported—indicates that the object runs in the context of its creator's transaction. If the object is the root object itself or its creator is not running in a transaction, the object is created without a transaction.
  • Required—indicates that the object runs in the context of its creator's transaction. If the object is the root object itself or its creator is not running in a transaction, the object is created with a new transaction.
  • RequiresNew—indicates that the object requires a transaction and the object is created with a new transaction.

The following code shows a .NET class configured to run within COM+, with assembly attributes set to configure the COM+ application properties.

Visual Basic .NET

Imports System
Imports System.Runtime.CompilerServices
Imports System.EnterpriseServices
Imports System.Reflection

'Registration details.
'COM+ application name as it appears in the COM+ catalog
<Assembly: ApplicationName("Class1")> 
'Strong name for assembly
<Assembly: AssemblyKeyFileAttribute("class1.snk")> 
<Assembly: ApplicationActivation(ActivationOption.Server)> 

<Transaction(TransactionOption.Required)> Public Class Class1
    Inherits ServicedComponent
    Public Sub Example1()
      …
    End Sub
End Class

Visual C# .NET

using System;
using System.Runtime.CompilerServices;
using System.EnterpriseServices;
using System.Reflection;

//Registration details.
//COM+ application name as it appears in the COM+ catalog
[Assembly: ApplicationName("Class1")]
'Strong name for assembly
[Assembly: AssemblyKeyFileAttribute("class1.snk")]
[Assembly: ApplicationActivation(ActivationOption.Server)]

[Transaction(TransactionOption.Required)]
public class Class1 : ServicedComponent {
       [AutoComplete]
    public void Example1() 
{
        …
    }
}

<Assembly: ApplicationName("Class1")> specifies the name of the COM+ application to install the components of the assembly into. <Assembly: ApplicationActivation(ActivationOption.Server)> specifies if the COM+ application is a server or library application. The assembly must be installed into the global assembly cache (GAC) using the gacutil command-line tool (GacUtil.exe) when you specify ApplicationActivation(ActivationOption.Server).

You can use the Regsvcs.exe command-line tool to convert the assembly to a type library, and register and install the type library into the specified COM+ application. The tool also configures properties that you have added programmatically to your assembly. For example, if ApplicationActivation(ActivationOption.Server) is specified in the assembly, the tool will create a server application. If an assembly is invoked without being installed with COM+, the run time creates and registers a type library and installs it with COM+. You can see and configure the COM+ application created for your assembly in the Component Services snap-in.

The process to create, register, and use serviced components is covered in detail in Writing Serviced Components in the .NET Framework Developer's Guide.

The following code fragment shows a transactional class configured to run under COM+, which executes two SQL commands within the boundary of a transaction. The first command inserts the order header of an order into the Orders table and returns the OrderId of the newly inserted order, which is used by the second command to insert the detail of the same order into the OrderDetails table. The transaction is aborted if either of the two commands fails to execute thereby preventing the rows from being added to the database.

Visual Basic .NET

<Transaction(TransactionOption.Required)> Public Class Class1
    Inherits ServicedComponent
    
 Public Sub Example1()
        …
        Try
            ' Create a New Connection
            conn = New SqlConnection("ConnString")
            ' Open the Connection
            conn.Open()
            ' Create a new Command object
            cmd = New SqlCommand()
            ' Insert the Order header
            ' Set the Command properties
            With cmd1
                .CommandType = CommandType.StoredProcedure
                .CommandText = "InsertOrderHeader"
                .Connection = conn
                ' Add input and output parameters
                .Parameters.Add("@CustomerId", SqlDbType.Int)
                …
               .ExecuteNonQuery()
               ' Clear the parameters for the next command
               .Parameters.clear()
            End With
            
            ' Insert all the Order details
            ' Set Command properties
            With cmd
                .CommandType = CommandType.StoredProcedure
                .CommandText = "InsertOrderDetail"
                .Connection = conn
                ' Add parameters
                .Parameters.Add("@OrderId", SqlDbType.Int)
                …
                ' Execute the command
               .ExecuteNonQuery()
                ' Repeat the above few lines for each order 
                     detail
            End With
            
            ' Commit the Transaction
            ContextUtil.SetComplete()
        Catch
            ' Rollback the Transaction
            ContextUtil.SetAbort()
        Finally
            ' Cleanup Code
        End Try
    End Sub

Using the System.EnterpriseServices.ContextUtil class, you can obtain information about the COM+ object context. It exposes SetComplete and SetAbort methods to explicitly commit and rollback a transaction respectively. As you might expect, the ContextUtil.SetComplete method is called at the very end of the try block when all the operations have been executed successfully to commit the transaction. Any exception thrown is caught in the catch block where the transaction is aborted using ContextUtil.SetAbort.

You can also cause a serviced component to automatically vote to commit or abort the transaction using the System.EnterpriseServices.AutoComplete attribute class. The component votes in favor of committing the transaction if the method call returns successfully. If the method call throws an exception, the transaction is aborted automatically; you don't need to explicitly call ContextUtilSetAbort. To use this feature, insert the <AutoComplete> attribute before the class method:

Visual Basic .NET

<Transaction(TransactionOption.Required)> Public Class Class1
    Inherits ServicedComponent
    <AutoComplete()> Public Sub Example1()
      …
    End Sub
End Class

Visual C# .NET

[Transaction(TransactionOption.Required)]
public class Class1 : ServicedComponent {
       [AutoComplete]
    public void Example1() 
{
        …
    }
}

The <AutoComplete> attribute offers the easiest way to do transactional programming by allowing you to avoid having to explicitly commit or rollback transactions. You get the exact same functionality the previous example where we explicitly called ContextUtil.SetAbort in the catch block to abort the transaction. The disadvantage is that it is not obvious that transactions are involved at all and may be forgotten when maintaining the code at a later time. Also, it does not allow you to throw a user-friendly message in case you want to when a transaction fails. In such cases you should explicitly catch any exceptions, call ContextUtil.SetAbort, and throw the custom message.

In systems that need transactions to run across MSMQ and other transaction-aware resources such as a SQL Server database, there is no choice but to use DTC or COM+ transactions. DTC coordinates all the resource managers participating in a distributed transaction and also manages activities related to the transaction. Please refer to Duncan Mackenzie's article, Reliable Messaging with MSMQ and .NET, for an example of distributed transaction spanning MSMQ and SQL Server.

Conclusion

Each transaction technique offers trade-offs with respect to application performance and code maintainability. Running a database transaction implemented in a stored procedure offers the best performance as it needs only a single round trip to the database. It also gives the flexibility of explicitly controlling the transaction boundary. Although it provides good performance and flexibility, you need to code in Transact SQL, which is not as easy to code as in .NET.

Manual transactions using ADO.NET transaction objects are easy to code and give you the flexibility of controlling the transaction boundary with explicit instructions to begin and end the transaction. The trade off for this ease and flexibility is that it incurs a performance cost for extra round trips to the database to complete the transaction.

An automatic transaction will be the only choice if your transaction spans multiple transaction-aware resource managers which could include SQL Server databases, MSMQ Message Queues, etc. It greatly simplifies the application design and reduces coding requirements. However, since COM+ service does all the coordination work, it may have some extra overhead.

Show:
© 2014 Microsoft