.NET实现事务的编码方式
2018-03-19 10:32 xiashengwang 阅读(341) 评论(0) 编辑 收藏 举报1,在T-SQL语句中用begin tran,end tran的方式
begin tran --select top(1) * from dbo.test with(updlock) update test with(updlock) set name ='name_upd' where id = 1 commit tran
2,利用ADO.NET的SqlTransaction类
1)SqlConnection.ClientConnectionId可以查看出两个连接是否是连接池的同一个连接。
2)一个SqlTransaction只能对应同一个SqlConnection,并且该SqlConnection不能关闭,否则SqlTransaction失效抛出异常。 从这个角度看,如果我们New SqlConnection() 两个链接,而ClientConnectionId相同,一定会是连接池先关闭了第一个SqlConnection,再把连接分配给了第二个SqlConnection,第一个SqlConnection如果有关联的SqlTransaction也会由于第一个SqlConnection的关闭而无效。总之,不要试图用一个SqlTransaction在多个SqlConnection间实现事务,这是行不通的。
3,利用.NET2.0提供的TransactionScope类
TransactionScope类可以实现分布式事务,不用在DB处理层处理事务,代码灵活也更加简洁,推荐使用。
msdn的一个例子:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
// This function takes arguments for 2 connection strings and commands to create a transaction // involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the // transaction is rolled back. To test this code, you can connect to two different databases // on the same server by altering the connection string, or to another 3rd party RDBMS by // altering the code in the connection2 code block. static public int CreateTransactionScope( string connectString1, string connectString2, string commandText1, string commandText2) { // Initialize the return value to zero and create a StringWriter to display results. int returnValue = 0; System.IO.StringWriter writer = new System.IO.StringWriter(); try { // Create the TransactionScope to execute the commands, guaranteeing // that both commands can commit or roll back as a single unit of work. using (TransactionScope scope = new TransactionScope()) { using (SqlConnection connection1 = new SqlConnection(connectString1)) { // Opening the connection automatically enlists it in the // TransactionScope as a lightweight transaction. connection1.Open(); // Create the SqlCommand object and execute the first command. SqlCommand command1 = new SqlCommand(commandText1, connection1); returnValue = command1.ExecuteNonQuery(); writer.WriteLine("Rows to be affected by command1: {0}", returnValue); // If you get here, this means that command1 succeeded. By nesting // the using block for connection2 inside that of connection1, you // conserve server and network resources as connection2 is opened // only when there is a chance that the transaction can commit. using (SqlConnection connection2 = new SqlConnection(connectString2)) { // The transaction is escalated to a full distributed // transaction when connection2 is opened. connection2.Open(); // Execute the second command in the second database. returnValue = 0; SqlCommand command2 = new SqlCommand(commandText2, connection2); returnValue = command2.ExecuteNonQuery(); writer.WriteLine("Rows to be affected by command2: {0}", returnValue); } } // The Complete method commits the transaction. If an exception has been thrown, // Complete is not called and the transaction is rolled back. scope.Complete(); } } catch (TransactionAbortedException ex) { writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message); } catch (ApplicationException ex) { writer.WriteLine("ApplicationException Message: {0}", ex.Message); } // Display messages. Console.WriteLine(writer.ToString()); return returnValue; }
几篇关于TransactionScope的博客
https://blog.appliedis.com/2015/05/27/the-magic-of-transactionscope/
下面这些情况会被提升成MSDTC分布式事务
1)不同服务器的数据库
2)同一服务器,不同数据库(连接字符串不同)。
3)同一服务器,同一数据库(连接字符串相同)。但出现了嵌套调用的情况。
就是外层开了一个SqlConnection,还没有关闭,里面又New了一个SqlConnection,这时,.Net的连接池会开两个单独的连接,两个连接当然会 当成分布式的事务了。
要保证事务不被提升成分布式事务,有两种方式。
1)数据库字符串只能用同一个,事务内的所有操作,都共用同一个SqlConnection。
2)数据库字符串只能用同一个,事务内的所有操作,都必须串行的关闭自己开的连接。这种情况连接池会共用同一连接。
一般写了DBHelper的工程,都是满足这个条件的。
要在同一事务内,访问同一服务器的不同数据库。有两种方式:
① 在sql文中,显示指定数据库名: TestDB..Table1
② 利用SqlConnection的ChangeDatabase方法,更改成目标数据库名,然后执行SQL语句,记住用完切回原来的数据库。
4,如果是跨服务器部署数据库,那就要必须用分布式事务了,启用MSDTC的方法
http://blog.csdn.net/zy0421911/article/details/52225082
可以用DTCPing.exe软件测试两端是否正常连通