Summary

• Transactions have ACID attributes: atomicity, consistency, isolation, and durability. Data locking gives transactions their ACID properties. Locking is costly; in some applications, you can adjust the extent of the locking by setting the transaction isolation level.

• A transaction is a unit of work that is done with a single durable resource, and a distributed transaction is a unit of work that is done with multiple durable resources.

• Transactions can be initiated from T-SQL statements, the DbConnection object's BeginTran method, or the TransactionScope object (which is in the System.Transactions namespace). The System.Transactions namespace is not part of ADO.NET but provides a programming model for transactions that should always be used when working with SQL Server 2005.

• You can view distributed transactions by using the Distributed Transaction Coordinator (DTC), which displays the committed, aborted, and total transaction counts, as well as other information.

• Using the TransactionScope object, you can create delegated transactions, which are light-weight transactions managed by the resource manager. If another durable resource enlists in the same transaction, the lightweight transaction is promoted to a distributed transaction.

• You can build your own transactional resource manager by implementing the IEnlistmentNotification interface. Add the ISinglePhaseNotification interface for better performance when there is only one resource in the transaction.

• The SQLCLR also has access to the current transaction through its Transaction.Current property. If you create a TransactionScope object within the SQLCLR while a lightweight transaction is active, the lightweight transaction is promoted to a distributed transaction.

• Transactions should always be coded using the just-in-time approach, where you create the transaction just before it is needed, do the work, and commit as quickly as possible.