• EF架构~通过EF6的DbCommand拦截器来实现数据库读写分离~续~添加事务机制


    回到目录

    上一讲中简单介绍了一个EF环境下通过DbCommand拦截器来实现SQLSERVER的读写分离,只是一个最简单的实现,而如果出现事务情况,还是会有一些问题的,因为在拦截器中我们手动开启了Connection链接,而在一个WEB请求时,如果你的一个变量即用到了read库又用到了write库,就会导致到sqlserver端的spid(system process id,系统进程ID,sqlserver里可能是某个数据库进程序的ID)发生变化 ,而对于这种变化,原本是本地的事务就会自动提升为分布式事务,对MSDTC不了解的同学,可能看我的相关文章,所以,我们使用拦截实现读写分离后,在程序里,你的读和写的仓储对象要分别定义,不能共享,而且,你在事务里所以写的仓储对象都要使用同一个数据上下文!

    当你按着我说的做后,本地事务就不会提升为msdtc了,如图:

    今天我在DbCommand拦截器进行了优化,下面共享一下代码,如是测试不是真实一项目代码

        /// <summary>
        /// SQL命令拦截器
        /// </summary>
        public class SqlCommandInterceptor : DbCommandInterceptor
        {
            /// <summary>
            /// 读库,从库集群,写库不用设置走默认的EF框架
            /// </summary>
            string readConn = System.Configuration.ConfigurationManager.AppSettings["readDb"] ?? string.Empty;
            private string GetReadConn()
            {
                var readArr = readConn.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                var resultConn = string.Empty;
                if (readArr != null && readArr.Any())
                {
                    resultConn = readArr[Convert.ToInt32(Math.Floor((double)new Random().Next(0, readArr.Length)))];
                }
                return resultConn;
            }
            //linq to entity生成的update,delete
            public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
            {
                base.NonQueryExecuting(command, interceptionContext);//update,delete等写操作直接走主库
            }
            /// <summary>
            /// 执行sql语句,并返回第一行第一列,没有找到返回null,如果数据库中值为null,则返回 DBNull.Value
            /// </summary>
            /// <param name="command"></param>
            /// <param name="interceptionContext"></param>
            public override void ScalarExecuting(DbCommand command,
               DbCommandInterceptionContext<object> interceptionContext)
            {
                if (!string.IsNullOrWhiteSpace(GetReadConn()))//如果配置了读写分离,就去实现
                {
                    if (!command.CommandText.StartsWith("insert", StringComparison.InvariantCultureIgnoreCase))
                    {
                        command.Connection.Close();
                        command.Connection.ConnectionString = GetReadConn();
                        command.Connection.Open();
                    }
                }
                base.ScalarExecuting(command, interceptionContext);
            }
    
            /// <summary>
            /// linq to entity生成的select,insert
            /// 发送到sqlserver之前触发
            /// warning:在select语句中DbCommand.Transaction为null,而ef会为每个insert添加一个DbCommand.Transaction进行包裹
            /// </summary>
            /// <param name="command"></param>
            /// <param name="interceptionContext"></param>
            public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
            {
                if (!string.IsNullOrWhiteSpace(GetReadConn()))//如果配置了读写分离,就去实现
                {
                    if (!command.CommandText.StartsWith("insert", StringComparison.InvariantCultureIgnoreCase))
                    {
                        command.Connection.Close();
                        command.Connection.ConnectionString = GetReadConn();
                        command.Connection.Open();
                    }
                }
                base.ReaderExecuted(command, interceptionContext);
            }
            /// <summary>
            /// 发送到sqlserver之后触发
            /// </summary>
            /// <param name="command"></param>
            /// <param name="interceptionContext"></param>
            public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
            {
                base.ReaderExecuted(command, interceptionContext);
            }
        }

    运行程序可以设置一些测试代码

     public ActionResult Index()
            {
                IUnitOfWork db = new backgroundEntities();
                IRepository<WebManageUsers> readUser = new BackgroundRepositoryBase<WebManageUsers>();
                var a = readUser.GetModel().ToList();//读库
                using (var trans = new TransactionScope())//事务写库
                {
                    IRepository<WebManageUsers> userWrite = new BackgroundRepositoryBase<WebManageUsers>(db);
                    IRepository<WebManageMenus> menuWrite = new BackgroundRepositoryBase<WebManageMenus>(db);
                    var entity = new WebManageUsers
                  {
                      WebSystemID = 0,
                      CreateDate = DateTime.Now,
                      DepartmentID = 3,
                      Description = "",
                      Email = "",
                      LoginName = "test",
                      Mobile = "",
                      Operator = "",
                      Password = "",
                      RealName = "test",
                      Status = 1,
                      UpdateDate = DateTime.Now,
                  };
                    var entity2 = new WebManageMenus
                   {
                       ParentID = 1,
                       About = "",
                       LinkUrl = "",
                       MenuLevel = 1,
                       MenuName = "test",
                       Operator = "",
                       SortNumber = 1,
                       Status = 1,
                       UpdateDate = DateTime.Now,
                   };
                    userWrite.Insert(entity);
                    menuWrite.Insert(entity2);
                    trans.Complete();
                }
    
    
    
                return View(a);
            }
    View Code

    最后的结果就是我们想要的,这里说明一点,仓储大步的读写分离没有数据库压力这块的考虑,只是随机去访问某个读库。

    回到目录

  • 相关阅读:
    openwrt的内核版本是在哪个文件中指定的?
    git如何将一个分支合并到另一个分支?
    cygwin如何下编译安装tmux?
    如何合并ts文件?
    在cygwin下创建的文件位于windows的哪个目录下?
    linux shell的for循环语法是怎样的?
    内部类访问局部变量时,为什么需要加final关键字
    Java8函数式编程的宏观总结
    Maven私服使用经验总结
    java关于Integer设置-128到127的静态缓存
  • 原文地址:https://www.cnblogs.com/lori/p/4210796.html
Copyright © 2020-2023  润新知