When using transaction is a good idea and when it isn’t 

TransactionScope Class: the framework to the rescue 

private static void BaseCaseSuccessTest()
{
    //Let's get the connection to SQL Server
    var connection = GetConnection();
    try
    {
        using (TransactionScope rootScope = new TransactionScope())
        {
            using (connection)
            {
                connection.Open();
                // All code placed here will take part in the transaction
                connection.Close();
            }
            rootScope.Complete();
        }
    }
    catch (TransactionAbortedException tae)
    {
        Console.WriteLine("Test aborted: " + tae.Message);
    }
}

private static void SuppressTest()
{
    var connection = GetConnection();
    try
    {
        using (TransactionScope scope1 = new TransactionScope())
        {
            using (connection)
            {
                connection.Open();
                // Do some work
                using (TransactionScope scope2 = new TransactionScope(TransactionScopeOption.Suppress))
                {
                    // Non transactional work here

                    var current = Transaction.Current; // current is equal to null

                    // This will create a new ambient transaction, as the upper level (scope3) is currently running with no ambient transaction
                    using (TransactionScope scope3 = new TransactionScope(TransactionScopeOption.Required))
                    {
                        current = Transaction.Current;  // current is now not null
                        scope3.Complete();
                    }

                    // Calling scope2.Complete() is not mandatory, given that the operations here are non transactional, but it is nevertheless
                    // recommended to do so for consistency sake among the code
                    scope2.Complete();
                }
                connection.Close();
            }
            scope1.Complete();
        }
    }
    catch (TransactionAbortedException tae)
    {
        Console.WriteLine("Test aborted: " + tae.Message);
    }
}

Transaction isolation level

I will start citing MSDN here:
“By default, the transaction executes with isolation level set to Serializable. Selecting an isolation level other than Serializable is commonly used for read-intensive systems. […]
Every isolation level besides Serializable is susceptible to inconsistency resulting from other transactions accessing the same information.”

Now, what does it mean in terms of performance that the default isolation level is set to Serializable? Let’s see what wikipedia has to say about it:
“This is the highest isolation level. It specifies that all transactions occur in a completely isolated fashion, or, in other words, as if all transactions in the system had executed serially, one after the other. The DBMS may execute two or more transactions at the same time only if the illusion of serial execution can be maintained.
With a lock-based concurrency control DBMS implementation, serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction.“

As pointed out in this MSDN blog, the default behaviour can be harmful if you don’t know exactly what you’re doing and what usage patterns your system will have, because you might end up having deadlock and timeout problems, without a clue about why this is happening!

Let’s now see an example that shows how to create a non default TransactionScope object, manually setting the isolation level to ReadCommitted and the timeout for our transaction:

private static void TransactionWithIsolationAndTimeout()
{
    var transactionScopeOptions = new TransactionOptions();
    // The default isolation level value is Serializable
    // Here we explicitely ask the framework to create a transaction with isolation level ReadCommitted:
    // Volatile data cannot be read during the transaction, but can be modified.
    transactionScopeOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
    transactionScopeOptions.Timeout = TimeSpan.MaxValue;
    var connection = GetConnection();
    try
    {
        using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required, transactionScopeOptions))
        {
            using (connection)
            {
                connection.Open();
                // Do transactional work using DB connection
                connection.Close();
            }
            scope1.Complete();
        }
    }
    catch (TransactionAbortedException tae)
    {
        Console.WriteLine("Test aborted: " + tae.Message);
    }
}

To end this topic, it’s important to note that when using nested TransactionScope objects, all nested scopes must be configured to use exactly the same isolation level if they want to join the ambient transaction. If a nested TransactionScope object tries to join the ambient transaction yet it specifies a different isolation level, an ArgumentException is thrown.

Local transaction vs. Distributed one

Up until now we’ve seen examples of just one SQL connection shared amongst several TransactionScope objects (and sometimes transactions), opened and closed only once.
This means that only one SQL server and one database is concerned.
This is what is called a local transaction, also known as lightweight.
A distributed transaction is a local transaction that has been escalated to MSDTC (Microsoft Distributed Transaction Coordinator).
Lightweight transactions should be the preferred solution, when possible, due to better performance because escalating to MSDTC adds overhead to the whole process (and saves some headache configuring it!).
So, how to avoid escalating? There is a lot about this topic on the internet because it’s not easy to understand exactly when a transaction is escalated and how to configure MSDTC to correctly handle your requests.
Browsing Stack Overflow I found a very interesting post  that casts some bright light on the problem and that  you can read for yourself.
Anyway to summarize and to remain practical, SQL Server 2005 and 2008 have different behaviour handling and escalating transactions:

SQL2008:

using (TransactionScope transactionScope = new TransactionScope()) {
   using (SqlConnection connection = new   SqlConnection(connectionString)) {
      connection.Open();
      connection.Close();
      connection.Open(); // escalates to DTC
   }
}