Distributed Transactions

using System;
using System.Data.SqlClient;
using System.Transactions;

namespace ConsoleApplication
{
public class Program
{
/// <summary>
/// This program illustrates the fact that transactions running on
/// different database connections can share their locks when they
/// are bound by the Distributed Transaction Coordinator.
/// </summary>
public static void Main(string[] args)
{
string connectionString = @"
Initial Catalog=DistributedTransactions;
Data Source=localhost;
Integrated Security=SSPI;";

string updateStatement = "
UPDATE Account
SET Balance=5000
WHERE AccountID=1001;";

// This first transaction puts a write lock on Account 1001.
using (TransactionScope transactionScope1 = new TransactionScope())
{
SqlConnection sqlConnection1 = new SqlConnection(connectionString);
sqlConnection1.Open();

SqlCommand sqlCommand1 = new SqlCommand(updateStatement);
sqlCommand1.Connection = sqlConnection1;
sqlCommand1.ExecuteNonQuery();

// This second transaction could normally not update Account 1001.
// Add TransactionScopeOption.RequiresNew to provoke a deadlock.
using (TransactionScope transactionScope2 = new TransactionScope())
{
SqlConnection sqlConnection2 = new SqlConnection(connectionString);
sqlConnection2.Open();

SqlCommand sqlCommand2 = new SqlCommand(updateStatement);
sqlCommand2.Connection = sqlConnection2;
sqlCommand2.ExecuteNonQuery();

sqlConnection2.Close();
transactionScope2.Complete();
}

sqlConnection1.Close();
transactionScope1.Complete();
}
}
}
}