.Net CLR 事务
.Net CLR 事务:事务的执行不是通过在数据库书写脚本完成的。而是通过c#,vb.net这些开发语言在应用层进行书写。运用该技术来编写事务程序会很轻松,较少了开发工作量。
传统事务
SqlConnection conn = new SqlConnection("Data Source=192.168.1.87;Initial Catalog=chapter23;Uid=sa;Pwd=123456;"); SqlCommand cmd = new SqlCommand(); SqlTransaction tran; string sqlText = "Insert into Students Values(@StudentId,@Firstname,@Lastname,@Company)"; cmd.Connection = conn; cmd.CommandText = sqlText; cmd.Parameters.AddWithValue("@StudentId", StudentId); cmd.Parameters.AddWithValue("@Firstname", Firstname); cmd.Parameters.AddWithValue("@Lastname", Lastname); cmd.Parameters.AddWithValue("@Company", Company); conn.Open(); tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted ,"InsertStudents"); try { cmd.Transaction = tran; cmd.ExecuteNonQuery(); throw new Exception("Bomb!"); tran.Commit(); } catch { tran.Rollback(); } finally { conn.Close(); }分析:
上面这段代码是传统的事务编程:事务发起后,如果在事务结束前一切运行正常则提交事务,否则就回滚事务。当然这些控制代码也是在应用程序上编写的。
分布式事务(跨连接)
public void CreateWithLogiclTransaction(CommittableTransaction tran) { //var tran = new CommittableTransaction(); DisplayTransactionInfo("刚初始化:", tran.TransactionInformation); SqlConnection conn = new SqlConnection("Data Source=192.168.1.87; Initial Catalog=chapter23;Uid=sa;Pwd=123456;"); SqlCommand cmd = new SqlCommand(); string sqlText = "Insert into Students Values(@StudentId,@Firstname,@Lastname,@Company)"; cmd.Connection = conn; cmd.CommandText = sqlText; cmd.Parameters.AddWithValue("@StudentId", StudentId); cmd.Parameters.AddWithValue("@Firstname", Firstname); cmd.Parameters.AddWithValue("@Lastname", Lastname); cmd.Parameters.AddWithValue("@Company", Company); conn.Open(); conn.EnlistTransaction(tran); DisplayTransactionInfo("登记后:", tran.TransactionInformation); try { cmd.ExecuteNonQuery(); } catch { } finally { conn.Close(); } }DisplayTransactionInfo:
public void DisplayTransactionInfo(string caption,TransactionInformation tranInfo) { StringBuilder sb = new StringBuilder(); sb.Append(caption + "\n"); sb.AppendFormat("{0}:{1}\n", "CreateTime", tranInfo.CreationTime); sb.AppendFormat("{0}:{1}\n", "Status", tranInfo.Status); sb.AppendFormat("{0}:{1}\n", "Local ID", tranInfo.LocalIdentifier); sb.AppendFormat("{0}:{1}\n", "Distributed ID" ,tranInfo.DistributedIdentifier); Console.Write(sb.ToString()); }调用代码:
CommittableTransaction tran = new CommittableTransaction(); Student clark = new Student() { StudentId = 3 , Firstname = "clark" , Lastname = "ben" , Company = "sun" }; Student alic = new Student() { StudentId = 4 , Firstname = "alic" , Lastname = "ka" , Company = "facebook" }; clark.CreateWithLogiclTransaction(tran); alic.CreateWithLogiclTransaction(tran); if (Abort()) { tran.Rollback(); Console.WriteLine("回滚事务"); DisplayTransactionInfo("回滚后的事务:",tran.TransactionInformation); } else { tran.Commit(); Console.WriteLine("提交事务"); DisplayTransactionInfo("提交后的事务:", tran.TransactionInformation); }执行结果:
刚初始化:
CreateTime:2012/2/29 7:51:46
Status:Active
Local ID:d0cbe7ff-d055-4a74-a8d2-3f29004b36b1:1
Distributed ID:00000000-0000-0000-0000-000000000000
登记后:
CreateTime:2012/2/29 7:51:46
Status:Active
Local ID:d0cbe7ff-d055-4a74-a8d2-3f29004b36b1:1
Distributed ID:00000000-0000-0000-0000-000000000000
刚初始化:
CreateTime:2012/2/29 7:51:46
Status:Active
Local ID:d0cbe7ff-d055-4a74-a8d2-3f29004b36b1:1
Distributed ID:00000000-0000-0000-0000-000000000000
登记后:
CreateTime:2012/2/29 7:51:46
Status:Active
Local ID:d0cbe7ff-d055-4a74-a8d2-3f29004b36b1:1
Distributed ID:03772a75-bd11-4d9f-b87a-c541d9820e39
是否忽略事务:[y/n]
y
回滚事务
回滚后的事务:
CreateTime:2012/2/29 7:51:46
Status:Aborted
Local ID:d0cbe7ff-d055-4a74-a8d2-3f29004b36b1:1
Distributed ID:03772a75-bd11-4d9f-b87a-c541d9820e39
依赖型事务
类似于父子关系,一个父事务下面可以挂多个子事务。当父事务提交后可以做如下决定:等待所有子事务运行完毕后决定是否提交本次事务;取消所有子事务并强行提交该事务。在生成依赖事务的时候通过指定相应的DependentCloneOption值来决定采用哪种策略来管理事务。
public void CreateWithLogiclAppendenceTransaction() { CommittableTransaction tran = new CommittableTransaction(); DisplayTransactionInfo("刚初始化:", tran.TransactionInformation); SqlConnection conn = new SqlConnection("Data Source=192.168.6.4;Initial Catalog=chapter23;Uid=sa;Pwd=123456;"); SqlCommand cmd = new SqlCommand(); string sqlText = "Insert into Students Values(@StudentId,@Firstname,@Lastname,@Company)"; cmd.Connection = conn; cmd.CommandText = sqlText; cmd.Parameters.AddWithValue("@StudentId", StudentId); cmd.Parameters.AddWithValue("@Firstname", Firstname); cmd.Parameters.AddWithValue("@Lastname", Lastname); cmd.Parameters.AddWithValue("@Company", Company); conn.Open(); conn.EnlistTransaction(tran); DisplayTransactionInfo("登记后:", tran.TransactionInformation); try { cmd.ExecuteNonQuery(); //产生依赖型事务 new Thread((o) => { DependentTransaction dTran = o as DependentTransaction; DisplayTransactionInfo("依赖事务", dTran.TransactionInformation); Thread.Sleep(5000); dTran.TransactionCompleted += (arg1,arg2) => { DisplayTransactionInfo("依赖事务" ,((Transaction)arg1).TransactionInformation); }; dTran.Complete(); }).Start(tran.DependentClone(DependentCloneOption.RollbackIfNotComplete)); //.Start(tran.DependentClone(DependentCloneOption.BlockCommitUntilComplete)); //这里描述依赖事务的提交机制:阻塞或取消 if (Abort()) { throw new Exception("Bomb!"); } tran.Commit(); DisplayTransactionInfo("提交后:", tran.TransactionInformation); } catch { tran.Rollback(); DisplayTransactionInfo("回滚后:", tran.TransactionInformation); } finally { conn.Close(); } }执行代码:
Student bank = new Student() { StudentId = 5 , Firstname = "Bank" , Lastname = "China" , Company = "China Govement" }; bank.CreateWithLogiclAppendenceTransaction();执行结果:
刚初始化:
CreateTime:2012/2/29 8:39:50
Status:Active
Local ID:90da8aca-a23e-4a67-9da3-96fedfa5903b:1
Distributed ID:00000000-0000-0000-0000-000000000000
登记后:
CreateTime:2012/2/29 8:39:50
Status:Active
Local ID:90da8aca-a23e-4a67-9da3-96fedfa5903b:1
Distributed ID:00000000-0000-0000-0000-000000000000
依赖事务
CreateTime:2012/2/29 8:39:50
Status:Active
Local ID:90da8aca-a23e-4a67-9da3-96fedfa5903b:1
Distributed ID:00000000-0000-0000-0000-000000000000
是否忽略事务:[y/n]
n
依赖事务
CreateTime:2012/2/29 8:39:50
Status:Committed
Local ID:90da8aca-a23e-4a67-9da3-96fedfa5903b:1
Distributed ID:00000000-0000-0000-0000-000000000000
提交后:
CreateTime:2012/2/29 8:39:50
Status:Committed
Local ID:90da8aca-a23e-4a67-9da3-96fedfa5903b:1
Distributed ID:00000000-0000-0000-0000-000000000000
环境事务
在编程的时候划分一块区域,将这区域中的所有操作都绑定到一个事务上,当程序执行到该区域的结尾部分时仍没有提及该事务,则自动做回滚处理。这么做的优点就是可以灵活的让事务在多个方法中共享。
public void CreateWithLogiclScopeTransaction() { using (var scope = new TransactionScope()) { Transaction.Current.TransactionCompleted+=(arg1,arg2)=>{ DisplayTransactionInfo("完成后环境事务" ,((Transaction)arg1).TransactionInformation); }; DisplayTransactionInfo("初始化后的环境事务" , Transaction.Current.TransactionInformation); SqlConnection conn = new SqlConnection("Data Source=192.168.6.4;Initial Catalog=chapter23;Uid=sa;Pwd=1234;"); SqlCommand cmd = new SqlCommand(); string sqlText = "Insert into Students Values(@StudentId,@Firstname,@Lastname,@Company)"; cmd.Connection = conn; cmd.CommandText = sqlText; cmd.Parameters.AddWithValue("@StudentId", StudentId); cmd.Parameters.AddWithValue("@Firstname", Firstname); cmd.Parameters.AddWithValue("@Lastname", Lastname); cmd.Parameters.AddWithValue("@Company", Company); try { conn.Open(); cmd.ExecuteNonQuery(); if (Abort()) { throw new Exception("Bomb"); } scope.Complete(); } catch { } finally { conn.Close(); } } }调用代码:
Student bank = new Student() { StudentId = 5 , Firstname = "Bank" , Lastname = "China" , Company = "China Govement" }; bank.CreateWithLogiclAppendenceTransaction();执行结果:
Local ID:4fc268f6-76ad-48fb-901a-d8f683208d10:1
Distributed ID:00000000-0000-0000-0000-000000000000
登记后:
CreateTime:2012/2/29 8:52:16
Status:Active
Local ID:4fc268f6-76ad-48fb-901a-d8f683208d10:1
Distributed ID:00000000-0000-0000-0000-000000000000
依赖事务
CreateTime:2012/2/29 8:52:16
Status:Active
Local ID:4fc268f6-76ad-48fb-901a-d8f683208d10:1
Distributed ID:00000000-0000-0000-0000-000000000000
是否忽略事务:[y/n]
n
依赖事务
CreateTime:2012/2/29 8:52:16
Status:Committed
Local ID:4fc268f6-76ad-48fb-901a-d8f683208d10:1
Distributed ID:00000000-0000-0000-0000-000000000000
提交后:
CreateTime:2012/2/29 8:52:16
Status:Committed
Local ID:4fc268f6-76ad-48fb-901a-d8f683208d10:1
Distributed ID:00000000-0000-0000-0000-000000000000
嵌套的环境事务
环境事务都是独立存在的,不管是否被嵌套在另一个环境事务中。如果你想将这两个环境事务联系起来,那么使用依赖事务是个不错的解决办法。
public void CreateWithLogiclNestedScopeTransaction() { using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required)) { DisplayTransactionInfo("父环境事务", Transaction.Current.TransactionInformation); Transaction.Current.TransactionCompleted+=((arg1, arg2) => { DisplayTransactionInfo("完成后的父事务" ,((Transaction)arg1).TransactionInformation); }); SqlConnection conn = new SqlConnection("Data Source=192.168.6.4; Initial Catalog=chapter23;Uid=sa;Pwd=123456;"); SqlCommand cmd = new SqlCommand(); string sqlText = "Insert into Students Values(@StudentId,@Firstname,@Lastname,@Company)"; cmd.Connection = conn; cmd.CommandText = sqlText; cmd.Parameters.AddWithValue("@StudentId", StudentId); cmd.Parameters.AddWithValue("@Firstname", Firstname); cmd.Parameters.AddWithValue("@Lastname", Lastname); cmd.Parameters.AddWithValue("@Company", Company); try { conn.Open(); cmd.ExecuteNonQuery(); new Thread((o) => { using (TransactionScope scope2 = new TransactionScope(TransactionScopeOption.Required)) { DisplayTransactionInfo("子环境事务" , Transaction.Current.TransactionInformation); Transaction.Current.TransactionCompleted += (arg1, arg2) => { DisplayTransactionInfo("完成后的子事务" , ((Transaction)arg1).TransactionInformation); }; SqlConnection conn2 = new SqlConnection("Data Source= 192.168.6.4; Initial Catalog=chapter23; Uid=sa;Pwd=123456;"); SqlCommand cmd2 = new SqlCommand(); string sqlText2 = "Insert into Students Values(1,'tmp','tmp','tmp')"; cmd2.Connection = conn2; cmd2.CommandText = sqlText2; try { conn2.Open(); cmd2.ExecuteNonQuery(); } catch { } finally { conn2.Close(); } } }).Start(); if (Abort()) { throw new Exception("Bomb"); } else { scope1.Complete(); } } catch { } finally { conn.Close(); } } }分析:
上面这段代码声明了2个环境事务,这2个环境事务是没有任何关联的。由于事务scope2没有提交,所以在scope2内的所有数据操作都会被回滚。
执行结果:
父环境事务
CreateTime:2012/2/29 9:12:04
Status:Active
Local ID:c9bfc6e2-14a6-4263-8692-d470e0dfc8f5:1
Distributed ID:00000000-0000-0000-0000-000000000000
是否忽略事务:[y/n]
n
子环境事务
CreateTime:2012/2/29 9:12:04
Status:Active
Local ID:c9bfc6e2-14a6-4263-8692-d470e0dfc8f5:2
Distributed ID:00000000-0000-0000-0000-000000000000
完成后的子事务
CreateTime:2012/2/29 9:12:04
Status:Aborted
Local ID:c9bfc6e2-14a6-4263-8692-d470e0dfc8f5:2
Distributed ID:00000000-0000-0000-0000-000000000000
完成后的父事务
CreateTime:2012/2/29 9:12:04
Status:Committed
Local ID:c9bfc6e2-14a6-4263-8692-d470e0dfc8f5:1
Distributed ID:00000000-0000-0000-0000-000000000000
嵌套的环境事务(能保持一致性)
上面我们说了嵌套的环境事务是彼此独立的,如果要将它们联系起来就必须使用依赖事务,现在我们就来实现这个功能。
public void CreateWithLogiclNestedTogeterScopeTransaction() { using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required)) { DisplayTransactionInfo("父环境事务" , Transaction.Current.TransactionInformation); Transaction.Current.TransactionCompleted += ((arg1, arg2) => { DisplayTransactionInfo("完成后的父事务" , ((Transaction)arg1).TransactionInformation); }); SqlConnection conn = new SqlConnection("Data Source=192.168.6.4; Initial Catalog=chapter23;Uid=sa;Pwd=123456;"); SqlCommand cmd = new SqlCommand(); string sqlText = "Insert into Students Values(@StudentId,@Firstname,@Lastname,@Company)"; cmd.Connection = conn; cmd.CommandText = sqlText; cmd.Parameters.AddWithValue("@StudentId", StudentId); cmd.Parameters.AddWithValue("@Firstname", Firstname); cmd.Parameters.AddWithValue("@Lastname", Lastname); cmd.Parameters.AddWithValue("@Company", Company); try { conn.Open(); cmd.ExecuteNonQuery(); new Thread((o) => { DependentTransaction dTran = (DependentTransaction)o; Transaction.Current = dTran; DisplayTransactionInfo("子环境事务" , Transaction.Current.TransactionInformation); //Thread.Sleep(5000); Transaction.Current.TransactionCompleted += (arg1, arg2) => { DisplayTransactionInfo("完成后的子事务" , ((Transaction)arg1).TransactionInformation); }; SqlConnection conn2 = new SqlConnection("Data Source= 192.168.6.4; Initial Catalog=chapter23; Uid=sa;Pwd=123456;"); SqlCommand cmd2 = new SqlCommand(); string sqlText2 = "Insert into Students Values(1,'tmp','tmp','tmp')"; cmd2.Connection = conn2; cmd2.CommandText = sqlText2; try { using (TransactionScope scope2 = new TransactionScope(TransactionScopeOption.Required)) { conn2.Open(); cmd2.ExecuteNonQuery(); scope2.Complete(); } } catch { } finally { dTran.Complete(); conn2.Close(); } }).Start(Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete)); if (Abort()) { throw new Exception("Bomb"); } else { scope1.Complete(); } } catch { } finally { conn.Close(); } } }分析:
上面代码一共涉及了3个事务,它们分别为:环境事务scope1,scope2和依赖事务dTran。Scope1事务与Scope2事务通过dTran联系起来。在执行的过程中,如果Scope2事务在超出作用域后没有被提交,那么Scope1事务就做回滚操作。dTran事务在操作过程中必须提交,来保证与scope1的联系。
执行结果
父环境事务
CreateTime:2012/3/1 3:00:48
Status:Active
Local ID:0fe52e89-38ee-4d05-9876-1815447d5a2f:1
Distributed ID:00000000-0000-0000-0000-000000000000
子环境事务
CreateTime:2012/3/1 3:00:48
Status:Active
Local ID:0fe52e89-38ee-4d05-9876-1815447d5a2f:1
Distributed ID:00000000-0000-0000-0000-000000000000
Sub Scope:
CreateTime:2012/3/1 3:00:48
Status:Active
Local ID:0fe52e89-38ee-4d05-9876-1815447d5a2f:1
Distributed ID:76140857-74e2-470a-89b8-ecbdab6f3357
是否忽略事务:[y/n]
N
完成后的父事务
CreateTime:2012/3/1 3:00:48
Status:Committed
Local ID:0fe52e89-38ee-4d05-9876-1815447d5a2f:1
Distributed ID:76140857-74e2-470a-89b8-ecbdab6f3357
完成后的子事务
CreateTime:2012/3/1 3:00:48
Status:Committed
Local ID:0fe52e89-38ee-4d05-9876-1815447d5a2f:1
Distributed ID:76140857-74e2-470a-89b8-ecbdab6f3357
总结:
上面介绍的事务技术通过合理的组合能快捷的开发出功能强大的事务处理机制。在使用该类事务技术时必须保证“DTC”服务已经在运行(net start msdtc)。