ADO.NET 1.1 Performance Guidelines - Transactions

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman


Use SQL Transactions for Server-Controlled Transactions on a Single Data Store

If you need to write to a single data store, and if you can complete the operation in a single call to the database, use the transaction control provided by the SQL language on your database server. The transaction runs close to the data and reduces the cost of the transaction. Running the transaction close to the data also permits database administrators to tune the operation without changing the deployment of your application code. The following code fragment shows a simple T-SQL transaction performed in a stored procedure.


UPDATE Orders SET Freight=@Freight Where OrderID=@OrderID
UPDATE [Order Details] SET Quantity=@Quantity Where OrderID=@OrderID

IF (@@ERROR > 0)

Note If you need to control a transaction across multiple calls to a single data store, use ADO.NET manual transactions.

Use ADO.NET Transactions for Client-Controlled Transactions on a Single Data Store

If you need to make multiple calls to a single data store participate in a transaction, use ADO.NET manual transactions. The .NET Data Provider for SQL Server and the .NET Data Provider for Oracle use the appropriate transaction language to enforce transactions on all subsequent SQL commands.

If you use SQL Profiler to monitor your use of ADO.NET manual transactions, you see that BEGIN TRAN, COMMIT TRAN, or ROLLBACK TRAN is run against the data store on your behalf by the provider. This enables you to control the transaction from your .NET Framework code and to maintain performance at a level that is similar to SQL transactions. The following code fragment shows how to use ADO.NET transactions.

SqlConnection conn = new SqlConnection(connString);
SqlTransaction trans = conn.BeginTransaction();
  SqlCommand cmd = new SqlCommand("MyWriteProc",conn, trans);
  cmd.CommandType = CommandType.StoredProcedure;
  …  // additional transactioned writes to database

When you use ADO.NET manual transactions, you can set the desired isolation level on the BeginTransacion method as shown in the following code fragment.

SqlConnection conn = new SqlConnection(connString);
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

Use DTC for Transactions That Span Multiple Data Stores

Enterprise Services uses the Microsoft Distributed Transaction Coordinator (DTC) to enforce transactions. If you have a transaction that spans multiple data stores or resource manager types, it is best to use Enterprise Services to enlist the data sources in a distributed transaction. Using Enterprise Services to enlist the data sources in this scenario is simple to configure.

The DTC performs the inter-data source communication and ensures that either all the data is committed or that none of the data is committed. This action creates an operational cost. If you do not have transactions that span multiple data sources, use SQL or ADO.NET manual transactions because they perform better.

Keep Transactions as Short as Possible

Design your code to keep transactions as short as possible to help minimize lock contention and to increase throughput. Avoid selecting data or performing long operations in the middle of a transaction.

Use the Appropriate Isolation Level

Resource managers such as SQL Server and other database systems support various levels of isolation for transactions. Isolation shields operations from the effect of other concurrent transactions. Most resource managers support the four isolation levels shown in Table 12.2. The isolation level determines the types of operation that can occur. The types of operation that can occur include dirty reads, nonrepeatable reads, or phantoms.

Table Isolation Levels

Isolation level Dirty reads Nonrepeatable reads Phantoms
Read uncommitted Yes Yes Yes
Read committed No Yes Yes
Repeatable read No No Yes
Serializable No No No

The highest isolation level, serializable, protects a transaction completely from the effects of other concurrent transactions. This is the most expensive isolation level in terms of server resources and performance. By selecting a lower level of isolation and writing the code for your transactions to deal with the effects of other concurrent transactions, you can improve performance and scalability. However, this approach may come at the expense of more complex code.

Avoid Code That Can Lead to Deadlock

Consider the following general guidelines when you use transactions so that you can avoid causing deadlocks:

  • Always access tables in the same order across transactions in your application. The likelihood of a deadlock increases when you access tables in a different order each time you access them.
  • Keep transactions as short as possible. Do not make blocking or long-running calls from a transaction. Keep the duration of the transactions short. One approach is to run transactions close to the data source. For example, run a transaction from a stored procedure instead of running the transaction from a different computer.
  • Choose a level of isolation that balances concurrency and data integrity. The highest isolation level, serializable, reduces concurrency and provides the highest level of data integrity. The lowest isolation level, read uncommitted, gives the opposite result.

Set the Connection String Enlist Property to False

A pooled transactional object must enlist its connection into the current transaction manually. To enable it to do so, you must disable automatic transaction enlistment by setting the connection string Enlist property to False.

Note This applies to a SqlConnection. For an OleDbConnection, you need to set OLE DB Services=-7 as a connection string parameter.

Pooled components that maintain database connections might be used in different transactions by separate clients. A pooled transactional object must be able to determine if it is activated in a new transaction that is different from the last time it was activated.

Each time a pooled transactional object is activated, it should check for the presence of a COM+ transaction in its context by examining ContextUtil.Transaction. If a transaction is present and the connection is not already enlisted, the object should enlist its connection manually by calling the EnlistDistributedTransaction method of the Connection object.

Personal tools