近来在开发中涉及到调用已有的业务实现的dll，在做并发模拟的时候，很容易就出现deadlock。而且业务实现中对外公布的接口方法中使用了TransactionScope来管理事务。纠缠于此几天，今天做了一些模拟来一探究竟。
先创建一个用于测试的数据库表：
create table Customer(id int not null,
name nvarchar(50) not null,
primary key(id));
再插入两条数据：
insert into Customer
select 2,'0'
创建一个修改客户名称的类：
代码
public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
    public class CustomerUpdater
    {
        public void Test(int customerId, string customerName)
        {
            TransactionOptions transactionOptions = new TransactionOptions();
            transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            using (TransactionScope txnScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions, EnterpriseServicesInteropOption.Automatic))
            {
                //read customer
                Customer customer = getCustomer(customerId);
                //update customer
                if (customer != null)
                {
                    updateCustomer(customer.Id, customer.Name + ":" + customerName);
                }
                txnScope.Complete();
            }
        }
        private string updateText = "update customer set name='{0}' where id={1}";
        private string selectText = "select * from customer where id={0}";
        private string connectionString = "Server=.;Integrated Security=SSPI;Database=Test;";
        private void updateCustomer(int id, string name)
        {
            string commandText = string.Format(updateText, name, id);
            using (SqlConnection sconn = new SqlConnection(connectionString))
            {
                sconn.Open();
                SqlCommand scomm = new SqlCommand(commandText, sconn);
                scomm.ExecuteNonQuery();
            }
        }
        private Customer getCustomer(int id)
        {
            string commandText = string.Format(selectText, id);
            using (SqlConnection sconn = new SqlConnection(connectionString))
            {
                sconn.Open();
                SqlCommand scomm = new SqlCommand(commandText, sconn);
                using (IDataReader dataReader = scomm.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        Customer customer = new Customer { Id = dataReader.GetInt32(0), Name = dataReader.GetString(1) };
                        return customer;
                    }
                }
            }
            return null;
        }
    }
然后在Main中启动多个线程来执行：
代码
class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Start to test TransactionScope & SqlTransaction...");
            try
            {
                for (int i = 0; i < 10; i++)
                {
                    ThreadPool.QueueUserWorkItem(new WaitCallback(updateCustomerName), i + 1);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
            }
            Console.ReadLine();
        }
        private static void updateCustomerName(object seed)
        {
            var impl = new CustomerUpdater2();
            impl.Test(2, seed.ToString());
        }
    }
启动程序，出现异常，debug中断：
"Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
查看trace 1222的日志，摘取最后一部分：
2011-01-28 17:37:17.94 spid17s       resource-list
2011-01-28 17:37:17.94 spid17s        keylock hobtid=72057594042515456 dbid=6 objectname=Test.dbo.Customer indexname=PK_Customer id=lock420b7c0 mode=S associatedObjectId=72057594042515456
2011-01-28 17:37:17.94 spid17s         owner-list
2011-01-28 17:37:17.94 spid17s          owner id=process38a988 mode=S
2011-01-28 17:37:17.94 spid17s          owner id=process38ac58 mode=S
2011-01-28 17:37:17.94 spid17s         waiter-list
2011-01-28 17:37:17.94 spid17s          waiter id=process38a988 mode=X requestType=convert
2011-01-28 17:37:17.94 spid17s          waiter id=process38ac58 mode=X requestType=convert
打开SQL profiler，再次执行一次，跟踪到的SQL语句如下：
//测试1 - Serializable - deadlock
select * from customer where id=2
select * from customer where id=2
select * from customer where id=2
update customer set name='0:3' where id=2
update customer set name='0:1' where id=2
update customer set name='0:2' where id=2
每个进程执行两条sql语句，可以看出这三个进程是分别先执行查询语句后，再分别执行修改语句的。由于他们处于不同进程的事务中，可以明显地看出，三个进程由于都拥有pk为2的customer表的数据行的共享锁，而共同请求转换为排它锁时产生死锁。最后数据库在仲裁的时候，将进程1和进程2当作了牺牲品，而进程3则得以执行完成。
将CustomerUpdater中TransactionScope的隔离级别修改为repeatable后再试，问题依旧存在。直到将隔离级别调到read committed时，死锁终于消失。跟踪到的SQL语句如下：
//测试2 - read uncommitted - nonrepeatable read
select * from customer where id=2
select * from customer where id=2
select * from customer where id=2
update customer set name='0:3' where id=2(SQL:BatchStarting)
update customer set name='0:3' where id=2(SQL:BatchCompleted)
update customer set name='0:2' where id=2(SQL:BatchStarting)
update customer set name='0:1' where id=2(SQL:BatchStarting)
update customer set name='0:2' where id=2(SQL:BatchCompleted)
update customer set name='0:1' where id=2(SQL:BatchCompleted)
执行select * from customer where id=2后，发现name为'0:1'。虽然降低隔离级别可以避免死锁，但是代价却是不一致读。如果我的这块业务逻辑比较敏感，要求线程安全，那么这么做是不能满足线程安全的要求的。TransactionScope在管理实务的策略选择上太过乐观，提前允许每个进程执行查询语句，占有共享锁。这对于数据一致性要求比较高的这块业务逻辑来说，根本难以满足我的要求。
我们知道，TransactionScope在某种程度上是作为SqlTransaction的替代品出现的。那么我们看看SqlTransaction看看，将CustomerUpdater改为SqlTransaction来实现一个新的类：
代码
public class CustomerUpdater2
    {
        public void Test(int customerId, string customerName)
        {
            using (SqlConnection sconn = new SqlConnection(connectionString))
            {
                sconn.Open();
                SqlTransaction stran = sconn.BeginTransaction(System.Data.IsolationLevel.Serializable);
                try
                {
                    Customer customer = getCustomer(customerId, stran);
                    if (customer != null)
                    {
                        updateCustomer(customer.Id, customer.Name + ":" + customerName, stran);
                    }
                    stran.Commit();
                }
                catch (Exception) { stran.Rollback(); }
            }
        }
        private string updateText = "update customer set name='{0}' where id={1}";
        private string selectText = "select * from customer where id={0}";
        private string connectionString = "Server=.;Integrated Security=SSPI;Database=Test;";
        private void updateCustomer(int id, string name, SqlTransaction stran)
        {
            string commandText = string.Format(updateText, name, id);
            SqlCommand scomm = new SqlCommand(commandText, stran.Connection, stran);
            scomm.ExecuteNonQuery();
        }
        private Customer getCustomer(int id, SqlTransaction stran)
        {
            string commandText = string.Format(selectText, id);
            SqlCommand scomm = new SqlCommand(commandText, stran.Connection, stran);
            using (IDataReader dataReader = scomm.ExecuteReader())
            {
                if (dataReader.Read())
                {
                    Customer customer = new Customer { Id = dataReader.GetInt32(0), Name = dataReader.GetString(1) };
                    return customer;
                }
            }
            return null;
        }
    }
修改updateCustomerName(object seed)方法中使用的CustomerUpdater为CustomerUpdater2，再次测试，发现执行完全成功。跟踪到的SQL语句如下：
//测试3 - serializable
select * from customer where id=2
update customer set name='0:1' where id=2
select * from customer where id=2
update customer set name='0:1:3' where id=2
select * from customer where id=2
update customer set name='0:1:3:2' where id=2
从上面的SQL语句中可以发现，在使用SqlTransaction(Isolation Level=Serializable)的时候，避免了在使用TransactionScope时所碰到的死锁问题。其实在《SQL SERVER中的两种常见死锁及解决思路》一文中，我对这种死锁用数据库脚本做了模拟，其实可以发现，TransactionScope的行为与模拟的数据库脚本的行为在死锁上是一致的（对于两者的区别，数据库脚本只能在一个connection/session之下，而TransactionScope可以跨多个连接，由MSDTC/COM+来处理事务/同步上下文）。那为什么SqlTransaction在隔离级别设置为最高（Serializable）的时候都不会出现共享锁升级到排它锁的死锁呢？
那么SqlTransaction是否是线程安全的呢？我用10个线程用SqlTransaction来模拟对一个表的同一行进行追加更新，即便在Serializable的隔离级别下，只要线程足够多，依旧会出现线程被覆盖的现象，就像使用TransactionScope，在隔离级别设置为ReadCommitted及以下时候所出现的情况。
此外，TransactionScope在使用过程中虽然避免了对每个操作传入Transaction对象，但是会出现隔离级别不一致的问题。例如我在对已有的业务功能进行整合时，假设已有3个业务操作：OperationA(IsolationLevel.Serializable),OperationB(IsolationLevel.RepeatableRead),OperationC(IsolationLevel.ReadCommitted)，我现在需要在一些特殊的业务场景下封装已有的部分操作，而这个业务场景下，我也希望引入事务管理来保证数据一致性，那么这个时候就喷到问题了，我在声明TransactionScope的时候，隔离级别究竟指定为什么呢？事实上代码在执行的时候会报如下的异常：
The transaction specified for TransactionScope has a different IsolationLevel than the value requested for the scope.
这也算是不用传入事务对象带来便利同时的问题，对于这个问题，不知你们有没有什么办法，欢迎分享。