爱华网先创建一个用于测试的数据库表：
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 classCustomerUpdater
{
public void Test(int customerId, string customerName)
{
TransactionOptions transactionOptions = newTransactionOptions();
transactionOptions.IsolationLevel =System.Transactions.IsolationLevel.Serializable;
using (TransactionScope txnScope = newTransactionScope(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}' whereid={1}";
private string selectText = "select * from customer whereid={0}";
private string connectionString = "Server=.;IntegratedSecurity=SSPI;Database=Test;";
private void updateCustomer(int id, string name)
{
string commandText = string.Format(updateText, name, id);
using (SqlConnection sconn = newSqlConnection(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 = newSqlConnection(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 withanother process and has been chosen as the deadlock victim. Rerunthe transaction."
查看trace 1222的日志，摘取最后一部分：
2011-01-28 17:37:17.94spid17sresource-list
2011-01-28 17:37:17.94spid17skeylock hobtid=72057594042515456 dbid=6objectname=Test.dbo.Customer indexname=PK_Customer id=lock420b7c0mode=S associatedObjectId=72057594042515456
2011-01-28 17:37:17.94spid17sowner-list
2011-01-28 17:37:17.94spid17sowner id=process38a988 mode=S
2011-01-28 17:37:17.94spid17sowner id=process38ac58 mode=S
2011-01-28 17:37:17.94spid17swaiter-list
2011-01-28 17:37:17.94spid17swaiter id=process38a988 mode=X requestType=convert
2011-01-28 17:37:17.94spid17swaiter 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后再试，问题依旧存在。直到将隔离级别调到readcommitted时，死锁终于消失。跟踪到的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 whereid=2后，发现name为'0:1'。虽然降低隔离级别可以避免死锁，但是代价却是不一致读。如果我的这块业务逻辑比较敏感，要求线程安全，那么这么做是不能满足线程安全的要求的。TransactionScope在管理实务的策略选择上太过乐观，提前允许每个进程执行查询语句，占有共享锁。这对于数据一致性要求比较高的这块业务逻辑来说，根本难以满足我的要求。
我们知道，TransactionScope在某种程度上是作为SqlTransaction的替代品出现的。那么我们看看SqlTransaction看看，将CustomerUpdater改为SqlTransaction来实现一个新的类：
代码
public class CustomerUpdater2
{
public void Test(int customerId, string customerName)
{
using (SqlConnection sconn = newSqlConnection(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}' whereid={1}";
private string selectText = "select * from customer whereid={0}";
private string connectionString = "Server=.;IntegratedSecurity=SSPI;Database=Test;";
private void updateCustomer(int id, string name, SqlTransactionstran)
{
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(objectseed)方法中使用的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(IsolationLevel=Serializable)的时候，避免了在使用TransactionScope时所碰到的死锁问题。其实在《SQLSERVER中的两种常见死锁及解决思路》一文中，我对这种死锁用数据库脚本做了模拟，其实可以发现，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 differentIsolationLevel than the value requested for the scope.
这也算是不用传入事务对象带来便利同时的问题，对于这个问题，不知你们有没有什么办法，欢迎分享。
