Choosing a Transaction Model
Prior to choosing a transaction model, you should consider whether or not you require transactions at all. Transactions are the single most expensive resource consumed by server applications, and they reduce scalability when used unnecessarily. Consider the following guidelines governing the use of transactions:
- Perform transactions only when you need to acquire locks across a set of operations and need to enforce ACID rules.
- Keep transactions as short as possible to minimize the amount of time that you hold database locks.
- Never place a client in control of transaction lifetime.
- Don't use a transaction for an individual SQL statement. SQL Server automatically runs each statement as an individual transaction.
Automatic vs. Manual Transactions
Although the programming model is somewhat simplified for automatic transactions, particularly when multiple components are performing database updates, manual local transactions are always significantly faster because they do not require interaction with the Microsoft DTC. This is true (although the performance degradation is reduced) even if you are using automatic transactions against a single local resource manager (such as SQL Server), because a manual, local transaction avoids any unnecessary interprocess communication (IPC) with the DTC.
Use manual transactions when:
- You are performing a transaction against a single database.
Use automatic transactions when:
- 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 a Windows 2000 Message Queuing (formerly known as MSMQ) resource manager.
Note Avoid mixing your transaction models. Use one or the other.
In application scenarios in which performance is deemed sufficient, it is reasonable to opt for automatic transactions (even against a single database) to simplify the programming model. Automatic transactions make it easy for multiple components to perform operations that are part of the same transaction.