文章系列
前言
上章我发现了这个问题,本章开始着手解决
一、采用事务的方法
思路:在事务中进行插入,插入后如果数量大于1,则回滚,否则则正常插入
代码如下:
using (var transaction = _context.Database.BeginTransaction())
{
for (int i = 0; i < 10; i++)
{
students.Add(new Student
{
Type = 0,
Number = ++findMax,
});
}
_context.Students.AddRange(students);
_context.SaveChanges();
if (_context.Students.Count(t => t.Number == students.FirstOrDefault().Number) > 1)
{
transaction.Rollback();
}
else
{
transaction.Commit();
}
}
1.运行项目,启动20线程,每个线程运行10次
2.查询数据库数据
SELECT Students.Number,count(*) as count FROM [dbo].[Students] GROUP BY Students.Number order by count desc;
发现并没有解决问题,查找原因
事务具有隔离性(isolation)。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
所以可能存在两个事务同时提交导致数据还是会重复
二、采用并发时间戳方式
思路:加一个中间表,中间表数据库如果被一个线程发生了更新了,另一个线程也在更新就会产时一个异常,根据这个异常修复对应数据(删除老的添加数据,更新新的添加数据,更新中间数据表)
代码如下:
int findMax = _context.StudentMaxs.FirstOrDefault(t => t.Type == 0).Number;
if (findMax == 0)
{
for (int i = 0; i < 10; i++)
{
students.Add(new Student
{
Type = 0,
Number = ++findMax,
});
}
StudentMax studentMax = new StudentMax()
{
Number = findMax,
Type = 0,
};
_context.StudentMaxs.Add(studentMax);
_context.Students.AddRange(students);
_context.SaveChanges();
}
else
{
for (int i = 0; i < 10; i++)
{
students.Add(new Student
{
Type = 0,
Number = ++findMax,
});
}
_context.Students.AddRange(students);
StudentMax studentMax = _context.StudentMaxs.FirstOrDefault(t => t.Type == 0);
studentMax.Number = findMax;
var saved = false;
while (!saved)
{
try
{
// Attempt to save changes to the database
_context.SaveChanges();
saved = true;
}
catch (DbUpdateConcurrencyException ex)
{
foreach (var entry in ex.Entries)
{
if (entry.Entity is StudentMax)
{
var proposedValues = entry.CurrentValues;
var databaseValues = entry.GetDatabaseValues();
foreach (var property in proposedValues.Properties)
{
var proposedValue = proposedValues[property];
var databaseValue = databaseValues[property];
if(property.Name== "Number")
{
findMax = (int)databaseValue;
proposedValues[property] = (findMax+10);
}
// TODO: decide which value should be written to database
// proposedValues[property] = <value to be saved>;
}
_context.Students.RemoveRange(students);
students.Clear();
for (int i = 0; i < 10; i++)
{
students.Add(new Student
{
Type = 0,
Number = ++findMax,
});
}
_context.Students.AddRange(students);
// Refresh original values to bypass next concurrency check
entry.OriginalValues.SetValues(databaseValues);
}
else
{
throw new NotSupportedException(
"Don't know how to handle concurrency conflicts for "
+ entry.Metadata.Name);
}
}
}
}
}
2.运行项目,启动20线程,每个线程运行10次
3.查询数据库数据
SELECT Students.Number,count(*) as count FROM [dbo].[Students] GROUP BY Students.Number order by count desc;
可以看到采用中间表的形式可以防止重复数据生成
总结
本文采用了两个方法对数据库数据进行连续编号,我想应该还有其他方法,下次带给大家其他方法