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.