What is a transaction, in one word?
ACID.
Atomicity: One block that can't be broken into smaller parts.
Consistency: Works on consistent view and leaves the DB in a constituently state.
Isolation: Effects only the transaction.
Durability: Writes the committed transactions to the database.
Single transaction vs. Distributed transactions:
Single transaction – a transaction that works on only one durable resource and on a single open connection (also called local lightweight transaction).These transactions are managed by the Lightweight Transaction Manager (LTM).
Distributed transaction – spans multiple durable resources. Works with two phases committed protocol and has a special manager that is called Distributed Transaction Coordinator (MS-DTC).
The System.Transaction namespace isn't part of the ADO.NET even tough it was develop by the Enterprise Services team in Microsoft. Before the release of the System.Transaction we used the old COM+ hosting model by inheriting from ServicedComponent class (and always using the MS-DTC manager).
The Transaction (in System.Transaction) is fully integrated with SQL server 2005. The transaction automatically promotes a single transaction to a distributed transaction if required.
It's really recommended to use the transaction with the using block. In the end of the block, just assign the complete flag by using the Transaction.Complete() method(This method can be called only once, the second call will throw an InvalidOperationException).
There are three types of transaction options that you can add to the connection:
1. Required (default): If there is an existed transaction - join it, else open a new transaction.
2. Requires new: Always open a new transaction (good for audit operation, when we want to commit the audit statement even though the transaction is rolled back).
3. Suppress: Run out-of-transaction even though there is an active transaction.
A transaction will promote to distribute transaction in three scenarios:
1. The durable resource doesn't implement the IPromotableSinglePhaseNotification.
2. Two durable resources in the same transaction.
3. When there are some application domains to one transaction.
Notice: If you want to create a new transaction manager you just need to implement the IEnlistmentNotification interface (and ISinglePhaseNotification interface for better performance)