这个示例主要演示的是在多进程操作数据库时,如何避免并发重复数据入库的例子。
过多的线程理论不再阐述,网上、书上皆有。
项目采用 Asp.Net Framework 4.5 / Mysql 5.4 数据库。
首先创建一个 LockInsertDB.cs 文件,这个文件用来实现数据库添加;
1 using System;
2 using MySql;
3 using MySql.Data.MySqlClient;
4 using System.Collections.Generic;
5 using System.Linq;
6 using System.Text;
7 using System.Threading.Tasks;
8
9 namespace DBImportTool
{
public class LockInsertDB : Common
{
private object obj = new object();
/// <summary>
/// 添加测试数据
/// </summary>
/// <param name="path">字段内容</param>
/// <param name="tname">线程名称</param>
public void InsertTestDB(string path, object tname)
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection("Server=10.17.1.57; Database=videodb; Uid=root; Pwd=xingzhi");
try
{
conn.Open();//打开连接
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
//加入多线程,排它锁机制.
lock (obj)
{
string select = "SELECT * FROM `tbl_test` WHERE `filepath` = '" + path + "'";
cmd.CommandText = select;
object val = cmd.ExecuteScalar();
if (val == null)
{
string sql = "INSERT INTO `tbl_test` (`filepath`, `addtime`) VALUES ('" + path + "', NOW());";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
P(DateTime.Now.ToString() + "\t" + tname + "\tOK\t" + path + "\n");
}
else
{
P(DateTime.Now.ToString() + "\t" + tname + "\t已存在\t" + path + "\n");
}
}
}
catch
{
}
finally
{
//关闭连接
conn.Close();
}
}
/// <summary>
/// 测试输出
/// </summary>
/// <param name="str">输出内容字符串</param>
public void P(string str)
{
Console.Write(str);
}
}
65 }
再创建一个控制台工程,Main方法代码如下:
1 using System;
2 using System.Threading;
3 using System.IO;
4 using System.Collections.Generic;
5 using System.Linq;
6 using System.Text;
7 using System.Threading.Tasks;
8 namespace DBImportTool
9 {
public class Mcqueen
{
static void Main(string[] args)
{
Run();
Console.ReadLine();
}
/// <summary>
/// 进程数量.
/// </summary>
static int tnum = ;
/// <summary>
/// 所对象
/// </summary>
static object obj = new object();
/// <summary>
/// 线程数组.
/// </summary>
static Thread[] threadarr = new Thread[tnum];
/// <summary>
/// 数据库插入对象.
/// </summary>
static LockInsertDB mdb = new LockInsertDB();
/// <summary>
/// 运行函数
/// </summary>
public static void Run()
{
for (int i = ; i < tnum; i++)
{
Thread t = new Thread(T1);
threadarr[i] = t;
threadarr[i].Name = "Thread " + i.ToString() + " :";
}
int y = ;
while (y < tnum)
{
threadarr[y].Start(threadarr[y].Name);
y++;
}
}
/// <summary>
/// 读取磁盘文件路径.
/// </summary>
/// <param name="tname">线程名称</param>
public static void T1(object tname)
{
DirectoryInfo di = Directory.CreateDirectory(@"F:\邢智的文件\");
GetFiles(di, tname);
}
/// <summary>
/// 采用对列入栈模式对列遍历文件夹结构.
/// </summary>
/// <param name="di">目录对象</param>
/// <param name="tobj">线程名称</param>
private static void GetFiles(DirectoryInfo di,object tobj)
{
int sgin = ;
Stack<DirectoryInfo> stack_dir = new Stack<DirectoryInfo>();
DirectoryInfo dir = di;
stack_dir.Push(di);
while (stack_dir.Count != )
{
dir = stack_dir.Pop();
DirectoryInfo[] diarr = dir.GetDirectories();
foreach (DirectoryInfo d in diarr)
stack_dir.Push(d);
FileInfo[] files = dir.GetFiles();
foreach (FileInfo f in files)
{
mdb.InsertTestDB(f.FullName.Replace("\\", "\\\\"), tobj);
Thread.Sleep();
sgin++;
}
}
}
public static void P(string str)
{
Console.Write(str);
}
}
91 }
static LockInsertDB mdb = new LockInsertDB();
一开始把这句初始化对象放到了private static void GetFiles(DirectoryInfo di,object tobj) 函数里面,虽然在后续的执行过程中没有任何问题,
但是初始化运行线程时导致了进程并发进入lock锁中,后来研究一下原因,是因为每个进程都在实例化化它的时候都会在lock中分配一个标识符,
这样一来,假如A进程内实例化了A1,那么当B进程进来之后,B却不认识A线程内的A1,因此lock也就对A1不起作用了。
因此把这个类跳出线程而放到一个全局中去实例化,也就不存在这个lock失效的问题了。
运行结果如下:
如果需要模拟更多的进程用户,只需要把 tnum = 2 的数值改一下即可。