利用事务,进行数据库的实时同步

最新推荐文章于 2023-12-31 01:38:54 发布

chuli0525

最新推荐文章于 2023-12-31 01:38:54 发布

阅读量344

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://my.oschina.net/foxidea/blog/102037

本文介绍了一种在不同机房间实现SQL Server数据库同步的方法，包括使用ADO.NET事务进行并发操作以提高效率，并解决数据同步延迟、默认值丢失和索引问题。通过构建通用类库，确保所有数据库操作保持一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

因为机房一年当中总要出几次故障，

每次都搞的我们筋疲力尽

尤其是数据库，

为了避免数据库所在机房出现问题，我们想办法把数据分别保存在不同的机房中，这样一个机房出问题，我们可以及时切换

sql server 中有微软官方提供的复制功能、镜像功能说是可以让数据库同步数据，

我个人使用过 sql server 的复制，但是效果并不理想

首先是数据的同步要延迟4-5 秒钟左右，其次发现数据表的默认值丢失，索引出错等问题

数据库的镜像功能，网上大部分文章都是在局域网环境进行的

我需要在两个不同机房来配置镜像功能，我尝试了几次太难配置，就懒得去折腾了

所以这个问题一直困扰着我

直到最近，我在想可以利用 ADO.NET 中的事务进行同步

要么都执行成功，要么失败

其实数据库的同步主要是在写入数据、 修改数据、 删除数据的操作上

注：还有一些操作如：修改数据库结构（字段名称的修改，字段的增加删除等）这些操作我想的是，最好手工备份下数据库，传输到另一台服务器上，然后再还原一下，保证两边数据是一致的

认清了这点，我们就可以针对这几个操作写一个通用的类库，所有的数据库操作都用此类库去进行，这样就可以基本保证数据库的同步了

我在这里简单的写了下ExecuteNoneQuery的实现

环境：

本地 SQL Server 2008 + .NET 2.0

首先是，在本地建三个数据库 DB1,DB2,DB3

三个数据库中都有一张TEST 的表

创建表SQL：

CREATE TABLE [dbo].[TEST](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

这里用一个控制台应用程序去实现：

配置文件App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="DBConnection" type="System.Configuration.NameValueSectionHandler" />    
  </configSections>

  <DBConnection>
    <add key="DB1" value="Data Source=127.0.0.1;Initial Catalog=DB1;User ID=fhz;Password=fhz;" />
    <add key="DB2" value="Data Source=127.0.0.1;Initial Catalog=DB2;User ID=fhz;Password=fhz;" />
    <add key="DB3" value="Data Source=127.0.0.1;Initial Catalog=DB3;User ID=fhz;Password=fhz;" />
    
  </DBConnection>


  <appSettings>
    <add key="DbProviderName" value="System.Data.SqlClient" />
  </appSettings>

  <connectionStrings>
    
    
  </connectionStrings>



</configuration>

Program.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Collections.Specialized;
using System.Data;
using System.Data.Common;


namespace CAAA
{
    /// <summary>
    /// 构建一个程序，可以同事写入多个数据库的，事务形式的提交
    /// 对数据库的设计 都有要求
    /// 从而保持 数据同步的
    /// </summary>
    class Program
    {

        NameValueCollection DbCollection = (NameValueCollection)ConfigurationManager.GetSection("DBConnection");
        DbProviderFactory DbProviderFactory = DbProviderFactories.GetFactory(ConfigurationManager.AppSettings["DbProviderName"]);

        static void Main(string[] args)
        {

            Program p = new Program();
           
            p.ExecuteNoneQuery("insert into TEST(Name) values('nima');");
            Console.WriteLine("insert into success!");

            p.ExecuteNoneQuery("update TEST set Name = Name+' fuck!' where Name='nima';");
            Console.WriteLine("update success!");

            p.ExecuteNoneQuery("delete from TEST where id=1");
            Console.WriteLine("delete success!");
        

            

            Console.Read();
        }

        

        public int ExecuteNoneQuery(string sql, params DbParameter[] dbParams)
        {

            bool success = false;
            List<DbConnection> conns = new List<DbConnection>(DbCollection.Count);
            List<DbTransaction> trans = new List<DbTransaction>(DbCollection.Count);

            List<DbCommand> cmds = new List<DbCommand>(DbCollection.Count);

            List<int> nums = new List<int>(DbCollection.Count);


            try
            {
                foreach (string key in DbCollection)
                {
                    DbConnection conn = DbProviderFactory.CreateConnection();
                    conn.ConnectionString = DbCollection[key];                    

                    DbCommand cmd = DbProviderFactory.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(dbParams);
                    cmd.Connection = conn;
                    
                    conns.Add(conn);
                    cmds.Add(cmd);

                    conn.Open();

                    DbTransaction tran = conn.BeginTransaction();
                    cmd.Transaction = tran;
                    trans.Add(tran);

                    int num = cmd.ExecuteNonQuery();

                    nums.Add(num);
                }



                if (nums.Count == DbCollection.Count)
                {
                    bool temBool = true;
                    for (int i = 0; i < nums.Count - 1; i++)
                    {
                        temBool = temBool && (nums[i] == nums[i + 1]);
                    }

                    if (temBool)
                    {
                        foreach (DbTransaction tran in trans)
                        {
                            //如果在 tran.Commit() 执行的过程中，数据库出错                            
                            //如：第一个数据库链接执行成功，到了第二执行个失败
                            //那样就无法保证数据库同步了
                            //这个时候要手工把数据库 同步一次
                            tran.Commit();
                        }

                        success = true;
                    }
                }

                if (!success)
                {
                    foreach (DbTransaction tran in trans)
                    {
                        tran.Rollback();
                    }
                }

            }
            catch (Exception e)
            {
                foreach (DbTransaction tran in trans)
                {
                    tran.Rollback();
                }

                throw e;
            }
            finally
            {
                foreach (DbConnection conn in conns)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                }

                foreach (DbCommand cmd in cmds)
                {
                    cmd.Dispose();
                }

                foreach (DbTransaction tran in trans)
                {
                    tran.Dispose();
                }
            }

            if (success)
            {
                return nums[nums.Count - 1];
            }
            else
            {
                return 0;
            }

        }

    }
}

代码如果就这样简单的去实现，在插入数据比较多的情况下，就会消耗很多时间

第一个数据库写入完成之后，才能去写第二个数据库，第二个完成之后才能去写带三个

所以，这样效率太低

可以用多线程去实现，这样就高效很多：

public int ExecuteNoneQuery(string sql, params DbParameter[] dbParams)
        {

            bool success = false;
            int count = DbCollection.Count;

            List<DbConnection> conns = new List<DbConnection>(count);
            List<DbTransaction> trans = new List<DbTransaction>(count);
            List<DbCommand> cmds = new List<DbCommand>(count);
            List<int> nums = new List<int>(count);




            try
            {
                ManualResetEvent[] events = new ManualResetEvent[count];
                for (int i = 0; i < count; i++)
                {
                    events[i] = new ManualResetEvent(false);
                    string key = DbCollection.Keys[i];

                    ThreadPool.QueueUserWorkItem(delegate(object o)
                    {
                        int inx = (int)o;
                        ManualResetEvent eve = events[inx];

                        DbConnection conn = DbProviderFactory.CreateConnection();
                        conn.ConnectionString = DbCollection[key];

                        DbCommand cmd = DbProviderFactory.CreateCommand();
                        cmd.CommandText = sql;
                        cmd.Parameters.AddRange(dbParams);
                        cmd.Connection = conn;

                        conns.Add(conn);
                        cmds.Add(cmd);

                        conn.Open();

                        DbTransaction tran = conn.BeginTransaction();
                        cmd.Transaction = tran;
                        trans.Add(tran);

                        int num = cmd.ExecuteNonQuery();

                        nums.Add(num);

                        eve.Set();
                    }, i);
                }

                WaitHandle.WaitAll(events);



                if (nums.Count == count)
                {
                    bool temBool = true;
                    for (int i = 0; i < count - 1; i++)
                    {
                        temBool = temBool && (nums[i] == nums[i + 1]);
                    }
                    success = temBool;
                }


                if (success)
                {
                    foreach (DbTransaction tran in trans)
                    {
                        tran.Commit();
                    }
                }
                else
                {
                    foreach (DbTransaction tran in trans)
                    {
                        tran.Rollback();
                    }
                }

            }
            catch (Exception e)
            {
                foreach (DbTransaction tran in trans)
                {
                    tran.Rollback();
                }

                throw e;
            }
            finally
            {
                foreach (DbConnection conn in conns)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                }

                foreach (DbCommand cmd in cmds)
                {
                    cmd.Dispose();
                }

                foreach (DbTransaction tran in trans)
                {
                    tran.Dispose();
                }
            }

            if (success)
            {
                return nums[nums.Count - 1];
            }
            else
            {
                return 0;
            }

        }

最终修复一些错误：

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Collections.Specialized;
using System.Data;
using System.Data.Common;
using System.Threading;


namespace CAAA
{
    /// <summary>
    /// 构建一个程序，可以同事写入多个数据库的，事务形式的提交
    /// 对数据库的设计 都有要求
    /// 从而保持 数据同步的
    /// </summary>
    class Program
    {

        NameValueCollection DbCollection = (NameValueCollection)ConfigurationManager.GetSection("DBConnection");
        public DbProviderFactory DbProviderFactory = DbProviderFactories.GetFactory(ConfigurationManager.AppSettings["DbProviderName"]);

        static void Main(string[] args)
        {

            Program p = new Program();
            DbParameter parame = p.DbProviderFactory.CreateParameter();
            parame.ParameterName = "@Name";
            parame.Value = "nima.so";

            p.ExecuteNoneQuery("insert into TEST(Name) values(@Name);", parame);
            Console.WriteLine("insert into success!");

            p.ExecuteNoneQuery("update TEST set Name = Name+' fuck!' where Name='nima';");
            Console.WriteLine("update success!");

            //p.ExecuteNoneQuery("delete from TEST where id=1");
            //Console.WriteLine("delete success!");




            Console.Read();
        }



        public int ExecuteNoneQuery(string sql, params DbParameter[] dbParams)
        {

            bool success = false;
            int count = DbCollection.Count;

            List<DbConnection> conns = new List<DbConnection>(count);
            List<DbTransaction> trans = new List<DbTransaction>(count);
            List<DbCommand> cmds = new List<DbCommand>(count);
            List<int> nums = new List<int>(count);




            try
            {
                ManualResetEvent[] events = new ManualResetEvent[count];
                for (int i = 0; i < count; i++)
                {
                    events[i] = new ManualResetEvent(false);
                    string key = DbCollection.Keys[i];

                    ThreadPool.QueueUserWorkItem(delegate(object o)
                    {
                        int inx = (int)o;
                        ManualResetEvent eve = events[inx];

                        DbConnection conn = DbProviderFactory.CreateConnection();
                        conn.ConnectionString = DbCollection[key];

                        DbCommand cmd = DbProviderFactory.CreateCommand();
                        cmd.CommandText = sql;



                        for (int n = 0; n < dbParams.Length; n++)
                        {
                            DbParameter op = dbParams[n];
                            DbParameter p = DbProviderFactory.CreateParameter();
                            p.ParameterName = op.ParameterName;
                            p.Size = op.Size;
                            p.Value = op.Value;
                            p.DbType = op.DbType;
                            p.Direction = op.Direction;
                            p.SourceColumn = op.SourceColumn;
                            p.SourceColumnNullMapping = op.SourceColumnNullMapping;
                            p.SourceVersion = op.SourceVersion;

                            cmd.Parameters.Add(p);
                        }
                        
                        cmd.Connection = conn;

                        conns.Add(conn);
                        cmds.Add(cmd);

                        conn.Open();

                        DbTransaction tran = conn.BeginTransaction();
                        cmd.Transaction = tran;
                        trans.Add(tran);

                        int num = cmd.ExecuteNonQuery();
                        

                        nums.Add(num);

                        eve.Set();
                    }, i);
                }

                WaitHandle.WaitAll(events);



                if (nums.Count == count)
                {
                    bool temBool = true;
                    for (int i = 0; i < count - 1; i++)
                    {
                        temBool = temBool && (nums[i] == nums[i + 1]);
                    }
                    success = temBool;
                }


                if (success)
                {
                    foreach (DbTransaction tran in trans)
                    {
                        tran.Commit();
                    }
                }
                else
                {
                    foreach (DbTransaction tran in trans)
                    {
                        tran.Rollback();
                    }
                }

            }
            catch (Exception e)
            {
                foreach (DbTransaction tran in trans)
                {
                    tran.Rollback();
                }

                throw e;
            }
            finally
            {
                foreach (DbConnection conn in conns)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                }

                foreach (DbCommand cmd in cmds)
                {
                    cmd.Dispose();
                }

                foreach (DbTransaction tran in trans)
                {
                    tran.Dispose();
                }
            }

            if (success)
            {
                return nums[nums.Count - 1];
            }
            else
            {
                return 0;
            }

        }

    }
}

转载于:https://my.oschina.net/foxidea/blog/102037