Enterprise Services (.NET 1.1) Performance Guidelines - Transactions

From Guidance Share
Jump to navigationJump to search

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


Choose the Right Transaction Mechanism

Avoid configuring your components to use transactions unless you really need them. If your component reads data from a database only to display a report, there is no need for any type of transaction. If you do need transactions because you are performing update operations, choose the right transaction mechanism.

Use Enterprise Services transactions for the following:

  • You need to flow a transaction in a distributed application scenario. For example, you need to flow transactions across components.
  • You require a single transaction to span multiple remote databases.
  • You require a single transaction to encompass multiple resource managers; for example, a database and Message Queuing resource manager.


Choose the Right Isolation Level

Transaction isolation levels determine the degree to which transactions are protected from the effects of other concurrent transactions in a multiuser system. A fully isolated transaction offers complete isolation and guarantees data consistency; however, it does so at the expense of server resources and performance. When choosing an isolation level, consider the following guidelines:

  • Use Serializable if data read by your current transaction cannot by changed by another transaction until the current transaction is finished. This also prevents insertion of new records that would affect the outcome of the current transaction. This offers the highest level of data consistency and least concurrency compared to other isolation levels.
  • Use Repeatable Read if data read by your current transaction cannot by changed by another transaction until the current transaction is finished; however, insertion of new data is acceptable.
  • Use Read Committed if you do not want to read data that is modified and uncommitted by another transaction. This is the default isolation level of SQL Server.
  • Use Read Uncommitted if you do not care about reading data modified by others (dirty reads) which could be committed or uncommitted by another transaction. Choose this when you need highest concurrency and do not care about dirty reads.
  • Use Any for downstream components that need to use the same isolation level as an upstream component (transactions flowing across components). If the root component uses Any, the isolation level used is Serializable.

When you flow transactions across components, ensure that the isolation level for downstream components is set to Any, the same value as the upstream component or a lower isolation level. Otherwise, a run-time error occurs and the transaction is canceled.


Configuring the Isolation Level

On Microsoft Windows 2000 Server, it is not possible to change the isolation level when you use automated transactions. Consider using manual transactions such as ADO.NET transactions, using T-SQL hints, or adding the following line to your stored procedures. 


  SET TRANSACTION ISOLATION LEVEL READ COMMITTED

On Microsoft Windows Server 2003, you can configure the isolation level either administratively, by using Component Services, or programmatically, by setting the Transaction attribute for your component as shown in the following code sample. 


  [Transaction(Isolation=TransactionIsolationLevel.ReadCommitted)]


References


Use Compensating Transactions to Reduce Lock Times

A compensating transaction is a separate transaction that undoes the work of a previous transaction. Compensating transactions are a great way to reduce lock times and to avoid long running synchronous transactions. To reduce the length of a transaction, consider the following:

  • Do only work directly related to the transaction in the scope of the transaction.
  • Reduce the number of participants in the transaction by breaking the transaction into smaller transactions.

Consider an example where a Web application has to update three different databases when processing a request. When the system is under load, transactions might begin to time out frequently. The problem here is that all three databases have to hold locks until all three complete the work and report back to the transaction coordinator. By using compensating transactions, you can break the work into three logical pieces — each of which can complete faster, releasing locks sooner — and therefore increase concurrency. The trade-off here is that you will have to create code that coordinates a "logical" transaction and deal with failure conditions if one of the updates fails. In this event, you need to execute a compensating transaction on the other two databases to keep data consistent across all three.


References

For more information about performing distributed transaction with a .NET Framework provider, see the following Knowledge Base articles: