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

回到目录

上一讲中简单介绍了一个EF环境下通过DbCommand拦截器来实现SQLSERVER的读写分离，只是一个最简单的实现，而如果出现事务情况，还是会有一些问题的，因为在拦截器中我们手动开启了Connection链接，而在一个ＷＥＢ请求时，如果你的一个变量即用到了read库又用到了write库，就会导致到sqlserver端的spid（system process id，系统进程ＩＤ，sqlserver里可能是某个数据库进程序的ＩＤ）发生变化　，而对于这种变化，原本是本地的事务就会自动提升为分布式事务，对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

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

作者：仓储大叔，张占岭，

荣誉：微软MVP

QQ：853066980

支付宝扫一扫，为大叔打赏!