1 实现
CustomDbContext扩展了DbContext,其构造函数带有形式参nameOrConnectionString,可以在使用CustomDbContext时指定数据库连接字符串。
DbContextFactory包含两个属性MasterDbContext和SlaveDbContext,MasterDbContext为主库上下文,SlaveDbContext为从库上下文。DbContextFactory还包含了一个方法:UpdateSlaves用于实现对SlaveDbContext的更新,因为SlaveDbContext是从多个配置的从库随机取出一个,因此定时检测不可用从库,将其从从库集合中剔除。
JobScheduler为定时任务规划器,使用Quartz实现。quartz.config和quartz_jobs.xml为定时任务配置文件。
为了使定时任务工作,在WebApiApplication类的Application_Start()函数应添加:
JobScheduler jobScheduler = new JobScheduler();
jobScheduler.log4netPath = AppSettings.Log4netPathForWeb;
jobScheduler.OnStart();
关键代码
/// <summary>
/// 自定义上下文
/// </summary>
[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class CustomDbContext:DbContext
{
public CustomDbContext(string nameOrConnectionString)
: base(nameOrConnectionString)
{
Database.SetInitializer<CustomDbContext>(null);
}
......
public DbSet<Collection> Collections { get; set; }
public DbSet<CollectionUser> CollectionUsers { get; set; }
......
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
EntityConfiguration.Set(modelBuilder);
}
}
public class EntityConfiguration
{
public static void Set(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Collection>().Property(c => c.FileName)
.IsUnicode(false)
.IsRequired()
.HasMaxLength(50);
modelBuilder.Entity<Collection>().Property(c => c.TableName)
.IsUnicode(false)
.IsRequired()
.HasMaxLength(50);
modelBuilder.Entity<Collection>().Property(c => c.Title)
.IsUnicode(false)
.IsRequired()
.HasMaxLength(200);
modelBuilder.Entity<Collection>().Property(c => c.Author)
.IsUnicode(false)
.IsOptional()
.HasMaxLength(50);
modelBuilder.Entity<Collection>().Property(c => c.PublicationName)
.IsUnicode(false)
.IsOptional()
.HasMaxLength(200);
modelBuilder.Entity<Collection>().Property(c => c.DiscNo)
.IsUnicode(false)
.IsOptional()
.HasMaxLength(50);
modelBuilder.Entity<Collection>().Property(c => c.ResourceType)
.IsUnicode(false)
.IsOptional()
.HasMaxLength(50);
modelBuilder.Entity<Collection>().Property(c => c.PublisherUnit)
.IsUnicode(false)
.IsOptional()
.HasMaxLength(200);
modelBuilder.Entity<Collection>().Property(c => c.Year)
.IsUnicode(false)
.IsOptional()
.HasMaxLength(10);
modelBuilder.Entity<Collection>().Property(c => c.Period)
.IsUnicode(false)
.IsOptional()
.HasMaxLength(10);
modelBuilder.Entity<Collection>().Property(c => c.PublicationDate)
.IsOptional();
modelBuilder.Entity<Collection>().Property(c => c.Downloads)
.IsOptional();
modelBuilder.Entity<Collection>().Property(c => c.CitationNumber)
.IsOptional();
}
}
/// <summary>
/// db上下文工厂
/// </summary>
public class DbContextFactory
{
private static List<string> allSlaves = GetAllSlaves();
private DbContextFactory() { }
/// <summary>
/// 主
/// </summary>
public static CustomDbContext MasterDbContext
{
get
{
return new CustomDbContext("name=Master");
}
}
/// <summary>
/// 从
/// </summary>
public static CustomDbContext SlaveDbContext
{
get
{
Random rm = new Random();
if (allSlaves.Count > 0)
{
int i = rm.Next(allSlaves.Count);
string name = string.Format("name={0}", allSlaves.ElementAt(i));
return new CustomDbContext(name);
}
else
{
return MasterDbContext;
}
}
}
/// <summary>
/// 获得所有可用连接
/// </summary>
/// <returns></returns>
private static List<string> GetAllSlaves()
{
List<string> connNames = new List<string>();
var conns = ConfigurationManager.ConnectionStrings;
if (conns == null)
{
throw new Exception("ConfigurationManager.ConnectionStrings 是空值,请检查Web.config");
}
var masterConn = conns["Master"];
if (masterConn == null)
{
throw new Exception("名称为Master的连接配置不存在,请检查Web.config");
}
//conn中必然包含master,还有一个默认的LocalSqlServer
int connCount = conns.Count - 1;
if (connCount == 0)
{
throw new Exception("连接配置中只包含Master,不包含任何Slave,请检查Web.config,并配置Slave");
}
for (int i = 0; i < connCount; i++)
{
string connName = string.Format("Slave{0}", i);
var conn = ConfigurationManager.ConnectionStrings[connName];
if (conn == null)
{
string msg = string.Format("{0}不存在,请检查配置Web.config", connName);
throw new Exception(msg);
}
//检测是否可连接
bool canConn = CanConnect(connName);
if (canConn)
{
connNames.Add(connName);
}
}
return connNames;
}
public static void UpdateSlaves()
{
allSlaves = GetAllSlaves();
if (allSlaves.Count == 0)
{
allSlaves.Add("Master");
}
}
private static bool CanConnect(string connName)
{
bool ret = false;
DbConnection dbConnection = null;
try
{
string connStr = ConfigurationManager.ConnectionStrings[connName].ToString();
MySqlConnectionFactory factory = new MySqlConnectionFactory();
dbConnection = factory.CreateConnection(connStr);
dbConnection.Open();//打不开会抛异常
ret = true;
}
catch (Exception ex)
{
}
finally
{
if (dbConnection != null && dbConnection.State == System.Data.ConnectionState.Open) dbConnection.Close();
}
return ret;
}
}
public class JobScheduler
{
/// <summary>
/// log4net配置文件位置
/// </summary>
public string log4netPath { get; set; }
private IScheduler scheduler;
public JobScheduler() { }
public void OnStart()
{
//构造函数自动加载Quartz.config,并通过quartz.plugin.xml.fileNames加载~/quartz_jobs.xml
try
{
if (string.IsNullOrWhiteSpace(log4netPath))
{
log4netPath = AppSettings.Log4netPathForApp;
}
//加载日志
LogConfigLoading.Load(log4netPath);
ISchedulerFactory sf = new StdSchedulerFactory();
scheduler = sf.GetScheduler();
scheduler.Start();
}
catch (Exception ex)
{
LogHelper.LogError(ex, "JobScheduler");
}
}
public void OnStop()
{
if (scheduler != null && scheduler.IsStarted)
{
scheduler.Shutdown(false);
}
}
public void OnPause()
{
if (scheduler != null && scheduler.IsStarted)
{
scheduler.PauseAll();
}
}
public void OnContinue()
{
if (scheduler != null)
{
bool isJobGroupPaused = false;
var groups = scheduler.GetJobGroupNames();
foreach (var group in groups)
{
isJobGroupPaused = scheduler.IsJobGroupPaused(group);
if (!isJobGroupPaused)
{
break;
}
}
if (isJobGroupPaused)
{
scheduler.ResumeAll();
}
}
}
}
public class DbMonitorJob : IJob
{
public void Execute(IJobExecutionContext context)
{
DbContextFactory.UpdateSlaves();
}
}
定时器配置文件quartz_jobs.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- This file contains job definitions in schema version 2.0 format -->
<job-scheduling-data xmlns= "http://quartznet.sourceforge.net/JobSchedulingData" xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance " version ="2.0 ">
<processing-directives>
<overwrite-existing-data>true</overwrite-existing-data>
</processing-directives>
<schedule>
<job>
<name>DbMonitorJob</name>
<group>DbMonitorJobGroup</group>
<description>更新可用从库</description>
<job-type>HY_WebApi.TaskScheduler.Jobs.DbMonitorJob, HY_WebApi.TaskScheduler</job-type>
<durable>true</durable>
<recover>false</recover>
</job>
<trigger>
<simple>
<name>DbMonitorJobTrigger</name>
<group>DbMonitorJobTriggerGroup</group>
<description>更新可用从库</description>
<job-name>DbMonitorJob</job-name>
<job-group>DbMonitorJobGroup</job-group>
<misfire-instruction>SmartPolicy</misfire-instruction>
<repeat-count>-1</repeat-count>
<repeat-interval>10000</repeat-interval>
</simple>
</trigger>
</schedule>
</job-scheduling-data>
定时任务配置文件quartz.config
# You can configure your scheduler in either <quartz> configuration section
# or in quartz properties file
# Configuration section has precedence
quartz.scheduler.instanceName = ServerScheduler
# configure thread pool info
quartz.threadPool.type = Quartz.Simpl.SimpleThreadPool, Quartz
quartz.threadPool.threadCount = 1
quartz.threadPool.threadPriority = Normal
# job initialization plugin handles our xml reading, without it defaults are used
quartz.plugin.xml.type = Quartz.Plugin.Xml.XMLSchedulingDataProcessorPlugin, Quartz
quartz.plugin.xml.fileNames = ~\quartz_jobs.xml
# export this server to remoting context
quartz.scheduler.exporter.type = Quartz.Simpl.RemotingSchedulerExporter, Quartz
quartz.scheduler.exporter.port = 555
quartz.scheduler.exporter.bindName = QuartzScheduler
quartz.scheduler.exporter.channelType = tcp
quartz.scheduler.exporter.channelName = httpQuartz
web项目配置文件Web.config
<configuration>
<connectionStrings>
<clear/><!--清除默认的连接字符串,务必加上!!!-->
<add name="Master" connectionString="Database=hy_webapi_n;Data Source=192.168.107.65;User Id=root;Password=cnki2016;CharSet=utf8;port=3306" providerName="MySql.Data.MySqlClient" />
<add name="Slave0" connectionString="Database=hy_webapi_n;Data Source=192.168.107.62;User Id=root;Password=cnki2016;CharSet=utf8;port=3306" providerName="MySql.Data.MySqlClient" />
<add name="Slave1" connectionString="Database=hy_webapi_n;Data Source=192.168.107.63;User Id=root;Password=cnki2016;CharSet=utf8;port=3306" providerName="MySql.Data.MySqlClient" />
</connectionStrings>
......
</configuration>
加载定时器
public class WebApiApplication : System.Web.HttpApplication
{
protected void Application_Start()
{
GlobalConfiguration.Configure(WebApiConfig.Register);
JobScheduler jobScheduler = new JobScheduler();
jobScheduler.log4netPath = AppSettings.Log4netPathForWeb;
jobScheduler.OnStart();
}
}
2 代码分析
最核心的部分是DbContextFactory。下面详细分析其设计与实现。
获得web.config配置文件中的连接名称
使用静态私钥变量allSlaves来表示从库集合,这样做的好处是:静态私有变量只在使用前初始化一次,当第一次被allSlaves使用时初始化一次,即调用GetAllSlaves()方法获得所有可用的从库。当第二次使用allSlaves时,即当SlaveDbContext属性第二次被调用时,不在计算allSlaves。大部分时间都花费在测试数据库是否可用,因此不在重复计算allSlaves节省了时间。直接的效果就是由于检测数据库是否可用的影响可以忽略不计。
不可使用单例模式
由于检测数据库是否可用相对耗费时间的比例较大,于是想到通过单例模式来实现DbContextFactory,这样会导致系统报错:The operation cannot be completed because the DbContext has been disposed.其原因就在于使用DbContext时,慎重使用单例模式,全局的DbContext会引起第二次调用出错,即第一次调用后DbContext资源即被释放。
类似于单例模式的实现,即全局的DbContext,也是不可取的。
基于上述考虑设计实现SlaveDbContext,在每次被调用时,都会返回一个新的实例。
多从库随机选择
当配置了多个从库时,应随机从从库集合中选择一个。于是使用伪随机数生成器Random。
所有从库不可用时切换到主库
当所有从库都不可用时,SlaveDbContext值为MasterDbContext。这里还应该增加一个额外的监测服务,当有从库不可用时自动报警,供系统维护人员查看。
注意先写后读的操作
对于这种操作,若主从同步延迟稍大,那么会造成操作失败,解决的办法是:只操作主库。保守的做法就是只操作主库,一般主从分部在内网的两台机器上,网络通信延迟一旦较大时,就会造成数据无法同步的假象。
-----------------------------------------------------------------------------------------
时间仓促,水平有限,如有不当之处,欢迎指正。