你的位置:首页 > 数据库

[数据库]简述.NET事务应用原则


.NET事务应用原则

1.在同一个数据库内进行CRUD时,应使用同一个DbConnection对象,且显式指定DbConnection均为同一个DbTransaction,示例代码如下:

//在同一个DB中操作一个表时,可以不用显式指定事务,因为单条SQL命令就是一个最小的事务单元    using (DbConnection conn = new SqlConnection("数据库连接字符串"))    {      var cmd = conn.CreateCommand();      cmd.CommandText = "delete users";      cmd.ExecuteNonQuery();    }    //在同一个DB中操作多个表或执行不同的SQL命令时,需要显式指定事务,且需确保每个Command均与同一个DbTransaction关联    using (DbConnection conn = new SqlConnection("数据库连接字符串"))    {      DbTransaction tran = conn.BeginTransaction();      try      {        var cmd = conn.CreateCommand();        cmd.Transaction = tran;        cmd.CommandText = "delete users";        cmd.ExecuteNonQuery();        var cmd2 = conn.CreateCommand();        cmd2.Transaction = tran;        cmd2.CommandText = "delete roles";        cmd2.ExecuteNonQuery();        tran.Commit();      }      catch (Exception ex)      {        Console.WriteLine(ex.Message);        tran.Rollback();      }    }

2.在同一个服务器上的不同数据库之间进行CRUD时,应使用同一个DbConnection对象,且显式指定DbConnection均为同一个DbTransaction,同时SQL命令语句中的包含的对象(表、视图、存储过程、函数等)应显式指定数据库名称,格式如:databasename.owner.tablename,如:Db1.dbo.Users;Db2.dbo.Users;(前提条件:多个数据库的用户名及密码相同的情况下,否则就只能使用分布式事务),示例代码如下:

//在同一个Server不同的DB中操作多个表或执行不同的SQL命令时,需要显式指定事务,且需确保每个Command均与同一个DbTransaction关联,CommandText还应显式添加数据库名称    using (DbConnection conn = new SqlConnection("数据库连接字符串"))    {      DbTransaction tran = conn.BeginTransaction();      try      {        var cmd = conn.CreateCommand();        cmd.Transaction = tran;        cmd.CommandText = "delete db1.dbo.users";        cmd.ExecuteNonQuery();        var cmd2 = conn.CreateCommand();        cmd2.Transaction = tran;        cmd2.CommandText = "delete db2.dbo.roles";        cmd2.ExecuteNonQuery();        tran.Commit();      }      catch (Exception ex)      {        Console.WriteLine(ex.Message);        tran.Rollback();      }    }

3.在不同的DB服务器上进行CRUD时,应使用分布式事务,可以采取隐式或显式开启分布式事务,示例代码如下:

//采用隐式开启分布式事务    using (TransactionScope tranScope = new TransactionScope(TransactionScopeOption.RequiresNew))    {      using (DbConnection conn = new SqlConnection("数据库连接字符串"))      {        conn.Open();        var cmd = conn.CreateCommand();        cmd.CommandText = "delete users";        cmd.ExecuteNonQuery();      }      using (DbConnection conn2 = new SqlConnection("数据库连接字符串2"))      {        conn2.Open();        var cmd2 = conn2.CreateCommand();        cmd2.CommandText = "delete users";        cmd2.ExecuteNonQuery();      }    }    //采用显式开启分布式事务    using (CommittableTransaction committableTransaction = new CommittableTransaction())    {      try      {        using (DbConnection conn = new SqlConnection("数据库连接字符串"))        {          conn.Open();          conn.EnlistTransaction(committableTransaction); //将连接登记到可提交事务          var cmd = conn.CreateCommand();          cmd.CommandText = "delete users";          cmd.ExecuteNonQuery();        }        using (DbConnection conn2 = new SqlConnection("数据库连接字符串2"))        {          conn2.Open();          conn2.EnlistTransaction(committableTransaction); //将连接登记到可提交事务          var cmd2 = conn2.CreateCommand();          cmd2.CommandText = "delete users";          cmd2.ExecuteNonQuery();        }        committableTransaction.Commit();      }      catch (Exception ex)      {        committableTransaction.Rollback(ex);      }    }    //采用显式开启分布式事务,模拟TransactionScope用法的过程    {      Transaction originalTransaction = Transaction.Current; //记录当前的环境事务,用于后面的恢复      CommittableTransaction committableTransaction = null;      DependentTransaction dependentTransaction = null;      committableTransaction = new CommittableTransaction();      Transaction.Current = committableTransaction;//将定义的可提交事务作为当前的环境事务      try      {        using (DbConnection conn = new SqlConnection("数据库连接字符串"))        {          conn.Open();          var cmd = conn.CreateCommand();          cmd.CommandText = "delete users";          cmd.ExecuteNonQuery();        }        dependentTransaction = Transaction.Current.DependentClone(DependentCloneOption.RollbackIfNotComplete); //复制当前的环境事务从而产生新的依赖事务,且指定必需等到该事务完成        Transaction.Current = dependentTransaction;//将复制到的新的依赖事务        using (DbConnection conn2 = new SqlConnection("数据库连接字符串2"))        {          conn2.Open();          var cmd2 = conn2.CreateCommand();          cmd2.CommandText = "delete users";          cmd2.ExecuteNonQuery();        }      }      catch (Exception ex)      {        Transaction.Current.Rollback(ex);      }      finally //不论成功与否,最终都将恢复成原来的环境事务      {        Transaction transaction = Transaction.Current;        Transaction.Current = originalTransaction;        transaction.Dispose();      }    }

最终总结一下:

1.查询无需事务;

2.涉汲执行增、删、改的SQL命令时,应考虑是否需要确保执行数据的一致性,若需要则必需使用事务,否则可以采取默认方式;

3.在同一个DB服务器中,尽可能的使用本地事务,跨多个DB服务器中,需要使用分布式事务;

4.尽可能的缩小事务的使用范围,避免出现多层级的嵌套事务;

5.若需要使用分布式事务,在WINDOWS下需要开启MS DTC服务(分布式事务管理器)